Home / MS-Excel / Functions and Formula

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.

In this tutorial:

  1. Method 1: Changing Excel's Default Date System
  2. Method 2: Using the TEXT Function
  3. Method 3: Using a Custom Format