Timesheet formula not working when calculation spans 2 days

[Bonnie Janes]

Hi, I have a formula that works until the times involved span 2 days: Formula is "=TEXT((EE24-DT24)-(FA24-EP24)-(FW24-FL24),"h:mm")" it first works out the hours of the shift (EE24-DT24) then takes off break (FA24-EP24) and lunch (FW24-FL24), it returns total hours for each shift. The problem I have is with night shifts that span 2 days; as soon as anytime is entered past midnight it returns #VALUE.

The source information is formatted as "Time - 1:30 PM"

What can I do?

(My boss also wants the total returned, say for seven and a half hours, as 7.5 as opposed to 7:30. Which I haven't yet worked out either!)

 

[Bonnie Janes]

Thank you Trixterz, you have been so helpful.

I now have the same problem with my travel hours formula. It's returning 6:30 instead of 6.5. I've tried adding *24 to the end of the formula, as we did with the other one but it doesn't work.

formula is =TEXT(DI24-CX24,"h:mm")

 

[Trixterz]

All working!

But now I have another problem. I've set conditional formatting up to make the zeros disappear, it's worked in the "Hours Worked" column, but not for the "Travel Hours column. It has the same conditional formatting and the cells have the same format. I can't work it out. Tried adding an =iferror , which I usually use to get rid of unsightly answers, but that's not working.

Any ideas?

View attachment 24248

I was going to attach the file but can't work out how to!! I'm new to Mr Excel!

Get rid of zeros with this code...

=IF(SUM(DT24:EE24)<>0,TEXT(
IFERROR(IF(OR(DT24="",EE24=""),"",IF(DT24>EE24,("24:00"-DT24)+EE24,EE24-DT24)),"")-
IFERROR(IF(OR(EP24="",FA24=""),"",IF(EP24>FA24,("24:00"-EP24)+FA24,FA24-EP24)),"")-
IFERROR(IF(OR(FL24="",FW24=""),"",IF(FL24>FW24,("24:00"-FL24)+FW24,FW24-FL24)),""),"h:mm")*24,"")

 

[Trixterz]

Thank you Trixterz, you have been so helpful.

I now have the same problem with my travel hours formula. It's returning 6:30 instead of 6.5. I've tried adding *24 to the end of the formula, as we did with the other one but it doesn't work.

View attachment 24249

formula is =TEXT(DI24-CX24,"h:mm")

Use the code below and you need to change the cell numbering format to "General"...

=TEXT(DI24-CX24,"h:mm")*24

P.S. You're going to have to pay me pretty soon for all of this work... just kidding.

 

[Bonnie Janes]

OMG so simple when you know how isn't it. You've been amazing. Thank you so much! xx

I probably can't help you with excel.... but if you want any cooking or ****tail tips I'm ya gal!

 

[Bonnie Janes]

It edited me.... mixed alcoholic drink tips, maybe I should have said!

 

[Trixterz]

Well hope you finally got your time sheet squared away; because after a few alcohol; I wont be able to be helpful to anyone.

 

[Dossfm0q]

@ 1.5 5 ???

=IF(D5<>"",(($E5-$D5)-($G5-$F5)-($I5-$H5))*(24),"")

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	Day/ Night	Travel		Work		Break		Lunch		Travel Hours	Work Hours	Hours Worked	Hours Worked @1.5	Hours Worked @2			Minutes Worked	Seconds Worked
2		Start	Finsh	Start	Finsh	Start	Finsh	Start	Finsh
3
4											Time	Values
5	Day	7:00:00 AM	8:00:00 AM	8:00:00 AM	4:00:00 PM	11:45:00 AM	12:00:00 PM	1:00:00 PM	2:00:00 PM	1:00:00 AM	6:45:00 AM	6.75					405	24300
6	Night	7:00:00 PM	8:00:00 PM	8:00:00 PM	4:00:00 AM	10:00:00 PM	10:15:00 PM	12:00:00 AM	1:00:00 AM	1:00:00 AM	6:45:00 AM	6.75					405	24300
7	Day	8:00:00 AM	9:00:00 AM	9:00:00 AM	5:00:00 PM	11:00:00 AM	11:15:00 AM	1:00:00 PM	2:00:00 PM	1:00:00 AM	6:45:00 AM	6.75					405	24300
8	Night	8:00:00 PM	9:00:00 PM	9:00:00 PM	5:00:00 AM	10:00:00 PM	10:15:00 PM	11:00:00 PM	12:00:00 AM	1:00:00 AM	6:45:00 AM	6.75					405	24300
9	Night	7:00:00 PM	8:00:00 PM	8:00:00 PM	5:00:00 AM	10:45:00 PM	11:00:00 PM	12:00:00 AM	1:00:00 AM	1:00:00 AM	7:45:00 AM	7.75					465	27900
10
11
12
13
14
15
16
17
18
19
20										5.00	34.75	34.75
21
22
23
24					Those cells start with New Day for exact and perfect calculations
25					"E5"=TODAY()+1+TIME(4,0,0)
26					"H5"=TODAY()+1+TIME(0,0,0)
27					"I5"=TODAY()+1+TIME(1,0,0)
28
29					"H8" TODAY()+0+TIME(23,0,0)
Sheet1

Cell Formulas
Range	Formula
B5	B5	=TODAY()+TIME(7,0,0)
B7,C5:D5	C5	=TODAY()+TIME(8,0,0)
E5	E5	=TODAY()+TIME(16,0,0)
F5	F5	=TODAY()+TIME(11,45,0)
G5	G5	=TODAY()+TIME(12,0,0)
H5,H7	H5	=TODAY()+TIME(13,0,0)
I5,I7	I5	=TODAY()+TIME(14,0,0)
J5:J9	J5	=(C5-B5)
K5:K9	K5	=(($E5-$D5)-($G5-$F5)-($I5-$H5))
B6,B9	B6	=TODAY()+TIME(19,0,0)
C9:D9,B8,C6:D6	C6	=TODAY()+TIME(20,0,0)
E6	E6	=TODAY()+1+TIME(4,0,0)
F6,F8	F6	=TODAY()+TIME(22,0,0)
G6,G8	G6	=TODAY()+TIME(22,15,0)
H6,H9,I8	H6	=TODAY()+1+TIME(0,0,0)
I6,I9	I6	=TODAY()+1+TIME(1,0,0)
C7:D7	C7	=TODAY()+TIME(9,0,0)
E7	E7	=TODAY()+TIME(17,0,0)
F7	F7	=TODAY()+TIME(11,0,0)
G7	G7	=TODAY()+TIME(11,15,0)
C8:D8	C8	=TODAY()+TIME(21,0,0)
E8:E9	E8	=TODAY()+1+TIME(5,0,0)
H8	H8	=TODAY()+0+TIME(23,0,0)
F9	F9	=TODAY()+TIME(22,45,0)
G9	G9	=TODAY()+TIME(23,0,0)
Q5:Q19	Q5	=IF(D5<>"",(($E5-$D5)-($G5-$F5)-($I5-$H5))*(1440),"")
R5:R19	R5	=IF(D5<>"",(($E5-$D5)-($G5-$F5)-($I5-$H5))*(86400),"")
L5:L19	L5	=IF(D5<>"",(($E5-$D5)-($G5-$F5)-($I5-$H5))*(24),"")
J20:K20	J20	=SUM(J$5:J$9)*24
L20	L20	=SUM(L$5:L$9)