Get Excel to accept negative values using time-format

[Guest]

Hello

Excel seems to refuse negative values in cells that are having i time-format.
Is there a way to solve this?

(Yes, time can be negative. If you for example compare planned time with
actual time, the difference can be positive or negative)

Kind regards Marcus

 

[Roger Govier]

Hi Marcus

Excel will not display negative time using the standard 1900 date system. It
displays a series of #########'s. However, the result can be used in further
calculations.

If you switch to the 1904 date system, Tools>Options>Calculation>click 1904
dates. Beware of other changes to dates already entered as they will alter
by 4 years and 1 day.

A way of getting the time to work under the 1900 date system is to use
=MOD(A1-B1,1)
but the resulting value will always be positive. You could use Conditional
Formatting to show the negative values as Red.
Format>Conditional Formatting>Formula is>=B1<A1 and set text format to Red.

Regards

Roger Govier