SharePoint Calculated Column showing #NUM!. One of the Column needs to be in Completed Status

Question

I have a calculated column that will get the date difference between the two columns.

Conditions:

• Difference between Completed Date and Created Date.
• Weekend should not be counted.
• If Completed Date is blank it should get the difference between Modified Date and Created Date.
• Status column value should be Completed, else the output should be blank.

I have this and everything is working except if the Status column has any value other than Completed. It shows #NUM!.

Been trying to use ISERROR but to no avail. Hope Anyone can help.

=IF(AND(ISBLANK([Completed Date]),[Status]="Completed"),(DATEDIF([Created],[Modified],"D"))-INT(DATEDIF([Created],[Modified],"D")/7)*2-
IF(WEEKDAY([Modified])<WEEKDAY([Created]),2,
IF(OR(WEEKDAY([Modified])=7,WEEKDAY([Created])=1),1,0)),(DATEDIF([Created],[Completed Date],"D"))-INT(DATEDIF([Created],[Completed Date],"D")/7)*2-
IF(WEEKDAY([Completed Date])<WEEKDAY([Created]),2,
IF(OR(WEEKDAY([Completed Date])=7,WEEKDAY([Created])=1),1,0)))

Answer 1

Add one more (outer most) condition to check if the [Status] is equal to Completed like:

=IF([Status]="Completed", IF(AND(ISBLANK([Completed Date]),[Status]="Completed"),(DATEDIF([Created],[Modified],"D"))-INT(DATEDIF([Created],[Modified],"D")/7)*2 - IF(WEEKDAY([Modified])<WEEKDAY([Created]),2,IF(OR(WEEKDAY([Modified])=7,WEEKDAY([Created])=1),1,0)),(DATEDIF([Created],[Completed Date],"D"))-INT(DATEDIF([Created],[Completed Date],"D")/7)*2-IF(WEEKDAY([Completed Date])<WEEKDAY([Created]),2,IF(OR(WEEKDAY([Completed Date])=7,WEEKDAY([Created])=1),1,0))), "")

If above is working fine for you, you can refine your formula further to remove nested conditions checking the Status value like:

=IF([Status]="Completed", IF(ISBLANK([Completed Date]),DATEDIF([Created],[Modified],"D")-INT(DATEDIF([Created],[Modified],"D")/7)*2 - IF(WEEKDAY([Modified])<WEEKDAY([Created]),2,IF(OR(WEEKDAY([Modified])=7,WEEKDAY([Created])=1),1,0)),(DATEDIF([Created],[Completed Date],"D"))-INT(DATEDIF([Created],[Completed Date],"D")/7)*2-IF(WEEKDAY([Completed Date])<WEEKDAY([Created]),2,IF(OR(WEEKDAY([Completed Date])=7,WEEKDAY([Created])=1),1,0))), "")

Answer 2

I found a resource I believe could be very helpful.

DATEDIF formular errors The DATEDIF function gives a #NUM! error result if the first date parameter is greater than the second date parameter ; the first parameter is the START DATE , and the second parameter is the END DATE ; the START DATE is expected to be less than or prior to the END DATE.

The DATEDIF function also gives a #NUM! error result if the third parameter is an invalid format string. Thus , "d" or "D" is for days , "m" or "M" is for months , and "y" or "Y" is for years ; anything else is invalid and will result in the error value #NUM! being output.

The DATEDIF function gives a #VALUE! error result if either or both of the two date parameters are not valid dates that Excel can recognize ; Excel treats invalid dates (dates in the wrong format) as text strings , and if either or both of the first two parameters are text , the error result #VALUE! will be output.

Source: https://chandoo.org/forum/threads/datedif-gives-value-error.36026/