0

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!

0

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

enter image description here

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

enter image description here

3
  • 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.
    – Kellie
    Oct 26 '20 at 20:45
  • you rock! :) Thank you so very much for this. I don't know how to show my sincere appreciation.
    – Kellie
    Oct 27 '20 at 0:14
  • If it did work for you, please mark this question as answered. Oct 27 '20 at 1:18

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.