Negative time calculations

[Gareth]

I'm missing something obvious but I can't work out what.

I'm trying to calculate the difference between two time formatted cells
(hh:mm:ss) where x - y is likely to result in a negative time (for
example -07:20:00).

Excel displays the negative time as ###. If I change the formula cell to a
general format (rather than a time format) then the number is displayed but
obviously the time formatting is lost (3 and a half hours appears as 3.5).

Is there a simple way to force Excel to display negative times?

 

[Ron Rosenfeld]

I'm missing something obvious but I can't work out what.

I'm trying to calculate the difference between two time formatted cells
(hh:mm:ss) where x - y is likely to result in a negative time (for
example -07:20:00).

Excel displays the negative time as ###. If I change the formula cell to a
general format (rather than a time format) then the number is displayed but
obviously the time formatting is lost (3 and a half hours appears as 3.5).

Is there a simple way to force Excel to display negative times?

Tools/Options/Calculation
Workbook Options
Select 1904 Date system

Be advised that this will throw off all of your other dates by four years + 1
day.
--ron

 

[Guest]

Tools > Optitions > Calculation tab > select 1904 date system > OK out

 

[Gord Dibben]

Simple but possibly disruptive if this workbook is linked to others.

Use the 1904 date system on that workbook.

See John's Walkenbach's site for more.

http://www.j-walk.com/ss/excel/usertips/tip051.htm


Gord Dibben MS Excel MVP

 

[Guest]

Hello Gareth,

Can you explain a little more what you're doing, are these times of day or
time durations, why do you need to get negative amounts?

If you just want to display like a negative you could use the following
formula

=IF(A1<B1,"-","")&TEXT(ABS(A1-B1),"hh:mm:ss")

This returns a text string so it's not recommended if you need to do further
calculations with the result