Calculations that result in a negative time cause a problem in Excel, or at least they do with the default settings. This article explains the need for negative times and how to manage these in Excel.

Short Answer

If you already know how to work with time in Excel and just want to know how to deal with negative time then the answer is…use the 1904 date system.

Go to File, Options, Advanced and scroll down to When calculating this workbook and tick the box as shown below.

Notes:

• Be careful with existing dates in the file
• You still can’t enter negative times directly
• Calculations will happily display negative times as a result

If you need more information than that…

How Excel stores time values

First, some background information on time. Excel’s base unit of time is the day. The number 1 is one day, 2 is two days etc. Time is simply a fraction of a day. For example:

Adding Up Time

As can be seen in the table above the time format for 0.25 and 1.25 are identical. This presents the first problem, when adding times that exceed 24 hours Excel doesn’t display the result properly. It only displays the decimal part of the number (time) not the whole number part (days).

The solution is to format the cell to display the whole number in hours by putting square brackets around that unit.

1. Right click the total cell and choose Format Cells
2. On the Number tab, go to Custom
3. In the Type box enter the code: [h]:ss

This results in the correct total being displayed.

The need for negative times

Adding up time is therefore not a problem as long as the formatting is correct. However, when working flexi time the daily, weekly or monthly totals are only part of the story. There maybe occasions when being a few hours down on the week is acceptable or perhaps entering a flex day. This is where displaying negative time becomes useful.

The 1904 Date System

Excel starts counting up in days from 01/01/1900, this is the default and is the most compatible date system to use when moving raw data between systems. The downside is it can’t display negative times, Excel instead displays #######.

Excel can also use the 1904 date system, which starts counting up in days from 01/01/1904. While not as cross system compatible as the 1900 date system, that doesn’t matter if your data only lives in Excel. The advantage though is support for displaying negative times. See the beginning of this article for steps to use the 1904 date system.

Negative Time Example

An Example timesheet for someone working flexi time might be:

So on Monday this person was three minutes down. After making some time up on Tuesday, they’re 1 hour down by Wednesday. Come Friday all is well with 29 minutes to carry over to the following week!

Entering Negative Times

Its still not possible to enter negative times, however, entering a positive time and subtracting from a running total is fine. If the result happens to be negative and Excel displays a series of # symbols, then switch to the 1904 date system as described above.