# Calculated Field with formula DATEDIF

Im working with a calculated field and using a formula to retrieve the difference of the days between two dates, DATE1 and DATE2, my simple formula is:

``````=DATEDIF([DATE1], [DATE2],"d")
``````

It works fine when DATE1 and DATE2 have value, but when DATE1 or DATE2 has no values I got the `#NÚM!` Error.

I searched about how to deal with errors in formulas on MSDN and find this useful topic: https://msdn.microsoft.com/en-us/library/office/bb862071(v=office.14).aspx

So I make this formula:

``````=ISERROR(DATEDIF([DATE1] , [DATE2] ,"d"), "0", DATEDIF([DATE1] , [DATE2] ,"d"))
``````

If the date difference between DATE1 and DATE2 brings me an error, I set the value of field to 0. If the difference brings me a number I set the value of the field with this number. But when I try to save this formula in the column, Sharepoint brings me te error "Could not save changes to the server" because he can't compile the formula.

I have 6 different fields in the list where I have to use DATEDIF function to return the difference of the days and a column to make the SUM of all date differences, when I get the #NÚM error I can't make this SUM.

What I'm doing wrong? Thanks in advance

## 1 Answer

`ISERROR()` takes only one parameter and returns `TRUE` or `FALSE`. It is typically used inside an `IF()` statement to evaluate a formula. What you probably want is something like

``````=if(ISERROR(DATEDIF([DATE1] , [DATE2] ,"d")),"0", DATEDIF([DATE1] , [DATE2] ,"d"))
``````

In words: If the `DATEDIF` function returns an error, use "0", else use the result of the `DATEDIF` function.

• It worked! And it was written on the msdn topic, I can't believe I didn't realize this. Thank you so much! – Thales Chemenian Mar 31 '16 at 20:30
• I think this is shorter: `=IF( AND([DATE1],[DATE2]) , DATEDIF([DATE1],[DATE2]) , 0 )` because empty dates are False values. And SP is loosly typed so "0" is equal to 0 (and not confirmed, "d" is the default value) – Danny '365CSI' Engelman Apr 1 '16 at 9:12
• My focus was not on formula efficiency but on correcting the syntax so the error disappears. Your formula works wonderfully, of course, but uses a few shortcuts that novice users may be struggling with. – teylyn Apr 1 '16 at 21:52