Microsoft Excel Question: Download Questions PDF
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?
Answer:
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.
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.
Download MS Excel Interview Questions And Answers
PDF