Calculated column for differences between two dates with blank check

Question

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!

Answer 1

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"))

Answer 2

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

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

Answer 3

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

Answer 4

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") , "" )