0

I'm working on a formula in SharePoint 2013 to compare the elapsed days after, if the start date is in the future, and today, unless today is after the end date, in which case I want the days between the start date and end date. I want to multiply this number by a value column I have created. I currently have the following:

=if(
([AM PROR (k£)]+[Apps PROR (k£)]+[CC PROR (k£)]+[Infra PROR (k£)])>1, 
([AM PROR (k£)]+[Apps PROR (k£)]+[CC PROR (k£)]+[Infra PROR (k£)]), 
IF(DATEDIF([FROM], [TODAY], ‘d’)<0,
 0,
 [EST: Monthly Revenue (£k)]*
IF(DATEDIF([TODAY], [TO] , ‘d’)<0,
 DATEDIF([FROM], [TO], ‘d’),
 DATEDIF([FROM], [TODAY], ‘d’))
)

But seem to be having an issue with the second IF statement, starting: IF(DATEDIF([FROM], [TODAY], ‘d’)<0, 0, IF(......

I want to say, if the project hasn't started, return 0, if it has then please IF(....

Is there a problem with using the DATEDIF to return a negative number in this way?

Any suggestions welcome.

James O' Sullivan
  • 47
  • 1
  • 11
  • Is this for the list's validation settings or for a calculated column? If you're trying to reference today's date in a calculated column, [you're going to run into some problems](https://stackoverflow.com/questions/43760150/sharepoint-2013-2016-calculated-column-stops-calculating/43763506#43763506). – Thriggle May 09 '17 at 14:52
  • Yes, a calculated column trying to reference today's date. Someone said to crest a column to return today's date and reference that instead, but was hoping to skip this and get it all into one formula? – James O' Sullivan May 09 '17 at 22:23

1 Answers1

0

[TODAY] does not work in SharePoint like it does in Excel

See: https://sharepoint.stackexchange.com/questions/151144/how-to-use-today-and-me-in-calculated-column

Danny '365CSI' Engelman
  • 16,526
  • 2
  • 32
  • 49