# Entering Negative Times

by Allen Wyatt
(last updated October 22, 2020)

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:

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

##### MORE FROM ALLEN

Aligning Plus/Minus Symbols

Scientific writing often involves the use of special symbols, such as the plus/minus symbol. If you want to align these ...

Discover More

Noticeable Delays when Switching Documents

Do you notice a certain "slowness" that occurs when you switch between document windows in Word? There could be any ...

Discover More

Counting Values in Table Cells

In Excel it is easy to count how many times a certain character occurs in a column of cells. In Word, it is a bit ...

Discover More

Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!

##### More ExcelTips (ribbon)

Counting Times within a Range

Excel allows you to easily store dates and times in your worksheets. If you have a range of cells that contain times and ...

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 Time Input

Need to know if a cell contains a time value? Excel doesn't contain an intrinsic worksheet function to answer the ...

Discover More
##### Subscribe

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

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 3 + 6?

2018-07-19 10:48:46

J. Woolley

When I want to display both normal time and negative time increment (i.e., time delay), I use a Custom Number format like the following: hh:mm AM/PM;[Red]?0.??

2018-07-19 09:33:21

MIchael Armstrong

The problem is only in the display of "negative times". If you're dealing with durations relative to a base time, just 1) do your net-duration computations using normal math, taking into account the units of your durations; 2) if you need to display the result as a date/time, convert the result from your duration units to days, and add or subtract to whatever your base date is, keeping in mind you can't go before zero, or 1900; 3) if the result is negative and a time only, manually convert to a "time" format via concatenation with colons or decimal points depending on the desired representation. Displaying an arbitrary time before 1900 in date format is certainly possible, but cumbersome, especially if you take the history of calendars into account. There is some small comfort in the fact that the farther back you go, there is generally less need for precision.

2018-07-18 05:53:26

Altti Helläkoski

There should be a separate type for DATE and TIME.
You can use DATE whenever you are referring dates or time in a day, always positive. TIME type field should be used just for number of years, days, hours, minutes and seconds. This particular type can be negative or positive.

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.