# Timesheet formula not working when calculation spans 2 days

#### Bonnie Janes

##### New Member
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

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

#### Trixterz

##### Board Regular

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

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
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
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
Yeah that's correct.

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

Replies
4
Views
54
Replies
7
Views
94
Replies
1
Views
67
Replies
7
Views
92
Replies
1
Views
118