Entering Negative Times

Written by Allen Wyatt (last updated December 5, 2020)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365

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.)
  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, 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:


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:


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.

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. ...


Extracting Foreign-Language Characters

If you need to analyze some text to determine if it contains accented characters, there are a couple of ways you can ...

Discover More

Copying Formats to a New Worksheet

Do you want to copy formats from one worksheet to another? You can do so easily by using the Format Painter. It even ...

Discover More

Repaginating in a Macro

When creating a macro that extensively processes a document, you may need to periodically force Word to repaginate the ...

Discover More

Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!

More ExcelTips (ribbon)

Adjusting Times for Time Zones

Collect a series of times in a worksheet, and you might need to adjust those times for various time zones. This involves ...

Discover More

Combining and Formatting Times

Excel allows you to store times in your worksheets. If you have your times stored in one column and an AM/PM indicator in ...

Discover More

Converting UTC Times to Local Times

Dates and times are often standardized on UTC time, which is analogous to GMT times. How to convert such times to your ...

Discover More

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

View most recent newsletter.


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}] (all 7 characters, in the sequence shown) 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 3 + 7?

There are currently no comments for this tip. (Be the first to leave your comment—just use the simple form above!)

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

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.