Entering Negative Times

Guido needs to enter negative times into cells. If he enters a positive time (2:00), Excel recognizes it just fine. If he tries to type in -2:00, Excel tells him there is an error in his formula. Guido wonders about the easiest way to enter a negative time value in a cell and have Excel recognize it as a time value.

Before providing a couple of possible answers to the question, it is good to understand the way that Excel views dates and times. Internally, dates and times are stored as serial numbers, which are nothing but actual numbers. Let's say you enter a date into cell B4, like "Mar 17, 2020" (without the quote marks. Excel understand you are entering a date and it converts it into a serial number. You can't tell it's a serial number, though, because Excel helpfully shows you the date represented by the serial number. (See Figure 1.)

Figure 1. Entering a date into Excel.

Note that the date shows in the cell as 17-Mar-20 and in the Formula bar as 3/17/2020. (Exactly how Excel displays the date depends on how you entered it and what your regional settings are within Windows.) The date shown in the cell (17-Mar-20) is the result of how the cell is formatted. When you entered the date into the cell and because the cell previously had a "General" format, Excel modified the formatting of the cell to display the date as it felt best.

Because of the date shown in the Formula bar (3/17/2020) you might think that this is the way that Excel stores the dates internally. Not so, you can see this by entering a formula into an adjacent cell (=B4) and then formatting that cell as "General." (See Figure 2.)

Figure 2. Seeing how Excel stores a date.

The Formula bar shows the formula (=B4), as it should, but the cell shows the value 43907. If you formatted the date in cell B4 as general, it would also show the same value. This is the serial number; it is the way that Excel stores dates internally.

But what does the serial number mean? It is a "count" of the number of days since a base date. Usually this base date is January 1, 1900, with that day being day 1. January 2, 1900 would be day 2, and so on. Thus, 43907 is the 43,907 day beginning with January 1, 1900.

It is possible for the base date for a workbook to be different, however. Excel, for historical purposes, understands two possible base dates. The other is referred to as the 1904 date system. It starts counting with January 1, 1904. Unlike the 1900 date system, the 1904 date system starts counting with 0. Thus, January 1, 1904, has a serial number of 0, January 2, 1904, has a serial number of 1, etc.

You can see which date system is used in a workbook by following these steps:

1. Display the Excel Options dialog box. (In Excel 2007 click the Office button and then click Excel Options. In Excel 2010 or a later version display the File tab of the ribbon and then click Options.)
2. At the left side of the dialog box click Advanced.
3. Scroll down until you see the When Calculating this Workbook section. (See Figure 3.)



Figure 3. The Advanced options in the Excel Options dialog box.

4. Note whether the Use 1904 Date System check box is selected or not.
5. Close the dialog box.

If the check box is selected in step 4, then your workbook uses the 1904 date system. If it is not selected, then you are using the 1900 date system.

What does this have to do with Guido's original question about entering negative times? Plenty, actually. You see, a time value, to Excel, is nothing more than a portion of a day. Note the examples provided so far involve dates that are stored as whole serial numbers, in this case 43907.

If you enter a date with a time into a cell, such as "3/17/20 6:00 pm" (again without the quote marks, then it is stored internally as the portion of the serial number to the right of the decimal point. (See Figure 4.)

Figure 4. Seeing how Excel stores a date and time.

The fractional portion stored with the serial number (.75) means that the time you entered (6:00 pm) is 75% through the day. Times—the decimal portion—can range from .0 (12:00:00 am) to .999988426 (11:59:59 pm).

Because of this scheme of storing dates and times internally as serial numbers, there is no such thing in the Excel world as a "negative time." There is no way to parse and store such a value, so Excel gives an error message when you try. You can do "date math" and subtract a time from a time value already stored in a cell, but all you are doing is adjusting the date and time serial number. For instance, if you have the serial number 43907.75 stored in a cell and you subtract a "time" from it that would give a negative result, all you've done is to move into the previous day. (See Figure 5.)

Figure 5. Subtracting from a date and time.

Note that in cell B6 a time, without an accompanying date, was entered (10:00 pm). This was parsed by Excel as a serial number with a "zero date" as shown in cell C6. If you try to subtract a time from that serial number that yields a negative result, Excel can't display the result as a time. (See Figure 6.)

Figure 6. You can't subtract too far with times.

If you hover the mouse pointer over the hash marks shown in cell B9, you'll see the error message: "Dates and times that are negative or too large display as ######." (The exact wording of the error message may vary from version to version of Excel.)

You'll get the same result if you try to arrive at negative times in other ways, as well. For instance, if you enter a time value into a cell and then, in a different cell, use a formula to multiply the time by -1. When you try to format the result as a time, you get the hash marks again. Excel simply cannot display negative times because they cannot exist as far as the program is concerned.

So what is Guido to do? If he actually needs to work with negative times, there is very little he can do for reasons already explained. (More on this in a moment, though.) If he is simply wanting to subtract times, that is a different story. The fact that Guido is trying to enter something like -02:00 indicates that he is working with event durations, not with actual "clock times." (Remember that Excel works with clock times to do its parsing, and -02:00 is not a clock time—you can't tell someone you'll meet them at -02:00.)

To handle event durations, you could simply let Excel do the math for you. For instance, a starting time could be placed in cell B4 and a number of hours Guido wants to subtract from it (2) could be placed in cell C4. In cell D4 he could then use a formula to calculate the result:

=B4 – (C4 / 24)

Dividing the number of hours by 24 gives the correct decimal value that is used internally by Excel in its serial numbers. Of course, if the result of the formula results in a negative serial number, then you'll get the hash marks when you try to format the result as a time. The only solution to this is to make sure that what is stored in cell B4 includes a date. You can format cell B4 to only show the time, but the inclusion of the date will stop the result being an error. You can find out more about working with negative elapsed times by taking a look at this ExcelTip:

http://excelribbon.tips.net/T006239

After all is said and done, there is one way that you can work with real negative times: Switch to the 1904 date system, as discussed earlier. For instance, look at the results of the subtractions when using the 1904 date system. (See Figure 7.)

Figure 7. The 1904 date system will handle negative times.

This approach will work great for individual workbooks on your own system but may not be so great when others use your workbook. Relying on workbooks that use differing date systems (some 1900 and others 1904) can be a cause of potential problems as you (or others) copy information from workbook to workbook. This issue was discussed in a different ExcelTip:

http://excelribbon.tips.net/T010942

When using the 1904 date system, you'll still run into hiccups when entering negative times, but the results of formulas can display negative times with no problem.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13356) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365.