Display Negative Time Values

Sometimes you want to display negative time values, but Excel will only display ######. There are several ways to escape this problem.

If you enter the time 12:00:00 (midday) into any cell and then format it as General, you will see that it has a numeric value of 0.5. Similarly, enter the time 24:00:00 in any cell. Look in the Formula bar and you will see that Excel shows 1/1/1900 12:00:00 AM.

Excel sees dates and times as nothing more than numbers. In the case of a date, by default Excel considers 1 January 1900 to have a numeric value of 1; 2 January 1900 a numeric value of 2; and so forth. Times are seen as decimals, with the exception of midnight, which has a numeric value of 1. Because of this, Excel has real trouble coping with the notion of negative time.

Here are three methods to get Excel to display negative time values.