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!)
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

Trixterz

Board Regular
Joined
Aug 15, 2019
Messages
54
You need to put a check in your formula calculation in the event the shift goes past midnight...

For example cell DT24 is the starting of the shift and EE24 is the ending of the shift.
Excel Formula:
=if(DT24>EE24,("24:00"-DT24)+EE24,EE24-DT24)

You can do the same thing for break and lunch time to find out the total time.
 

Bonnie Janes

New Member
Joined
Apr 27, 2017
Messages
26
Hi Trixterz,

Thanks so much, that works, but I can't work out how to add the break and lunch bits, do they also need their own IF formula too?

"=IF(DT24>EE24,("24:00"-DT24)+EE24-(FA24-EP24)-(FW24-FL24),(EE24-DT24)-(FA24-EP24)-(FW24-FL24))" This worked for the days but not the nights.

It worked, but instead of returning 6:45 it returned 18:45, why is this?

I also need it to appear as 6.75 not 6.45 do you know how I change this?

Thank you so much.

Bonnie
 

Bonnie Janes

New Member
Joined
Apr 27, 2017
Messages
26
1602590923783.png
 

Trixterz

Board Regular
Joined
Aug 15, 2019
Messages
54

ADVERTISEMENT

Okay I guess you want the full formula...
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")

Let me know if that formula works as I am not able to test it.
 

Trixterz

Board Regular
Joined
Aug 15, 2019
Messages
54
Also if you want the time to show in a decimal format then use this code...
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

Also make sure that the cell format for that formula is changed back to General.
 

Bonnie Janes

New Member
Joined
Apr 27, 2017
Messages
26

ADVERTISEMENT

Okay I guess you want the full formula...
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")

Let me know if that formula works as I am not able to test it.

Hey - thanks for that. Still getting a #VALUE answer for the night shift with this string...
 

Bonnie Janes

New Member
Joined
Apr 27, 2017
Messages
26
Also if you want the time to show in a decimal format then use this code...
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

Also make sure that the cell format for that formula is changed back to General.
But - this (*24) works brilliantly :) and so simple!!
 

Trixterz

Board Regular
Joined
Aug 15, 2019
Messages
54
Hey - thanks for that. Still getting a #VALUE answer for the night shift with this string...

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
 

Bonnie Janes

New Member
Joined
Apr 27, 2017
Messages
26
Yeah that's correct.

I will try and write the formula myself, copying yours, sometimes that works, starting from scratch.

Appreciate your time on this.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,538
Messages
5,523,428
Members
409,522
Latest member
szophie

This Week's Hot Topics

Top