Displaying Negative Times

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 in versions of Excel prior to Excel 2007, follow these steps:

1. Choose Options from the Tools menu. Excel displays the Options dialog box.
2. Make sure the Calculation tab is selected. (Click here to see a related figure.)
3. The 1904 Date System check box controls which dating system is used. If the check box is not selected, then the 1900 dating system is used; if it is selected, then the 1904 system is used.

If you are using Excel 2007 then you should follow these steps:

1. Click the Office button and then click Excel Options. Excel displays the Excel Options dialog box.
2. Click Advanced at the left side of the dialog box.
3. Scroll through the options until you see the section called When Calculating This Workbook. (Click here to see a related figure.)
4. The Use 1904 Date System check box controls which dating system is used, just as in earlier version of Excel.

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.