Written by Allen Wyatt (last updated August 28, 2021)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and 2021
Mike has a worksheet that contains some times. If he subtracts 6:33 from 6:21 he noticed that he doesn't get a negative elapsed time, as in -:12. Instead he gets ############# in the cell. Mike is wondering how he can display the negative time difference properly?
The easiest way to solve this problem is to just change the date system used by the workbook. This may sound goofy, but if you use a 1900 date system (which is the default for Windows versions of Excel), then you get the ############# in the cell. If, instead, you change to the 1904 date system (which is the default for Mac versions of Excel) then you'll see the correct negative elapsed time in your formula.
To change the date system, follow these steps:
Figure 1. The Advanced options of the Excel Options dialog box.
If you prefer to not change the dating system used in the workbook (perhaps it may mess up some other date formulas you have in the worksheet), then the only thing you can do is to create a text-based version of the time differential using a formula such as the following:
=IF(B2-A2<0, "-" & TEXT(ABS(B2-A2),"hh:mm"), B2-A2)
If the difference between the two time values (in A2 and B2) is negative, then the formula concatenates a text value consisting of the minus sign and the absolute value of the difference between the times.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (6239) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and 2021. You can find a version of this tip for the older menu interface of Excel here: Displaying Negative Times.
Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!
Need to protect a series of Social Security Numbers in a worksheet? The techniques provided in this tip might be a good ...
Discover MoreWhen adjusting column width, Excel can add an extra line to some cells. This behavior seems to be related to the text ...
Discover MoreIt is often desired to change what is displayed in a cell based on what is in a different cell. What if what you want to ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2021-09-16 14:52:30
Dan Simhony
Dear Mr Wyatt, not only 1904 date system messed up existing registered dates, it also prevented me for assigning date values to worksheet cells. Took me an entire frustrating evening to find out why my old test procedures stopped working. Luckily I tried it on a single test workbook. Users should do well to stick with the beaten path.
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.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2025 Sharon Parq Associates, Inc.
Comments