Displaying Negative Times in MS Excel

Dennis Faas's picture

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:

  • From the Tools menu, select Options.  
  • Ms Excel will display the Options dialog box. Be sure that the Calculation tab is selected.  
  • The 1904 Date System check box controls which dating system is used. If the check box has not been selected, the 1900 dating system is in use. If it is selected, the 1904 system is in use.

Follow the steps below to do the same in MS Excel 2007:

  • 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.

When you become a member at CarolsCornerOffice.com, you have access to this and many, many more articles that include screenshots. Don't delay: visit us today!

Rate this article: 
No votes yet