My son-in-law came to me recently with a problem he was experiencing in MS Excel. He is a director in his company and is charged with keeping track of his employees time. He wanted to be able to display a negative time in his MS Excel spreadsheet. I have to tell you – I had a time figuring out this one.
After doing some research and digging around a bit, I found that the easiest solution to this problem is to simply change the date system used by the workbook. I agree that it sounds a bit strange, but it works.
The default date system for Windows versions of MS Excel is 1900 and if you are using this system, and attempt to subtract a larger number from a smaller one, you result will she displayed as ############ in the cell.
If you switch to the 1904 date system, which is the default for Mac versions of Excel, you will see the correct elapsed time in your formula.
Follow the steps below to learn how:
- Click Office Button | Excel Options.
- MS Excel displays the Excel Options dialog box.
- On the left side of the dialog box, click Advanced.
- Scroll through the options until you see the section titled When Calculating This Workbook.
The Use 1904 System check box controls which dating system is in use, like above.