Nov 15 2022 02:22 PM
Hello,
I am trying to figure out a way to write a variation of Excel's 'NETWORKDAYS' formula where it will calculate number of business days between a start date/time and an end date/time - and my business hours need to be 7am - 11pm. Preferably the least complicated, if possible?
Example:
10/1/22 10:58 PM - 10/3/22 10:58 PM = 3 business days (i.e. 10/1, 10/2 & 10/3)
10/1/22 11:01 PM - 10/3/22 10:58 PM = 2 business days (i.e. 10/2 (adjusted due to 11pm cut-off) and 10/3).
Thank you in advance for any assistance.
Nov 15 2022 02:36 PM
Why 3 / 2 business days? 10/1/22 was a Saturday and 10/2/22 was a Sunday...
Nov 15 2022 02:45 PM
Sorry horrible example - I was just using quick dates to show the example. I didn't even realize those were weekends. Any weekday really was what I meant.
Nov 15 2022 03:02 PM - edited Nov 15 2022 03:32 PM
OK forget the weekend I still don't get it. Do you want to count a full day if any part of that workday is included? In your first example:
10/1/22 10:58 PM - 10/3/22 10:58 PM = 3 business days (i.e. 10/1, 10/2 & 10/3)
you are saying 3 days and including a full day for 10/1 when the start time is 2min before closing.
assuming that is true then:
=NETWORKDAYS( StartCELL + 1/24, EndCELL - 7/24, [HOLIDAYS})
should work
EDIT: this may not work/cause an error if the start and end days are the SAME day or next day but <8hrs apart. You can do a check IF(3*(End-Start)>1, NETWORKDAYS(... ), (HOUR(Start)<23)*(HOUR(End)>7))
Nov 15 2022 03:14 PM
Solution@NeilKloster Perhaps, with the start date and time in A2 and the end date and time in B2:
=NETWORKDAYS(A2,B2)-(HOUR(A2)>=23)-(HOUR(B2)<7)
Format the cell with the formula as General.
Nov 15 2022 03:41 PM
Nov 15 2022 06:17 PM
Nov 15 2022 06:22 PM
Nov 15 2022 03:14 PM
Solution@NeilKloster Perhaps, with the start date and time in A2 and the end date and time in B2:
=NETWORKDAYS(A2,B2)-(HOUR(A2)>=23)-(HOUR(B2)<7)
Format the cell with the formula as General.