hour difference between two dates/times, exclude weekends and holidays

Originally Posted by protonLeah

Try NETWORKDAYS()

This formula yields a turnaround time of 72 hrs. This formula does not account for the weekend. It also does not change the time in to the next business day at 9am

Originally Posted by daddylonglegs

Is the "time out" ever on a weekend?

For your example you want the answer 51:00, what if the time in was a Friday at 10:00 and the time out a Monday at 15:00, how many hours is that?

In that case, when the "time in" was Friday at 10 am and "time out" on Monday is at 3 PM, the turnaround time would be 14 hrs. I considered 8am-5pm business hrs.

If the time in was either Saturday or Sunday at 10am, and the time out on monday is at 3pm, the turnaround time would be be 8 hrs (from 8am till 3pm on monday).

Also, if a time in is on a holiday such as Dec 25th(wed) at 10am and time out is Dec 26th(thurs) at 3pm, the turnaround time would be 8 hrs as well.

Any "time in" date should be counted as the next business day at 8am. Sorry if this is very confusing. I really appreciate your help on this.

Originally Posted by daddylonglegs

OK but given that logic surely Sunday at 12:00 until Wednesday at 12:00 should be 22:00 (8:00 to 17:00 on both Monday and Tuesday and 8:00 until 12:00 on Wednesday)?

Try this formula

=(NETWORKDAYS(A2,B2)-1)*("17:00"-"8:00")+MOD(B2,1)-MEDIAN(NETWORKDAYS(A2,A2)*MOD(A2,1),"8:00","17:00")

That counts all the working hours assuming that B2 will always be within working hours but A2 may not - you can add a holiday range to both NETWORKDAYS functions to exclude holidays too, i.e.

=(NETWORKDAYS(A2,B2,holidays)-1)*("17:00"-"8:00")+MOD(B2,1)-MEDIAN(NETWORKDAYS(A2,A2,holidays)*MOD(A2,1),"8:00","17:00")

format result cell as [h]:mm

Very close. I think I communicated incorrectly.

Sunday at 12:00 until Wednesday at 12:00 should be 52:00. Time in would start monday at 8:00. tuesday at 8:00 would be 24 hrs. Wed at 8 would be 48 hrs. wed at 12:00 would be 52:00 Since I am concerned with the number of hours during business days, not only business hrs.