Forum Discussion
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 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.
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.
7 Replies
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.
