Timesheet formula not working when calculation spans 2 days

Bonnie Janes

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

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
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.
 
Upvote 0
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
 
Upvote 0
1602590923783.png
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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...
 
Upvote 0
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!!
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,728
Members
448,294
Latest member
jmjmjmjmjmjm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top