Search code examples
datesharepointnumbersformulas

How to calculate the elapsed time between two dates in Sharepoint when the answer may be a negative number?

I am working on a Sharepoint 2013 formula that will calculate the time in days between two dates. I have successfully done so with this formula:

=DATEDIF(Created,[Sold On],"d")

However, there will be some instances when the Created date comes AFTER the Sold On date, thus the days will be a negative number. I have attempted the following formulas:

1) =IF(ISERROR(DATEDIF(Date1,Date2,"d")), -DATEDIF(Date2,Date1,"d"))

2) =IF(ISERROR(DATEDIF(Created,Sold On,"d")), -DATEDIF(Sold On,Created,"d"),DATEDIF(Created,Sold On,"d"))

3) =IF(ISERROR(DATEDIF(Created,[Sold On],"d")), -DATEDIF(Sold On,[Created],"d"))

4) =IF(ISERROR(DATEDIF(Created,[Sold On],"d")), -DATEDIF(Sold On,[Created],"d"),DATEDIF(Created,[Sold On],"d"))

I found these examples on the Sharepoint forums. None of these formulas worked for me. Sharepoint threw a syntax error for each. I even thought I detected a missing closing parenthesis at the end of each of the above and tried each that way. Still threw syntax errors. Any suggestions?

Solution

  • DATEDIF returns an error if the result is negative, so in the second DATEDIF in the IF, reverse the order of the dates.

    While the test for error might work, this is a bit cleaner:

    =IF(created<[Sold On], DATEDIF(created, [Sold On],"d"), DATEDIF([Sold On],created,"d"))

    To return a negative when Created is after Sold date:

    =IF(created<[Sold On], DATEDIF(created, [Sold On],"d"), -DATEDIF([Sold On],created,"d"))