negative time?

[Dusan Malenka]

Hello pople.

I need to make some operations with time, but when result is
negative in cell value "########" appears . Is it possible to set cell
format
to display negative time (for example: "-10:25").
Thank you
Dusan.

[Tom Ogilvy]

You can change to the 1904 date system in the calculation tab of the
tools=>Options. It will work with negative time. This will screw up
existing dates however. You know what the 1900 date system does with
negative time. The alternative would be put a text value in the cell (the
time with a negative sign in front) and have your code properly interpret
the time value.

Regards,
Tom Ogilvy
MVP Excel

Dusan Malenka wrote in message <387DC372...@alcatel.de>...

[Bill Manville]

In article <387DC372...@alcatel.de>, Dusan Malenka wrote:
> I need to make some operations with time, but when result is
> negative in cell value "########" appears . Is it possible to set cell
> format
> to display negative time (for example: "-10:25").
>

Excel does not normally recognise the concept of negative time.

If you set Tools / Options / Calculation / 1904 date format
you can get negative time. However, as others have pointed out in
another thread, there are problems if you also have dates in the workbook
that you reference from or copy to a workbook using the usual 1900
format.

The alternative is to display the time as a text string (noting that you
will be unable to do arithmetic on it). If you current formula is
=A1-B1 change it to
=IF(A1>=B1),"-","")&TEXT(IF(A1>=B1,A1-B1,B1-A1),"[h]:mm")

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - reply to newsgroup

[Patrick Molloy]

I'd suggest something like
=IF(Time1<Time2,Time2-Time1,"-" & -24*(Time2-Time1),Time2-Time1)
format the cell to [h]:mm

--
Patrick
Microsoft Excel MVP
________________

Dusan Malenka <d.ma...@alcatel.de> wrote in message
news:387DC372...@alcatel.de...
> Hello pople.

>
> I need to make some operations with time, but when result is
> negative in cell value "########" appears . Is it possible to set cell
> format
> to display negative time (for example: "-10:25").

> Thank you
> Dusan.
>
>
>
>