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?

On Sat, 10 Nov 2007 18:46:27 -0000, "Gareth" wrote:

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

Tools Optitions Calculation tab select 1904 date system OK out


"Gareth" wrote:

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?

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

On Sat, 10 Nov 2007 18:46:27 -0000, "Gareth" wrote:

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?

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

"Gord Dibben" wrote:

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

On Sat, 10 Nov 2007 18:46:27 -0000, "Gareth" wrote:

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?