Calculating the difference between cells

[Guest]

I'm new to functions and formulas - how do I create one to automatically
update a cell so that it shows the difference between two cells that display
times.

For example, cell A1 contains 2:30pm and A2 contains 2:45pm - I want A3 to
display the difference, regardless of whether it is a negative or postivie.
In fact, I would prefer that it made no reference to whether it's a negative
or not.

 

[Peo Sjoblom]

If you want negative time you have to change the date system to 1904 under
tools>options>calculation

Note that it will add 1462 days to any date, so if you use that you can
convert back any altered dates by putting 1462 in an empty cell, copy it,
paste special and select subtract

otherwise you have to fake it

=IF(B1>=A1,TEXT(B1-A1,"[h]:mm:ss"),TEXT(A1-B1,"-[h]:mm:ss"))

--


Regards,

Peo Sjoblom

 

[Biff]

Hi!

Try this:

=ABS(A2-A1)

Format the cell as [h]:mm

Biff

 

[Arvi Laanemets]

Hi

Another situation is, when the time interval contains midnight, or even
several days, like start time was 14:45 and end time was 14:30 in next day.
When this is the case, either:
1) Use datetime values instead of times, like A2=14.02.2005 14:45,
A3=15.02.2005 14:30
now you can calculate the difference as
=A3-A2
Depending on max interval length, format the cell with formula as "hh:mm" or
"[h]:mm" or "[d] hh:mm"
This works with time intervals of any length, but start time must always be
the earlier one.

2) With ordinary time values in source cells, like in your example,
calculate the difference as:
=A3-A2+(A3<A2)
Format the cell with formula with any valid time format.
This works, when the time intervals are always less than 24 hours. Again,
start time must always be the earlier one.