# Calculating business days between dates and allowing a negative number

I have some working code that calculates business days between two dates:

``````=(DATEDIF([Date Submitted],[Start Date],"D"))-INT(DATEDIF([Date Submitted],[Start Date],"D")/7)*2-IF(WEEKDAY([Start Date])<WEEKDAY([Date Submitted]),2,IF(OR(WEEKDAY([Start Date])=7,WEEKDAY([Date Submitted])=1),1,0))
``````

AND I have some working code that calculates the total days between two dates and ALLOWS NEGATIVE NUMBER as the result:

``````=IF(ISERROR(DATEDIF([Date Submitted],[Start Date],"d")), -DATEDIF([Start Date],[Date Submitted],"d"),DATEDIF([Date Submitted],[Start Date],"d"))
``````

What I can't figure out is how to combine them to allow a negative number as the result while calculating business days instead of total days.

Any help will be greatly appreciated!

Try the formula below and let me know if you see any issues. It turned out to be little lengthy.

``````=IF(ISERROR(DATEDIF([Date Submitted],[Start Date],"d")),-((DATEDIF([Start Date],[Date Submitted],"d")+1)-INT(DATEDIF([Start Date],[Date Submitted],"d")/7)*2-IF((WEEKDAY([Date Submitted])-WEEKDAY([Start Date]))<0,2,0)-IF(OR(AND(WEEKDAY([Date Submitted])=7,WEEKDAY([Start Date])=7),AND(WEEKDAY([Date Submitted])=1,WEEKDAY([Start Date])=1)),1,0)-IF(AND(WEEKDAY([Start Date])=1,(WEEKDAY([Date Submitted])-WEEKDAY([Start Date]))>0),1,0)-IF(AND(NOT(WEEKDAY([Start Date])=7),WEEKDAY([Date Submitted])=7),1,0)),DATEDIF([Date Submitted],[Start Date],"d")+1-INT(DATEDIF([Date Submitted],[Start Date],"d")/7)*2-IF((WEEKDAY([Start Date])-WEEKDAY([Date Submitted]))<0,2,0)-IF(OR(AND(WEEKDAY([Start Date])=7,WEEKDAY([Date Submitted])=7),AND(WEEKDAY([Start Date])=1,WEEKDAY([Date Submitted])=1)),1,0)-IF(AND(WEEKDAY([Date Submitted])=1,(WEEKDAY([Start Date])-WEEKDAY([Date Submitted]))>0),1,0)-IF(AND(NOT(WEEKDAY([Date Submitted])=7),WEEKDAY([Start Date])=7),1,0))

``````

Here's the output of my testing of the formula

Updated when both dates are the same, [BusinessDays] to be calculated zero (0) Added a condition at the beginning to check that, the rest remains the same. It's assumed that both dates are required.

``````=IF((YEAR([Start Date])+MONTH([Start Date])+DAY([Start Date]))=(YEAR([Date Submitted])+MONTH([Date Submitted])+DAY([Date Submitted])),0,IF(ISERROR(DATEDIF([Date Submitted],[Start Date],"d")),-((DATEDIF([Start Date],[Date Submitted],"d")+1)-INT(DATEDIF([Start Date],[Date Submitted],"d")/7)*2-IF((WEEKDAY([Date Submitted])-WEEKDAY([Start Date]))<0,2,0)-IF(OR(AND(WEEKDAY([Date Submitted])=7,WEEKDAY([Start Date])=7),AND(WEEKDAY([Date Submitted])=1,WEEKDAY([Start Date])=1)),1,0)-IF(AND(WEEKDAY([Start Date])=1,(WEEKDAY([Date Submitted])-WEEKDAY([Start Date]))>0),1,0)-IF(AND(NOT(WEEKDAY([Start Date])=7),WEEKDAY([Date Submitted])=7),1,0)),DATEDIF([Date Submitted],[Start Date],"d")+1-INT(DATEDIF([Date Submitted],[Start Date],"d")/7)*2-IF((WEEKDAY([Start Date])-WEEKDAY([Date Submitted]))<0,2,0)-IF(OR(AND(WEEKDAY([Start Date])=7,WEEKDAY([Date Submitted])=7),AND(WEEKDAY([Start Date])=1,WEEKDAY([Date Submitted])=1)),1,0)-IF(AND(WEEKDAY([Date Submitted])=1,(WEEKDAY([Start Date])-WEEKDAY([Date Submitted]))>0),1,0)-IF(AND(NOT(WEEKDAY([Date Submitted])=7),WEEKDAY([Start Date])=7),1,0)))

``````

• THANK YOU, this is GREAT! The only thing I'm seeing as that my users expect the "number of days" calculated to be zero if the submitted date and the start date are the same day. Your wonderful code calculates the dates the same as 1. Oct 26, 2020 at 20:45
• you rock! :) Thank you so very much for this. I don't know how to show my sincere appreciation. Oct 27, 2020 at 0:14
• If it did work for you, please mark this question as answered. Oct 27, 2020 at 1:18