Forum Discussion
NeilKloster
Nov 15, 2022Brass Contributor
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 ...
- Nov 15, 2022
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.
NeilKloster
Nov 15, 2022Brass Contributor
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.
HansVogelaar
Nov 15, 2022MVP
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.
- NeilKlosterNov 16, 2022Brass ContributorHansVogelaar - this looks like it works!!
- mtarlerNov 15, 2022Silver Contributorthat 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.- NeilKlosterNov 16, 2022Brass ContributorApologies, 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?