Number signs (###) displayed in cells of a worksheet that contain dates in Excel for Mac (248396)


The information in this article applies to:

  • Microsoft Excel 2004 for Mac
  • Microsoft Excel X for Mac
  • Microsoft Excel 2001 for Mac
  • Microsoft Excel 98 Macintosh Edition
This article was previously published under Q248396

SYMPTOMS

When you open an existing worksheet, you see number signs (###) in cells that contain dates.

CAUSE

The behavior occurs when the following conditions are true:
  1. In Microsoft Excel X for Mac and later versions on Mac OS X, the Show century option is turned off in the International preference.

    Or, in Microsoft Excel 2001 for Mac and earlier versions on Mac OS 9, the Show century option is turned off in the Date & Time Control Panel.
  2. You type dates in a workbook including the day, month and year, using the dash (-) or slash mark (/) separators in one or more cells.
  3. You do not click Cells on the Format menu to change the number format. You leave the date formatted as the default number format.
  4. You adjust the column width just wide enough to display the date in the cell.
  5. You close the workbook.
  6. You turn on the Show century option in the International preference on Mac OS X or in the Date & Time Control Panel on Mac OS 9.
  7. You reopen the workbook.

WORKAROUND

With the cell selected, point to Column on the Format menu, and then click AutoFit Selection to widen the column to display the newly formatted dates.

Note After you widen the columns, worksheets may be printed differently. Check your print settings after the changes.

MORE INFORMATION

Microsoft Excel X for Mac and later versions on Mac OS X

Use the International preference to determine what number formats to use and to determine the correct separators to use, for example, "/" or "-". These settings affect the way that Microsoft Excel displays and uses dates and times. If you type a date in a cell, and then you do not click Cells on the Format menu to choose a number format, Excel uses the Short Date format in the International preference. When the Short Date format changes, the date format automatically changes in the cells.

To verify or change the status of date format options, including the Show century option, on Mac OS X, follow these steps:
  1. On the Apple menu, click System Preferences.
  2. Click International.
  3. On the Formats tab, click Customize under Dates. If you want to modify these settings, go to step 4.
  4. Make the changes that you want, and then click OK.

Microsoft Excel 2001 for Mac and earlier versions on Mac OS 9

Use the Date & Time Control Panel to determine what number formats to use and to determine the correct separators to use, for example, "/" or "-". These settings affect the way Excel displays and uses dates and times. If you type a date in a cell, and then you do not click Cells on the Format menu to choose a number format, Excel uses the Short Date format in the Date & Time Control Panel. When the Short Date format changes, the date format automatically changes in the cells.

To verify or change the status of date format options, including the Show century option, on Mac OS 9 and earlier versions, follow these steps:
  1. Open the Date & Time Control Panel
  2. Click Date Formats. If you want to modify these settings, go to step 3.
  3. Make the changes that you want, and then click OK.

REFERENCES

For additional information about how Microsoft Excel works with two-digit year numbers, click the following article number to view the article in the Microsoft Knowledge Base:

302768 How Microsoft Excel works with two-digit year numbers

Modification Type:MajorLast Reviewed:1/26/2005
Keywords:kbprb KB248396
©2004 Microsoft Corporation. All rights reserved.