Forum Discussion
calculate business hours in excel
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.
Why 3 / 2 business days? 10/1/22 was a Saturday and 10/2/22 was a Sunday...
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.
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.
- HansVogelaar - this looks like it works!!
- 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?
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 workEDIT: 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))
