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

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] )
  • As an additional note, blank dates will not necessarily raise an error. They are treated as "12/31/1899" by default. – Mike Smith - MCT - MVP Jul 3 '18 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. – Dee Jay Jul 3 '18 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 - MVP Jul 3 '18 at 17:04
  • Just tried again and it worked just fine THANK YOU!! – Dee Jay Jul 3 '18 at 18:26

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.