Reg Hr, OT and DT

[ThaiThao]

How would I go about calculating reg hours, OT and DT with set rules? What I made isn't taking into account 2pm through 10pm. It's just calculating the amount of hours I have. So say for example, I clock in at 6pm and clocked out at 2am. I want reg hours = 4, OT = 2, DT = 2
Reg Hours = 2pm to 10 pm
OT = 10pm - 12am
DT = 12am+

Pay Calculator.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N	O
9	Day of the Week↓		Clocked In↓		Clocked Out↓		Reg HRs Worked↓		OT Hours↓		DT Hours↓		Total Daily Hours↓
10	Monday		6:00 PM		2:00 AM		8.00		0.00		0.00		8.00
11	Tuesday		2:00 PM		10:00 PM		8.00		0.00		0.00		8.00
12	Wednesday		2:00 PM		10:00 PM		8.00		0.00		0.00		8.00
13	Thursday		2:00 PM		10:00 PM		8.00		0.00		0.00		8.00
14	Friday		2:00 PM		10:00 PM		8.00		0.00		0.00		8.00
15	Saturday								0.00		0.00		0.00
16	Sunday										0.00		0.00
Sheet1

Cell Formulas
Range	Formula
L10:L14	L10	=IF((F10-D10+(F10<D10))*24>10,(F10-D10+(F10<D10))*24-10,0)
N10:N14	N10	=SUM(H10:M10)
L15	L15	=IF((F15-D15+(F15<D15))*24>8,(F15-D15+(F15<D15))*24-8,0)
N15	N15	=SUM(J15:M15)
L16	L16	=IF((F16-D16+(F16<D16))*24>0,(F16-D16+(F16<D16))*24,0)
N16	N16	=SUM(L16)
H10:H14	H10	=MIN(8,IF((F10-D10+(F10<D10))*24>1,(F10-D10+(F10<D10))*24, 0))
J10:J14	J10	=MIN(2,IF((F10-D10+(F10<D10))*24>8,(F10-D10+(F10<D10))*24-8,0))
J15	J15	=MIN(8,IF((F15-D15+(F15<D15))*24>0,(F15-D15+(F15<D15))*24,0))

 

[awoohaw]

I could not figure out your formulas. Not saying they are wrong, but just couldn't figure them out. Maybe you will not understand mine1
but try this:

mr excel 9.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Day of the Week↓		Clocked In↓		Clocked Out↓		Reg HRs Worked↓	Suggestion	OT Hours↓	Suggestion	DT Hours↓	Suggestion	Total Daily Hours↓	Suggestion
2	Monday		18:00:00		02:00:00		0	04:00:00	0	02:00:00	0	02:00:00	0	08:00:00
3	Tuesday		14:00:00		22:00:00		0	08:00:00	0	00:00:00	0	00:00:00	0	08:00:00
4	Wednesday		14:00:00		22:00:00		0	08:00:00	0	00:00:00	0	00:00:00	0	08:00:00
5	Thursday		14:00:00		22:00:00		0	08:00:00	0	00:00:00	0	00:00:00	0	08:00:00
6	Friday		14:00:00		22:00:00		0	08:00:00	0	00:00:00	0	00:00:00	0	08:00:00
7	Saturday							00:00:00	0	00:00:00	0	00:00:00	0	00:00:00
8	Sunday							00:00:00		00:00:00	0	00:00:00	0	00:00:00
Sheet8

Cell Formulas
Range	Formula
J2:J8	J2	=IF(IF(E2<C2,E2+1,E2)>TIME(22,0,0),MIN(1-TIME(22,0,0),ABS(E2-TIME(22,0,0))),0)
K2:K6	K2	=IF((F10-D10+(F10<D10))*24>10,(F10-D10+(F10<D10))*24-10,0)
L2:L8	L2	=IF(E2<C2,E2,0)
M2:M6	M2	=SUM(H10:M10)
N2:N8	N2	=SUM(H2,J2,L2)
K7	K7	=IF((F15-D15+(F15<D15))*24>8,(F15-D15+(F15<D15))*24-8,0)
M7	M7	=SUM(J15:M15)
K8	K8	=IF((F16-D16+(F16<D16))*24>0,(F16-D16+(F16<D16))*24,0)
M8	M8	=SUM(L16)
G2:G6	G2	=MIN(8,IF((F10-D10+(F10<D10))*24>1,(F10-D10+(F10<D10))*24, 0))
I2:I6	I2	=MIN(2,IF((F10-D10+(F10<D10))*24>8,(F10-D10+(F10<D10))*24-8,0))
I7	I7	=MIN(8,IF((F15-D15+(F15<D15))*24>0,(F15-D15+(F15<D15))*24,0))
H2:H8	H2	=MIN(TIME(22,0,0)-C2,ABS(E2-C2))

 

[ThaiThao]

I could not figure out your formulas. Not saying they are wrong, but just couldn't figure them out. Maybe you will not understand mine1
but try this:

Thanks for the reply, it work handsomely. You did it way better and more simple than I could've thought of, lol.
Now, what if I worked from 12pm to 2am? Reg Hrs = 8, OT = 4, DT = 2
Reg Hours = 2pm to 10 pm
OT = 10pm - 12am (Post Shift OT), 12pm - 2pm(Pre Shift OT)
DT = 12am+
(sorry I added to my OP, didn't think about this until i tried your formula)

 

[awoohaw]

it probably will not work as the formulas i gave have hard cutoffs for the start of OT. I was only going with OT starting at 10 pm, not after 8 hours was spent.