Timesheet formula not working when calculation spans 2 days

Bonnie Janes

New Member
Joined
Apr 27, 2017
Messages
26
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

New Member
Joined
Apr 27, 2017
Messages
26
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.

1602758375548.png


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

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Trixterz

Board Regular
Joined
Aug 15, 2019
Messages
59
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...
VBA 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

Board Regular
Joined
Aug 15, 2019
Messages
59
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"...

VBA Code:
=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

New Member
Joined
Apr 27, 2017
Messages
26
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

New Member
Joined
Apr 27, 2017
Messages
26

ADVERTISEMENT

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

Trixterz

Board Regular
Joined
Aug 15, 2019
Messages
59
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

Active Member
Joined
Mar 9, 2009
Messages
399
@ 1.5 5 ???
VBA Code:
=IF(D5<>"",(($E5-$D5)-($G5-$F5)-($I5-$H5))*(24),"")

Book1
ABCDEFGHIJKLMNOPQRS
1Day/ NightTravelWorkBreakLunchTravel HoursWork HoursHours WorkedHours Worked @1.5Hours Worked @2Minutes WorkedSeconds Worked
2StartFinshStartFinshStartFinshStartFinsh
3
4TimeValues
5Day7:00:00 AM8:00:00 AM8:00:00 AM4:00:00 PM11:45:00 AM12:00:00 PM1:00:00 PM2:00:00 PM1:00:00 AM6:45:00 AM6.7540524300
6Night7:00:00 PM8:00:00 PM8:00:00 PM4:00:00 AM10:00:00 PM10:15:00 PM12:00:00 AM1:00:00 AM1:00:00 AM6:45:00 AM6.7540524300
7Day8:00:00 AM9:00:00 AM9:00:00 AM5:00:00 PM11:00:00 AM11:15:00 AM1:00:00 PM2:00:00 PM1:00:00 AM6:45:00 AM6.7540524300
8Night8:00:00 PM9:00:00 PM9:00:00 PM5:00:00 AM10:00:00 PM10:15:00 PM11:00:00 PM12:00:00 AM1:00:00 AM6:45:00 AM6.7540524300
9Night7:00:00 PM8:00:00 PM8:00:00 PM5:00:00 AM10:45:00 PM11:00:00 PM12:00:00 AM1:00:00 AM1:00:00 AM7:45:00 AM7.7546527900
10   
11   
12   
13   
14   
15   
16   
17   
18   
19   
205.0034.7534.75
21
22
23
24Those 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
RangeFormula
B5B5=TODAY()+TIME(7,0,0)
B7,C5:D5C5=TODAY()+TIME(8,0,0)
E5E5=TODAY()+TIME(16,0,0)
F5F5=TODAY()+TIME(11,45,0)
G5G5=TODAY()+TIME(12,0,0)
H5,H7H5=TODAY()+TIME(13,0,0)
I5,I7I5=TODAY()+TIME(14,0,0)
J5:J9J5=(C5-B5)
K5:K9K5=(($E5-$D5)-($G5-$F5)-($I5-$H5))
B6,B9B6=TODAY()+TIME(19,0,0)
C9:D9,B8,C6:D6C6=TODAY()+TIME(20,0,0)
E6E6=TODAY()+1+TIME(4,0,0)
F6,F8F6=TODAY()+TIME(22,0,0)
G6,G8G6=TODAY()+TIME(22,15,0)
H6,H9,I8H6=TODAY()+1+TIME(0,0,0)
I6,I9I6=TODAY()+1+TIME(1,0,0)
C7:D7C7=TODAY()+TIME(9,0,0)
E7E7=TODAY()+TIME(17,0,0)
F7F7=TODAY()+TIME(11,0,0)
G7G7=TODAY()+TIME(11,15,0)
C8:D8C8=TODAY()+TIME(21,0,0)
E8:E9E8=TODAY()+1+TIME(5,0,0)
H8H8=TODAY()+0+TIME(23,0,0)
F9F9=TODAY()+TIME(22,45,0)
G9G9=TODAY()+TIME(23,0,0)
Q5:Q19Q5=IF(D5<>"",(($E5-$D5)-($G5-$F5)-($I5-$H5))*(1440),"")
R5:R19R5=IF(D5<>"",(($E5-$D5)-($G5-$F5)-($I5-$H5))*(86400),"")
L5:L19L5=IF(D5<>"",(($E5-$D5)-($G5-$F5)-($I5-$H5))*(24),"")
J20:K20J20=SUM(J$5:J$9)*24
L20L20=SUM(L$5:L$9)
 

Watch MrExcel Video

Forum statistics

Threads
1,113,744
Messages
5,543,955
Members
410,586
Latest member
acadavid86
Top