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