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?
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,621
Messages
5,832,736
Members
430,160
Latest member
a_majda

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
Top