Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel keeps converting text to date format | Excel Discussion (Misc queries) | |||
How do I calculate and show negative time in Excel? | Excel Worksheet Functions | |||
How do i get excel to format a cell for a negative result. | Excel Discussion (Misc queries) | |||
Negative Time Values | Excel Discussion (Misc queries) | |||
how to use time format to present negative value? ex. -00:42 min | Excel Discussion (Misc queries) |