Time Calculation When Working Past Midnight That Rounds W/Lunch Punches

[blinhart]

I have a timecard sheet I am working on that I got to figure the time worked and round to the nearest quarter hour, which is what i wanted it to do. However, I can't seem to get it to work if that person takes a lunch hour and their shift goes past midnight into the next day. In row 45 when there is just an in & out punch it works fine. But if I try to add a lunch hour, I get a negative return value. One crew works 7 pm until 7:15 am the next day for one shift, then the shift rotates every 3 months to 7 am until 7:15 pm. Sometime they leave for dr appt or something and may only work 8-9 hours. Any help is appreciated.

MIN(ROUND(SUM((D45-C45)+(D45<c45))*96,0) 4,12.25)="" -="" formula="" work="" without="" a="" lunch="" hour="" punch

MIN(ROUND(SUM((F46-C46)-(E46-D46))*96,0)/4,8) - formula I am trying to use and it returns a -11.50

Cross posted https://www.excelforum.com/excel-for...h-punches.html
</c45))*96,0)>

 

[thisoldman]

The midnight date change can be difficult to deal with. Here's the formulas I came up with.

Book1
	B	C	D	E	F	G	H	I	J	K	L
44	Week	(IN)	(OUT)	(IN)	(OUT)	(IN)	(OUT)	(IN)	(OUT)	Reg Hours	OT Hours
45	SUN	19:00	07:15							12.25	0.00
46	MON	19:00	02:00	03:00	08:24					12.25	0.25
47	TUE	19:00	06:50							11.75	0.00
48	WED	19:00	02:15	03:00	08:35					12.25	0.50
Template

Cell Formulas
Range	Formula
K45	=MIN(MROUND(24*(SUMIF($C$44:$J$44,"(OUT)",C45:J45)-TRUNC(SUMIF($C$44:$J$44,"(OUT)",C45:J45)+COUNT(C45:J45)*5/48) -SUMIF($C$44:$J$44,"(IN)",C45:J45)+TRUNC(SUMIF($C$44:$J$44,"(IN)",C45:J45)+COUNT(C45:J45)*5/48)),0.25),12.25)
L45	=IFERROR(MROUND(24*(SUMIF($C$44:$J$44,"(OUT)",C45:J45)-TRUNC(SUMIF($C$44:$J$44,"(OUT)",C45:J45)+COUNT(C45:J45)*5/48) -SUMIF($C$44:$J$44,"(IN)",C45:J45)+TRUNC(SUMIF($C$44:$J$44,"(IN)",C45:J45)+COUNT(C45:J45)*5/48))-12.25,0.25),0)