SOLVED

calculate business hours in excel

Contributor

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. 

 

7 Replies

@NeilKloster 

Why 3 / 2 business days? 10/1/22 was a Saturday and 10/2/22 was a Sunday...

@Hans Vogelaar 

 

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. 

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

best response confirmed by NeilKloster (Contributor)
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.

that doesn't work if it starts on a sunday night or end on a saturday morning since you will subtract an extra day.
That said, my equation above checking if the difference is <8hrs doesn't work for <8hrs if it crosses overnight or is on a weekend or holiday.
Apologies, I feel like I did not do a good job of explaining.
So we have our business hours from 7am to 11pm and with an 11pm cut off time for any cases. So if something comes in at 10:59 PM, it's considered that same business day. If it comes in at 11:00 or after, it's considered the next business day. This is what we call our service level agreement (SLA for short).

I have a formula in excel that captures the 11pm cut-off time, so when a case shows that it came in at 10:58pm it states "Yes" (YES meaning it came in during business hours), but after 11:00pm to say "Next Day" (meaning came in the next day or after business hours). That part is good. The part that is tripping me up is my 'Business Days' where I want to calculate how old that case is, in business days.

So example

Case comes in at 10:58 PM on 10/31/22 and is not completed until 11/2/22 at 10:00 PM - that would be 2.00 business days. But if that same case came in at 11:01 PM on 10/31/22 and was completed the same time, it would be 1.00 business days because technically it would be on the next business day that it came in (past that 11:00 PM cut off).

Does that help / make any sense?

@Hans Vogelaar - this looks like it works!!