Entering Negative Times

by Allen Wyatt
(last updated January 17, 2015)

1

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 "Jan 17, 2015" (without the quote marks. Excel understand you are entereing 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-Jan-15 and in the Formula bar as 1/17/2015. (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-Jan-15) 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 (1/17/2015) 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 42021. 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, 42021 is the 42,021 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 and Excel 2013 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.)
  4. Figure 3. The advanced options in the Excel Options dialog box.

  5. Note whether the Use 1904 Date System check box is selected or not.
  6. 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, actvually. 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 42021.

If you enter a date with a time into a cell, such as "1/17/15 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 42021.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 entering 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, and 2013.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Putting Character Codes to Work

If you know the character codes for some characters of interest, you can use those codes to do lots of tasks. This tip ...

Discover More

Detecting Hidden Rows

Excel allows you to easily hide rows in a worksheet, so their contents are not visible. Figuring out how to detect where ...

Discover More

Using the SYMBOL Field

The most common way of adding symbols to a document is to use the Symbol dialog box. There is another way, however, that can ...

Discover More

Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!

More ExcelTips (ribbon)

Dealing with Small Time Values

It is no secret that you can store time values in an Excel worksheet. But do you really know how small of a time value you ...

Discover More

Taking the Time into Account in a Formula

Need to check the current time in a formula you are putting together? It can sometimes be tricky to remember what Excel ...

Discover More

Converting Numeric Values to Times

If you have a bunch of times entered into cells without the colon between the hours and minutes, chances are good that Excel ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 7 + 5?

2015-01-17 23:06:07

Col Delane

Perhaps Microsoft could solve this long standing problem by creating a standard time cell format called "duration", that is separate from the "clock time" format.


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.