Excel formula for overnight hours within a specific time period

[MellieD]

I need help to create an Excel formula to see how many overnight hours have been worked during a particular period for billing purposes. Our employees’ clock in and out at various times and I need to exclude the time outside of the hours of 11pm to 6am. We pay our employees for all the time they work but we can only bill our customer for time worked between 11pm and 6am the next day. Below is an example of several time punch scenarios to calculate how much we can bill to our customer. I’ve tried several IF, MOD and TIMEVALUE formulas but nothing seems to work due to the extra complexity of calculating time over two day period. For instance, the perfect scenario is in row 2 where employee clocked in at 11pm and clocked out at 6am. 7 hours is billed to the customer.

In row 3, the employee clocked in 5 minutes early and clocked out 10 minutes later. We will pay the employee 7 hours and 15 minutes but only bill the customer 7 hours.

Row 4 would be billed at 30 minutes and so on. Row 11 we would not bill the customer for any hours due to the entire time being outside of the 11pm to 6am window.

I know these examples are somewhat simplistic in their clock in/clock out nature. I would prefer to stay away from VBA as I don’t understand it and have no experience with it. Any suggestions on formulas I can use to cover all of these scenarios? Total time worked and time only between 11pm and 6am?
Thank you

 

[Toadstool]

Hi MellieD,

The tricky part is figuring out what the dates are for the 11pm start and 6am end. This formula assumes if the Clock In is after 12 noon then you're looking for 11pm the Date In to 6am the next day. If the Clock In is before 12 noon then you are looking at 11pm the day before Date In to 6am of the Date In.

MellieD.xlsx
	A	B	C	D	E
1	Date In	Clock In	Date Out	Clock Out	Overlap with 11pm to 6am
2	12/31/2021	23:00	1/1/2022	6:00	7:00
3	12/31/2021	22:55	1/1/2022	6:10	7:00
4	1/1/2022	23:15	1/1/2022	23:45	0:30
5	1/1/2022	22:50	1/2/2022	5:00	6:00
6	1/1/2022	23:02	1/2/2022	6:15	6:58
7	1/1/2022	23:05	1/2/2022	5:45	6:40
8	1/2/2022	0:00	1/2/2022	6:00	6:00
9	1/2/2022	1:00	1/2/2022	4:00	3:00
10	1/2/2022	1:00	1/2/2022	6:30	5:00
11	1/2/2022	19:00	1/2/2022	22:00	0:00
1st

Cell Formulas
Range	Formula
E2:E11	E2	=MAX(MIN(A2+TIME(6,0,0)+(IF(B2>TIME(12,0,0),1,0)),C2+D2)-MAX(A2+B2,A2+TIME(23,0,0)+(IF(B2>TIME(12,0,0),0,-1))),0)

 

[MellieD]

Hi Toadstool,
Good point about taking the dates into consideration for the 11pm start and 6am end. I totally overlooked that aspect.
Thank you for this solution. It works perfectly.

 

[Toadstool]

You're welcome.
Thanks for the feedback.