Time formula

[rkramapo]

Hi- I have a question on how I can edit this IF formula to reflect the correct time when an employee schedule end time is at 12am. The total works for times after 12am. Let's say cell p40 is 6p and cell o40 is 12am and any hours worked greater than 5 requires a half hour break- the total hours pops up as -18

=IF(P40*24-O40*24<5, P40*24-O40*24, P40*24-O40*24-0.5)

 

[Dave Patton]

With Excel 2016, try the second formula

Dates and Time 2024.xlsm
	O	P	Q	R
39	Start	End
40	18:00:00	12:00	17.50	17.5
41
2e

Cell Formulas
Range	Formula
Q40	Q40	=LET(t,(P40-O40+(O40>P40))*24,IF(t<5,t,t-0.5))
R40	R40	=IF((P40-O40+(O40>P40))*24<5,(P40-O40+(O40>P40))*24,(P40-O40+(O40>P40))*24-0.5)

 

[rkramapo]

With Excel 2016, try the second formula

Dates and Time 2024.xlsm
	O	P	Q	R
39	Start	End
40	18:00:00	12:00	17.50	17.5
41
2e

Cell Formulas
Range	Formula
Q40	Q40	=LET(t,(P40-O40+(O40>P40))*24,IF(t<5,t,t-0.5))
R40	R40	=IF((P40-O40+(O40>P40))*24<5,(P40-O40+(O40>P40))*24,(P40-O40+(O40>P40))*24-0.5)

awesome thanks!

 

[Dave Patton]

Review the formula with Excel's Formula Evaluation

Dates and Time 2024.xlsm
	A	B	C	D	E	F
1	Employee's		Friday
2	Name		Shift		Hours
3	Miledy	Super	7:30	16:00	8	8.00
4	Steve	Super			0	0.00
5	Jordan	Super	16:00	0:15	7.75	7.75
6	Janet G	Cash 1	7:00	15:30	8	8.00
7	Danna	Cash 2			0	0.00
8	Charlotte	Cash 3	15:00	23:30	8	8.00
9	Luis C	FSW 1	7:30	15:30	7.5	7.50
10	John B.	FSW 2	9:00	16:00	6.5	6.50
11	Julissa	FSW 3			0	0.00
12	LORETA	FSW 4	16:00	23:45	7.25	7.25
13	Alicia	FSW4			0	0.00
14	Martha	FSW6	12:00	20:30	8	8.00
15	Lucy	FSW 14	16:00	23:45	7.25	7.25
16	CARLOS	Grill 1	6:30	15:00	8	8.00
17	Lionel D.	Grill 2	11:45	20:15	8	8.00
18	Patty C	Grill 3			0	0.00
19	Ysabel R	Grill 4	16:00	0:15	7.75	7.75
20	Clara R.	Grill 5	16:00	23:45	7.25	7.25
21	William	Grill 6			0	0.00
22	Ryan	Grill 7			0	0.00
23	Patrick	Grill 8	16:00	23:45	7.25	7.25
24	TINA D	COOK 1	7:30	16:00	8	8.00
25	OPEN	Cook 2	11:00	18:00	6.5	6.50
26	BRIAN C.	Cook 3	6:30	15:00	8	8.00
27	GAVIN	Cook 4	9:00	17:15	7.75	7.75
28	Mary	Cook 5	9:15	17:45	8	8.00
29	Radcliff	COOK 6	12:00	20:30	8	8.00
30	BRIAN O.	UT 1	7:30	14:30	6.5	6.50
31	Felipe	UT 2	9:00	17:30	8	8.00
32	Jean T.	UT 3	16:00	0:15	7.75	7.75
33	MELVIN	UT 4	16:00	0:15	7.75	7.75
34	EMILIO	UT 5			0	0.00
35	Elima	UT 6	16:00	0:15	7.75	7.75
36	Cesar	UT 7			0	0.00
37	OPEN	UT 8			0	0.00
38	Student		18:00		-18	5.50
2ee

Cell Formulas
Range	Formula
E3:E38	E3	=IF(D3*24-C3*24<5, D3*24-C3*24, D3*24-C3*24-0.5)
F3:F38	F3	=IF((D3-C3+(C3>D3))*24<5,(D3-C3+(C3>D3))*24,(D3-C3+(C3>D3))*24-0.5)

There are several workarounds for Midnight. One is to enter 1 second instead of leaving the cell blank.

Student

18:00

0:00

-18.00​

5.50​