# Calculate difference between dates without weekend and ignore blanks

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 Answer

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