Forum Discussion
Solved
calculate business hours in excel
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 ...
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.
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?
