Dealing With Negative Time Values

Category: Formulas | [Item URL]

Because Excel stores dates and times as numeric values, it's possible to add or subtract one from the other.

However, if you have a workbook containing only times (no dates), you may have discovered that subtracting one time from another doesn't always work. Negative time values appear as a series of hash marks (########), even though you've assigned the [h]:mm format to the cells.

By default, Excel uses a date system that begins with January 1, 1900. A negative time value generates a date/time combination that falls before this date, which is invalid.

The solution is to use the optional 1904 date system. Select Tools, Options, click the Calculation tab, and check the 1904 date system box to change the starting date to January 2, 1904. Your negative times will now be displayed correctly, as shown below.

Be careful if you workbook contains links to other files that don't use the 1904 date system. In such a case, the mismatch of date systems could cause erroneous results.



Search for Tips


All Tips

Browse Tips by Category

Tip Books

Needs tips? Here are two books, with nothing but tips:

Contains more than 100 useful tips and tricks for Excel 2013 | Other Excel 2013 books | Amazon link: 101 Excel 2013 Tips, Tricks & Timesavers

Contains more than 200 useful tips and tricks for Excel 2007 | Other Excel 2007 books | Amazon link: John Walkenbach's Favorite Excel 2007 Tips & Tricks

© Copyright 2016, J-Walk & Associates, Inc.
Privacy Policy