1

This is for SharePoint Online

I have a calculated column that takes info from 2 date columns and calculates the number of days. Some times one of these columns wont contain data. This will then populate the field with either #NAME! or #NUM? error in the third column.

I'm attempting to create an error catch with the code below:

=DATEDIF([Create Date],[Date Closed],"d",IF(ISERROR(DATEDIF([Create Date],[Date Closed],"d"),"-","") )

I am not sure if there are too many arguments or if I have just totally messed up the syntax.

All I want the calculation to do is:

First- calculate the number of days between one column and the other. Thats what the (DATEIF,"d") function does.

Then-If either of the columns are missing data the ISERROR function should catch it. If that is the case it should show either "-" or "".

I don't know if this is possible out of the box or if I need to use JavaScript to accomplish this.

If it is possible I could really use help with this syntax

Improve this question

2 Answers 2

Reset to default
3

It's clearer to test for blanks with ISBLANK(), not ISERROR().

DATEDIF is not needed for simple "d" math. Just subtract the dates. DATEDIF also raises an error if the result is negative.

=[Date Closed] - [Create Date]

So:

=IF( OR( ISBLANK([Date Closed]), ISBLANK([Create Date]) ), "-",
    [Date Closed] - [Create Date] )
Improve this answer
4
  • As an additional note, blank dates will not necessarily raise an error. They are treated as "12/31/1899" by default.
    – Mike Smith - MCT
    Commented Jul 3, 2018 at 2:14
  • I understand the syntax here. Thank you Mike. I'm still getting the "something went wrong" error. Think I'm understanding the formula correctly; If date closed or create date is blank then display "-" else subtract Date Closed from Create Date. I'm not sure if I'm misplacing a character or not. Just having trouble finding my mistake. I tried copying your text but I'm still getting an that message.
    – Mile T.
    Commented Jul 3, 2018 at 15:10
  • Just to confirm... The two columns are Date columns, not Single Line of Text. Does the first, simple subtraction, example work?
    – Mike Smith - MCT
    Commented Jul 3, 2018 at 17:04
  • Just tried again and it worked just fine THANK YOU!!
    – Mile T.
    Commented Jul 3, 2018 at 18:26
0

I know this is a really old question, but it was a top result on Google, so I'd like to answer. It works if you test for the error first.

=IF(ISERROR(DATEDIF(TODAY(),[Training Expires],"m")),"Expired",IF(DATEDIF(TODAY(),[Training Expires],"m")>3,"OK","Expiring"))
Improve this answer

Your Answer

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

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