Time Formula

[rkramapo]

Hi- I need help with a formula for time. I wanted to see how to edit the formula so that it takes into account 1/2 hour break for every 5 hours.

BID Schedule 2025 BTI.xls
	A	B	C	D	E	F	G	H
41	Employee's		Friday			Saturday
42	Name		Shift		Hours	Shift		Hours
43		Super	7:30 AM	4:00 PM	8.00	8:00 AM	4:00 PM	7.50
44		Super			0.00			0.00
45		Super	4:00 PM	12:15 AM	7.75	4:00 PM	11:30 PM	7.00
46		Cash 1	7:00 AM	3:30 PM	8.00	9:00 AM	3:30 PM	6.00
47		Cash 2			0.00			0.00
48		Cash 3	3:00 PM	11:30 PM	8.00	3:00 PM	10:30 PM	7.00
49		FSW 1	7:30 AM	3:30 PM	7.50	8:30 AM	4:00 PM	7.00
50		FSW 2	9:00 AM	4:00 PM	6.50	9:00 AM	4:15 PM	6.75
51		FSW 3			0.00			0.00
52		FSW 4	4:00 PM	11:45 PM	7.25			0.00
53		FSW 5			0.00	3:30 PM	10:30 PM	6.50
54		FSW6	12:00 PM	8:30 PM	8.00	12:00 PM	8:30 PM	8.00
55		FSW 7	4:00 PM	11:45 PM	7.25	4:00 PM	11:00 PM	6.50
56		Grill 1	6:30 AM	3:00 PM	8.00			0.00
57		Grill 2	12:00 PM	8:30 PM	8.00			0.00
58		Grill 3			0.00	9:00 AM	5:00 PM	7.50
59		Grill 4	4:00 PM	11:45 PM	7.25			0.00
60		Grill 5	4:00 PM	11:45 PM	7.25			0.00
61		Grill 6			0.00	3:30 PM	11:45 PM	7.75
62		Grill 7			0.00	8:00 AM	5:00 PM	8.50
63		Grill 8	4:00 PM	11:45 PM	7.25	3:30 PM	11:45 PM	7.75
64		Grill 9	9:00 AM	5:30 PM	8.00			0.00
65		Cook 1	6:30 AM	3:00 PM	8.00			0.00
66		Cook 2	8:30 AM	5:00 PM	8.00			0.00
67		Cook 3	12:00 PM	7:30 PM	7.00	8:00 AM	6:30 PM	10.00
Schedule

Cell Formulas
Range	Formula
E43:E67,H43:H67	E43	=IF(D43*24-C43*24<5, D43*24-C43*24, D43*24-C43*24-0.5)

 

[Dave Patton]

What are the expected results?
Consider

Time 2023.xlsm
	A	B	C	D	E
41	Employee's		Friday
42	Name		Shift		Hours
43		Super	07:30	16:00	8
44		a	07:30	22:45	13.75
45		b	08:00	18:30	9.5
46
3c

Cell Formulas
Range	Formula
E43:E45	E43	=LET(t,(D43-C43)*24, IF(t<5,t,t-INT(t/5)*0.5))