1

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

6

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
  • 1
    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

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.