2

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

Improve this question

1 Answer 1

Reset to default
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.

Improve this answer
3
  • 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, 2016 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, 2016 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, 2016 at 21:52

Your Answer

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

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