1

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)))
0

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))), "")
1

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.