Time Span calculations

[Jafferi]

Cell A1 ~ 09:00 (Plan arrival time)
Cell A2 ~ 09:15 (Actual Arrival Time)
Cell A3 ~ 0:15 (Late by 15Min)

The above tells the timing for arriving to work and schedule to arrive to
work. The calculation would be showing as such;
ABS(A2-A1)=A3

My problem is, how do we get a negative. for example if
Cell A1 ~ 09:00
Cell A2 ~ 08:55
This will tells that the arriving time is earlier by 5Min which should give
-5min. how do I get a formula for such?

Thanks in advance!

 

[Jacob Skaria]

Jafferi

Excel cannot display negative times. To display them in text format, you can
use the below formula; but cannot be used for any calculations..

=IF(A2>A1,"+" &TEXT(A2-A1,"hh:mm:ss"),"-"&TEXT(ABS(A2-A1),"hh:mm:ss"))

If this post helps click Yes

 

[Sheeloo]

You can test for - which one is the earlier time and have your calcualtions
accordingly... something like

=IF(A1<A2,A2-A1,-(A1-A2))

 

[T. Valko]

By default, Excel for Windows doesn't display negative times/dates. If you
tried it then you noticed the result of the formula was a bunch of ####
symbols.

You can change a setting so that it will display negative times/dates *but*
changing this setting will cause any current dates you have entered in your
file to be off by 1461 days. For example, if A1 contained the date 1/1/2009
then you changed the date setting the date in A1 will now be 1/2/2013. For
this reason I would reccomend not changing this setting. Unless you really
know what you're doing, changing this setting can cause a lot of unforeseen
problems.

Having said all that...

You can display the negative time in a TEXT format. That means it won't be a
true numeric time value if you need to do further calculations on the result
of the formula.

A1 = 9:00 AM
A2 = 8:55 AM

=TEXT(ABS(A2-A1),IF(A2-A1<0,"-","")&"h:mm")

Returns: -0:05 as a TEXT string

 

[Shane Devenshire]

Hi,

If you only want minutes displayed then

=1440*(A2-A1)

and format the cell to the custom format

General "Min"

Format, Cells, Number tab, Custom.