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!

4 Answers 4


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
    Commented Apr 19, 2017 at 13:38
  • I have been looking all over the place, can you not calculate the difference between years?
    – BeerusDev
    Commented Sep 22, 2021 at 14:10
  • @BeerusDev you can specify the unit "Y" for the completed years between your dates. support.microsoft.com/en-us/office/…
    – JayHell
    Commented Sep 24, 2021 at 15:14

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

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


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


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
    Commented May 3, 2016 at 8:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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