# Calculated column for differences between two dates with blank check

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.

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

`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 May 3 '16 at 8:10

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

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