0

I am trying to calculate difference (in days) between two dates in SharePoint 2013 using the formula below:

=IF(AND((WEEKDAY(Completed,2))<(WEEKDAY(Issued,2)),((WEEKDAY(Issued,2))-(WEEKDAY(Completed,2)))>1),(((DATEDIF(Issued,Completed,"D")+1))-(FLOOR((DATEDIF(Issued,Completed,"D")+1)/7,1)*2)-2),(((DATEDIF(Issued,Completed,"D")+1))-(FLOOR((DATEDIF(Issued,Completed,"D")+1)/7,1)*2)))

This works fine. However, the completed date remains empty until the action is completed. This creates a !Num error when I run the calculation. How do I integrate this with ISBLANK formula to remove the !Num error.

1

It seems I have found the solution to my own question. I nested two IF statements as below

=IF(NOT(ISBLANK(Completed)),IF(AND((WEEKDAY(Completed,2))<(WEEKDAY(Issued,2)),((WEEKDAY(Issued,2))-(WEEKDAY(Completed,2)))>1),(((DATEDIF(Issued,Completed,"D")+1))-(FLOOR((DATEDIF(Issued,Completed,"D")+1)/7,1)*2)-2),(((DATEDIF(Issued,Completed,"D")+1))-(FLOOR((DATEDIF(Issued,Completed,"D")+1)/7,1)*2))),"")

It worked fine in Excel. I will try to use it in SharePoint as see if it works there too!

  • It works fine in SharePoint!! I hope this can help others – Jamal Nov 24 '16 at 8:12

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.