Overtime Formula

[Robert Wyatt]

I'm including a L2BB could someone please check it out and tell me where I'm going wrong with the overtime. I works if i use time in 08:00 til 16:30 it will show half hour overtime, but If I use 23:55 til 08:15 it doesn't show the overtime.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2
3
4
5
6
7
8
9
10
11
12			Total Bi-Weekly Hour	Total Hours Worked	Total Tip Appliance Hours	Total Regular Hours	Total Overtime Hours			Training Meeting Hours	Total PTO Hours	Total FMLA Hours	Total Vacation Hours
13			80.00	60.07	60.07	60.07	0.00			0.00	0.00	0.00	0.00
14
15			Regular Work Hours					Training / Meeting Hours
16	Day of Week	Options	Time In	Time Out	Hours Woirked	Regular Hours	Overtime Hours	Time In	Time Out	Total Hours	PTO Hours	FMLA Hours	Vacation Hours
17	Fri 5/19		11:55 PM	7:59 AM	8.07	8.07	0.00			0.00	0.00	0.00	0.00
18	Sat 5/20		11:55 PM	7:55 AM	8.00	8.00	0.00			0.00	0.00	0.00	0.00
19	Sun 5/21				0.00	0.00	0.00			0.00	0.00	0.00	0.00
20	Mon 5/22				0.00	0.00	0.00			0.00	0.00	0.00	0.00
21	Tue 5/23				0.00	0.00	0.00			0.00	0.00	0.00	0.00
22	Wed 5/24		11:55 PM	7:55 AM	8.00	8.00	0.00			0.00	0.00	0.00	0.00
23	Thu 5/25		11:55 PM	5:55 AM	6.00	6.00	0.00			0.00	0.00	0.00	0.00
24	Fri 5/26		11:55 PM	7:55 AM	8.00	8.00	0.00			0.00	0.00	0.00	0.00
25	Sat 5/27		11:55 PM	7:55 AM	8.00	8.00	0.00			0.00	0.00	0.00	0.00
26	Sun 5/28				0.00	0.00	0.00			0.00	0.00	0.00	0.00
27	Mon 5/29				0.00	0.00	0.00			0.00	0.00	0.00	0.00
28	Tue 5/30				0.00	0.00	0.00			0.00	0.00	0.00	0.00
29	Wed 5/31		11:55 PM	7:55 AM	8.00	8.00	0.00			0.00	0.00	0.00	0.00
30	Thu 6/1		11:55 PM	5:55 AM	6.00	6.00	0.00			0.00	0.00	0.00	0.00
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
Sheet1

Cell Formulas
Range	Formula
D13:E13	D13	=SUM(E17:E30)
J13:M13,F13:G13	F13	=SUM(F17:F30)
J17:J30,E17:E30	E17	=ROUND(IF((OR(C17="",D17="")),0,IF((D17<C17),((D17-C17)*24)+24,(D17-C17)*24)),2)
F17:F30	F17	=E17-G17
G17:G30	G17	=IFERROR(IF(((D17-C17)+D17-C17)*24>8,((D17-C17)+(D17-C17))*24-8,0), "")

 

[awoohaw]

You need to test to be sure the end time is the next day, and if so, add 24 hours (1 day) to that value.

Mr excel questions 63.xlsm
	A	B	C	D	E	F	G
1
2
3
4
5
6
7
8
9
10
11
12			Total Bi-Weekly Hour	Total Hours Worked	Total Tip Appliance Hours	Total Regular Hours	Total Overtime Hours
13			80	60.06666667	60.06666667	60.06666667	0
14
15			Regular Work Hours
16	Day of Week	Options	Time In	Time Out	Hours Woirked	Regular Hours	Overtime Hours
17	2023/May/19 Fri		23:55	07:59	8.066666667	8.066666667	0
18	2023/May/20 Sat		23:55	07:55	8	8	0
19	2023/May/21 Sun				0	0	0
20	2023/May/22 Mon				0	0	0
21	2023/May/23 Tue				0	0	0
22	2023/May/24 Wed		23:55	07:55	8	8	0
23	2023/May/25 Thu		23:55	05:55	6	6	0
24	2023/May/26 Fri		23:55	07:55	8	8	0
25	2023/May/27 Sat		23:55	07:55	8	8	0
26	2023/May/28 Sun				0	0	0
27	2023/May/29 Mon				0	0	0
28	2023/May/30 Tue				0	0	0
29	2023/May/31 Wed		23:55	07:55	8	8	0
30	2023/Jun/01 Thu		23:55	05:55	6	6	0
Robert Wyatt

Cell Formulas
Range	Formula
D13:E13	D13	=SUM(E17:E30)
F13:G13	F13	=SUM(F17:F30)
E17:E30	E17	=((IF(D17<C17,1,0)+A17+D17)-(A17+C17))*24
F17:F30	F17	=E17-G17
G17:G30	G17	=IFERROR(IF(((D17-C17)+D17-C17)*24>8,((D17-C17)+(D17-C17))*24-8,0), "")

 

[Dave Patton]

paste the the following into a clean sheet and review the formulas

T202309a.xlsm
	A	B	C	D	E	F	G	H
15			Regular Work Hours					08:00
16	Day of Week	Options	Time In	Time Out	Hours Worked	Regular Hours	Overtime Hours
17			23:55	07:59	8.06666667	8	0.06666667
18			23:55	08:15	8.33333333	8	0.33333333
19
20			23:55	07:59	08:04	08:00	00:04
21	45068		23:55	08:15	08:20	08:00	00:20
1e

Cell Formulas
Range	Formula
E17:E18	E17	=(D17-C17+(C17>D17))*24
F17:F18	F17	=MIN(8,E17)
G17:G18,G20:G21	G17	=E17-F17
E20:E21	E20	=(D20-C20+(C20>D20))
F20:F21	F20	=MIN($H$15,E20)

T202309a.xlsm
	A	B	C	D	E	F	G
11
12			Total Bi-Weekly Hour	Total Hours Worked	Total Tip Appliance Hours	Total Regular Hours	Total Overtime Hours
13			80	24.4	24.40	24	0.4
14
15			Regular Work Hours
16	Day of Week	Options	Time In	Time Out	Hours Woirked	Regular Hours	Overtime Hours
17	45065		23:55	07:59	8.07	8	0.0666667
18	45066		23:55	07:55	8.00	8	0
19	45067		23:55	08:15	8.33	8	0.3333333
20
1f

Cell Formulas
Range	Formula
D13	D13	=SUM(E17:E19)
E13:G13	E13	=SUM(E17:E19)
E17:E19	E17	=(D17-C17+(C17>D17))*24
F17:F19	F17	=MIN(8,E17)
G17:G19	G17	=E17-F17