Negative or positive time difference. How to

[salewit]

I'm trying to subtract the time in one cell from another. Simple stuff. But I also want a NEGATIVE time displayed in certain situations. I'm trying to calculate arrival information. For example:

Due at 17:10. Arrived at 18:20. 18:20-17:10 = 1:10 (late)

But if this happens:

Due at 17:10. Arrived at 17:00. 17:00-17:10 = ##### (can not display negative time).

Any ideas?

 

[AnotherHiggins]

Excel cannot deal with negative times - that's a known problem with it.

But you could work around it to display what you want. But understand that this will only DISPLAY the time, the value in the cell with be text and you therefore cannot do further math with it.

=IF(Arrived-Due<0,TEXT(ABS(Arrived-Due),"[h]:mm") & " (Early)",TEXT(Arrived-Due,"[h]:mm") & " (Late)")

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[PGoRule]

Suppose if your cell 'B1' contains actual arrival and 'C1' contains scheduled time then using two conditions in two different columns display the result.

Use "=IF(B1>C1,"Late","Early")" for 'D1' to display the status.
Use "=IF(B1>C1,B1-C1,C1-B1)" for 'E1' to disply the time difference. Format this cell as Time (##:##).


Sharing the best from my side...

--Prashant--

 

[GlennUK]

Excel can deal with negative times, but you have to change the date system used. Do menu command Tools/Options/Calculation and tick the "1904 Date System" checkbox.

WARNING: This may have unexpected side effects if you are dealing with dates, and exchange workbooks with other users that do not have the 1904 Date System switched on.

In fact, I'd use one of the methods already given rather than touch the 1904 Date System option, but thought you'd better know about it, and choose for yourself.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[salewit]

Thanks for all the help. I may have to try the 1904 Date method as I was planning on calculating and graphing on the data. I also didn't have any plans to exchange this with anyone.

This has been a crash course in time calculations. Another problem that just popped up .... in the above example, if my scheduled arrival is 23:30 and it arrives 2 hours late at 1:30, I've got an entirely new problem whereas some of the examples above would show this as being VERY eary. I guess I'd need to add the dates to the cell as well.

 

[Rofeu]

Hi,

although your negative time is not displayed, the value still exists. If you change the numberformat to number, you'll see the negative value. You could use a column for showing the ie '10 Minutes Early' and another one for plotting with the true value (-0.01).

Cheers,

Roel

PS Yes, you'll need to add the date as well