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)

 

[Joe4]

In the example you laid out, if I recreate it as you have you have described, it get 17.5, not -18. What we don't know is if you have a date component to go along with your time piece.

In cases like this, it is often very helpful if you post a small sample of your data showing different circumstances/situations, along with your expected results, as so much is dependent upon your actual data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Dave Patton]

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

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

 

[rlv01]

P40-O40+(O40>P40)

@Dave Patton - a nice bit of typecasting there.

 

[rkramapo]

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

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

 

[rkramapo]

Sorry- first time poster- so the highlighted shift under student should be 5.5 not -18. How can I fix that formula so it can recognize that.

 

[Joe4]

Sorry- first time poster- so the highlighted shift under student should be 5.5 not -18. How can I fix that formula so it can recognize that.

Use Dave Patton's formula from post 3.
Adjusted for your new image which has data in columns C and D instead of O and P, put this formula in cell E3 and copy down to cell E38:

=LET(t,(D3-C3+(C3>D3))*24,IF(t<5,t,t-0.5))

 

[rkramapo]

Use Dave Patton's formula from post 3.
Adjusted for your new image which has data in columns C and D instead of O and P, put this formula in cell E3 and copy down to cell E38:

=LET(t,(D3-C3+(C3>D3))*24,IF(t<5,t,t-0.5))

I used his formula, but it is giving me an error with the formula- invalid name formula- is it because I'm using excel 2016?

 

[Joe4]

I used his formula, but it is giving me an error with the formula- invalid name formula- is it because I'm using excel 2016?

Yes, LET is a new function.

But if you are using Excel 2016, why did you list 365 in your profile???

 

[rkramapo]

Yes, LET is a new function.

But if you are using Excel 2016, why did you list 365 in your profile???
View attachment 107650

Sorry- I was switching between desktops where one had 2016 and one had 365- should have checked off more than one version.