Dates and times displayed as serial numbers when viewing formulas in Excel

Article translations Article translations
Article ID: 241072 - View products that this article applies to.
This article was previously published under Q241072
Expand all | Collapse all

On This Page

SYMPTOMS

Microsoft Excel displays serial number values for dates and times in cells, but shows formatted dates and times in the formula bar.

CAUSE

This behavior occurs when you are viewing your worksheet as formulas rather than values.

RESOLUTION

To display formatted dates and times in the cells on the worksheet:
  1. On the Tools menu, click Options, or click Preferences (on the Edit menu in Microsoft Excel 98 for the Macintosh and in Microsoft Excel 2001 for Mac; on the Excel menu in Microsoft Excel X for Mac and in Microsoft Excel 2004 for Mac).
  2. On the View tab, click to clear (remove the "x" from) the Formulas check box, and then click OK.

MORE INFORMATION

The Formulas check box is used to view formulas in the cells on the worksheet, as well as in the formula bar.

How Microsoft Excel Stores Dates and Times

Microsoft Excel stores dates as sequential numbers known as serial values. Excel stores times as decimal fractions because time is considered a portion of a day. Dates and times are values and so you can add, subtract, and include dates and times in other calculations. For example, to determine the difference between two dates, you can subtract one date from the other. You can view a date as a serial value and a time as a decimal fraction by changing the format of the cell that contains the date or time to General format.

Properties

Article ID: 241072 - Last Review: September 18, 2011 - Revision: 4.0
APPLIES TO
  • Microsoft Excel 95 Standard Edition
  • Microsoft Excel 97 Standard Edition
  • Microsoft Excel 2000 Standard Edition
  • Microsoft Excel 2002 Standard Edition
  • Microsoft Excel 5.0 for Macintosh
  • Microsoft Excel 98 for Macintosh
  • Microsoft Excel 2001 for Mac
  • Microsoft Excel X for Mac
  • Microsoft Excel 2004 for Mac
  • Microsoft Excel 2008 for Mac
Keywords: 
kbdtacode kbprb KB241072

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com