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

#### Bonnie Janes

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

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

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)

#### Trixterz

##### Board Regular
Use @Dossfm0q formula, its much simpler...
Excel Formula:
``=CONVERT((EE24-DT24)-(FA24-EP24)-(FW24-FL24),"day","hr")``

#### Bonnie Janes

##### New Member

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

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

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

#### Trixterz

##### Board Regular
Good news !! I seem to have finally got it working using your sample and first formula Trixterz. Thank you both for your help
See the updated sample file with all possible error fixes such as when employee input text into the time sheet... TimeSheet Sample.xlsx

Replies
4
Views
121
Replies
7
Views
124
Replies
1
Views
92
Replies
7
Views
107
Replies
1
Views
140

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