I need to determine the difference between two times. If the difference is positive, it works fine. But Excel does not want to display negative time values. Why not?

Submitted by: Administrator
Excel stores dates and times as numeric values, so it should be possible to add or subtract one from the other. The problem occurs if you have a workbook containing only times and no dates. As you discovered, subtracting one time from another doesn't always work. Negative time values appear as a series of pound signs, 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 1904 date system. Select Tools, Options. In the Options dialog box, click the Calculation tab and check the 1904 date system option to change the starting date to January 2, 1904. Your negative times will now be displayed correctly.

If you use the 1904 date system, be careful when linking to date cells in other workbooks. If the linked workbook uses the 1900 date system, the dates retrieved by the links will be incorrect.
Submitted by: Administrator

Read Online Microsoft Excel Job Interview Questions And Answers