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

Finding the Date Associated with a Negative Value

When working with data taken from the real world, you often have to determine which certain conditions were met, such as when ...

Discover More

Only Showing Readability Statistics

Perform a grammar check, and Word displays some statistics that represent an analysis of your words. By writing a macro you ...

Discover More

Reversed Bolding

If you paste information from one document into another, you may be surprised at the results. If your text changes from ...

Discover More

Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!

More ExcelTips (ribbon)

Calculating Elapsed Time with Excluded Periods

When using Excel to calculate elapsed time, there can be all sorts of criteria that affect the formulas you would otherwise ...

Discover More

Entering Large Time Values

If you need to input humongous times into a worksheet, you may run into a problem if you need to enter times greater than ...

Discover More

Checking for Data Entry Errors for Times

When you enter a time value into Excel, the program tries its hardest to make the value into a valid time. This can lead to ...

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. Maximum image size is 6Mpixels. 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 four less than 9?

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.