Get Excel to accept negative values using time-format
 

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

Get Excel to accept negative values using time-format
 

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, ToolsOptionsCalculationclick 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.
FormatConditional FormattingFormula is=B1<A1 and set text format to Red.

Regards

Roger Govier


Marcus Ahlbäck wrote:
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