When you use DATEDIF in a SharePoint Calculated field and the result is negative the function returns #NUM! how can I detect that value with an IF to branch around it?

=IF(DATEDIF(NOW(),[Proposal Status Date],"d")="#NUM!",1,2)

Ultimately, overdue "Proposals" return a negative value which isn't cool with DATEDIF.

Thanks! I hope this makes sense.

Guess I could just compare the dates first (?) -- just asking...

  • Such a great question. Stack overflow gave me a badge... Weee... No one voted for it though... – Glenn Ferrie Apr 22 at 3:30

Use the ISERROR() function.

=IF(ISERROR(DATEDIF(NOW(),[Proposal Status Date],"d")),1,2)
  • ISERR: Value refers to any error value except #N/A.

  • ISERROR: Value refers to any error value (#N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!).

  • ISNA: Value refers to the #N/A (value not available) error value.

IS functions


This can be resolve by using this formula :

=IF(ISERROR(DATEDIF(NOW(),[Proposal Status Date],"d")), -DATEDIF(NOW(),[Proposal Status Date],"d"))

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.