2

I am trying to create a calculated column which shows the number of days' difference between two columns which have dates in them. I understand that I can get this with the following formula:

=DATEDIF([Date 1],[Date 2],"D")

which indeed works, as long as there is a date in both columns.

However, in many cases the columns are blank, because the date has not yet been set.

This sets the number as 0 if both Date 1 and Date 2 are empty; #NUM! if Date 1 is present but not Date 2; and 42,000+ if date 2 is present but not date 1.

How do I set up the column to make the above formula, but with a check to see if either column is blank, and if they are, to leave the calculated column blank too? I have tried various formulae and none of them seem to work right.

Thanks in advance!

7

Check if one of the value is empty, if it is, return blank, else calculate the difference.

=IF(OR(ISBLANK([Date 1]),ISBLANK([Date 2])),"",DATEDIF([Date 1],[Date 2],"D"))
  • Lifesaver! Worked for me too – Enilorac Apr 19 '17 at 13:38
1

IF(AND((NOT(ISBLANK([Date 1])),(NOT(ISBLANK([Date 2]))),DATEDIF([Date 1],[Date 2],"D"),"")

1

Since SharePoint doesn't really care about Types.

Blank Dates values are False values, and valid Dates are True values,

So Erin was the closest, just had a too long Formula

=IF( AND([Date 1],[Date 2]) , DATEDIF([Date 1],[Date 2],"D") , "" )
  • This one seemed to return a syntax error? Thanks all the same though; Jay's answer above worked. – user53809 May 3 '16 at 8:10
1

It may be slightly easier to use the IFERROR function to keep the formula shorter

=IFERROR(DATEDIF([Date 1],[Date 2],"D"),"")

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.