How to display -/+ variences with time?

CP4840

New Member
Joined
May 4, 2005
Messages
17
I have having an issue with calculating the difference between two times when there is a negative. I am trying to put together a departure schedule. If the scheduled departure time is 23:15 and the truck does not depart until 00:01 then the variance is 00:46. That means the truck left 46mins late. I figured out that formula. Now, if the truck leaves at 23:00 I get a calculation of 23:45, when I need it to display that the truck was early by 15 mins or -00:15.

d= the constant or the scheduled depart time (23:15)
e= the actual depart time (23:00)
f= the formula I am currently using is =E19-D19+IF(D19>E19,1)

I would really appreciate any help; maybe there is an easier way to do this?
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
This is tricky when you are using times and no dates but one way would be to use this formula

=IF((E19-D19+(D19>E19))<0.5,TEXT(1+E19-D19,"hh:mm"),"-"&TEXT(ABS(E19-D19),"hh:mm"))

or perhaps..

=TEXT(1440*(((E19-D19+(D19>=E19))<0.5)+E19-D19),"0")& " mins late"
 

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
Edit: looks like mine might turn out to be what barry has already posted!

Hi CP4840:

How about ...
Book1
DEFG
18SchedudledActualLateByMinutes
1923:150:0100:46
2023:1523:00-00:15
Sheet5


formula in cell F19 is ...

=IF((E19-D19+(E19<D19))>0.5,"-"&TEXT(ABS(E19-D19),"hh:mm"),TEXT(E19+1-D19,"hh:mm"))
 

CP4840

New Member
Joined
May 4, 2005
Messages
17
Guys,
Thank you sensei, you are both bona fide wise man. Seriously, thanks for the help with the formula, it worked perfect.
 

Watch MrExcel Video

Forum statistics

Threads
1,119,137
Messages
5,576,299
Members
412,716
Latest member
Ardin
Top