Timesheet formula not working when calculation spans 2 days

[Bonnie Janes]

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]

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

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

 

[Trixterz]

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

Please refer to the sample excel file posted here: TimeSheet Sample.xlsx

 

[Dossfm0q]

Greetings
What about Below, Feedback Please

THX

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	Day/ Night	Travel		Work		Break		Lunch		Travel Hours	Hours Worked	Hours Worked
2		Start	Finsh	Start	Finsh	Start	Finsh	Start	Finsh
3											Value	Time
4	Day	7:00:00 AM	8:00:00 AM	8:00:00 AM	4:00:00 PM	11:45:00 AM	12:00:00 PM	1:00:00 PM	2:00:00 PM	1:00:00 AM	6.75	6:45:00 AM
5	Night	7:00:00 PM	8:00:00 PM	8:00:00 PM	4:00:00 AM	10:00:00 PM	10:15:00 PM	12:00:00 AM	1:00:00 AM	1:00:00 AM	6.75	6:45:00 AM
Sheet1

Cell Formulas
Range	Formula
B4	B4	=TODAY()+TIME(7,0,0)
C4:D4	C4	=TODAY()+TIME(8,0,0)
E4	E4	=TODAY()+TIME(16,0,0)
F4	F4	=TODAY()+TIME(11,45,0)
G4	G4	=TODAY()+TIME(12,0,0)
H4	H4	=TODAY()+TIME(13,0,0)
I4	I4	=TODAY()+TIME(14,0,0)
J4:J5	J4	=(C4-B4)
K4:K5	K4	=CONVERT((E4-D4)-(G4-F4)-(I4-H4),"day","hr")
L4:L5	L4	=(E4-D4)-(G4-F4)-(I4-H4)
B5	B5	=TODAY()+TIME(19,0,0)
C5:D5	C5	=TODAY()+TIME(20,0,0)
E5	E5	=TODAY()+1+TIME(4,0,0)
F5	F5	=TODAY()+TIME(22,0,0)
G5	G5	=TODAY()+TIME(22,15,0)
H5	H5	=TODAY()+1+TIME(0,0,0)
I5	I5	=TODAY()+1+TIME(1,0,0)

 

[Trixterz]

Use @Dossfm0q formula, its much simpler...

=CONVERT((EE24-DT24)-(FA24-EP24)-(FW24-FL24),"day","hr")

 

[Bonnie Janes]

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]

Good news !! I seem to have finally got it working using your sample and first formula Trixterz. Thank you both for your help

 

[Dossfm0q]

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]

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:

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

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]

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