# How to display -/+ variences with time?

#### CP4840

##### New Member
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
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
Edit: looks like mine might turn out to be what barry has already posted!

Hi CP4840:

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
Guys,
Thank you sensei, you are both bona fide wise man. Seriously, thanks for the help with the formula, it worked perfect.

Replies
3
Views
166
Replies
6
Views
288
Replies
4
Views
123
Replies
0
Views
186
Replies
5
Views
192

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.

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.

### Which adblocker are you using?

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

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