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
You may need to correct the formula to the right cell address.

Base on the info received this is how the formula is setup base on cell address:
DT24 = Work Start
EE24 = Work Finished

EP24 = Break Start
FA24 = Break End

FL24 = Lunch Start
FW24 = Lunch End

Excel Formula:
=TEXT((IF(DT24>EE24,("24:00"-DT24)+EE24,EE24-DT24)-IF(EP24>FA24,("24:00"-EP24)+FA24,FA24-EP24)-IF(FL24>FW24,("24:00"-FL24)+FW24,FW24-FL24)),"h:mm")*24


Hi Trixterz,

It's still not working - do you have any ideas. I can send you the document to look at if it's easier...?
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Dossfm0q

Active Member
Joined
Mar 9, 2009
Messages
355
Greetings
What about Below, Feedback Please

THX
Book1
ABCDEFGHIJKL
1Day/ NightTravelWorkBreakLunchTravel HoursHours WorkedHours Worked
2StartFinshStartFinshStartFinshStartFinsh
3ValueTime
4Day7: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.756:45:00 AM
5Night7: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.756:45:00 AM
Sheet1
Cell Formulas
RangeFormula
B4B4=TODAY()+TIME(7,0,0)
C4:D4C4=TODAY()+TIME(8,0,0)
E4E4=TODAY()+TIME(16,0,0)
F4F4=TODAY()+TIME(11,45,0)
G4G4=TODAY()+TIME(12,0,0)
H4H4=TODAY()+TIME(13,0,0)
I4I4=TODAY()+TIME(14,0,0)
J4:J5J4=(C4-B4)
K4:K5K4=CONVERT((E4-D4)-(G4-F4)-(I4-H4),"day","hr")
L4:L5L4=(E4-D4)-(G4-F4)-(I4-H4)
B5B5=TODAY()+TIME(19,0,0)
C5:D5C5=TODAY()+TIME(20,0,0)
E5E5=TODAY()+1+TIME(4,0,0)
F5F5=TODAY()+TIME(22,0,0)
G5G5=TODAY()+TIME(22,15,0)
H5H5=TODAY()+1+TIME(0,0,0)
I5I5=TODAY()+1+TIME(1,0,0)
 

Bonnie Janes

New Member
Joined
Apr 27, 2017
Messages
26

ADVERTISEMENT

Hi guys,

That is a lot simpler but the night shift is still giving me a minus answer! How do I get round this?

Also the cells for the times will be blank, the employee fills them in, so they can't contain formulas....
 

Bonnie Janes

New Member
Joined
Apr 27, 2017
Messages
26
Good news !! I seem to have finally got it working using your sample and first formula Trixterz. Thank you both for your help :)
 

Dossfm0q

Active Member
Joined
Mar 9, 2009
Messages
355

ADVERTISEMENT

the height Yellows means serial Time, because we on new Day after 00:00 so I add +1 to Today to be in new day " =TODAY()+1+TIME(1,0,0) "
 

Trixterz

Board Regular
Joined
Aug 15, 2019
Messages
54
Hi guys,

That is a lot simpler but the night shift is still giving me a minus answer! How do I get round this?

Also the cells for the times will be blank, the employee fills them in, so they can't contain formulas....

Okay I am not familiar with Convert so I don't want to get into it... But refer to my excel sample sheet linked here for alternative: TimeSheet Sample.xlsx

It uses the following code will all errors eliminated. This code should fix all the issues you have encounter with all other previous code:
Excel Formula:
=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
 

Bonnie Janes

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

1602756940739.png


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

Watch MrExcel Video

Forum statistics

Threads
1,109,411
Messages
5,528,617
Members
409,828
Latest member
99DodgeRam

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top