Formatting Issues

How to Fix Excel Custom Date Format Displaying the Wrong Year

John Wilson Sep 27, 2026 870 views

Question details

The user wants to format plain numbers (like 630) to look like dates but the application returns the wrong year or an incorrect date.

How to Fix Excel Custom Date Format Displaying the Wrong Year

Product: Excel
Device & OS: not provided
Scenario: Entering shortcut numbers to represent dates and relying on custom formats to display them with slashes and the correct year.
Observed behavior: Excel treats entries like 630 or 63 as standard numerical values rather than date serial numbers, leading to incorrect year or date displays when standard date formats are applied.

Before you start

Keep in mind that spreadsheet programs inherently store true dates as sequential serial numbers. If you type a 3-digit number like 630, it is treated as the 630th day after January 1, 1900, unless you use a very specific text-based custom number format.

Solution 1Recommended

Use Specific Custom Number Formatting Codes

Apply custom number formatting with literal slash characters and a hardcoded year to make plain numbers look like standard dates.

Because typing 630 stores a standard number rather than a date serial number, applying a normal Date format will yield an incorrect year (usually 1901). To bypass this, you can force the application to display the number with slashes and a specific year appended to it as text.

Select the target cells

Highlight the cells containing your plain numbers (e.g., 630, 603).

Open Format Cells

Right-click the selected cells and choose "Format Cells" from the context menu, or simply press Ctrl+1 on your keyboard.

Navigate to Custom formats

Go to the "Number" tab in the dialog box and select "Custom" from the Category list on the left.

Enter the formatting code

In the "Type" box, enter ##"/"##"/2024" to display 6/30/2024 from the number 630. If you prefer leading zeros (like 06/30/2024), enter 00"/"00"/2024" instead.

Apply the format

Click "OK" to apply the new custom formatting to your selected cells.

Use Specific Custom Number Formatting Codes

Entering Single-Digit Days or Months: For dates like June 3, make sure to enter 603 instead of 63. This ensures the formatting code aligns correctly with the digits.

Easily Format Data

Fix Date Formatting Issues Faster with WPS Office

WPS Spreadsheet offers highly compatible and identical formatting features. You can seamlessly apply the same custom number formats to plain data entries without worrying about compatibility issues.

1. Open your file: Launch WPS Office and open your spreadsheet document.
2. Select the data: Highlight the cells containing the numeric entries you wish to format.
3. Access Format Cells: Press Ctrl+1 to quickly open the Format Cells dialog box.
4. Apply custom format: Select the Custom category and type ##"/"##"/2024" into the Type field.
5. Confirm changes: Click OK to instantly format your numbers to look exactly like standard dates.

100% compatible with Microsoft Excel custom number formatsFree and lightweight alternative for spreadsheet managementUser-friendly interface for quick data formatting and analysis

microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel show 1901 when I type a 3-digit number and format it as a date?

Spreadsheets calculate dates based on the number of days since January 1, 1900. A plain number like 630 is treated as the 630th day, which mathematically falls in the year 1901.

Can I use these custom formatted cells in actual date calculations?

No. Because the underlying value remains a standard number (e.g., 630) and not a true Excel date serial number, date-specific formulas like DATEDIF or EOMONTH will not work correctly.

How do I convert these formatted numbers into real dates?

To convert them into true date serial numbers, you can use the Data tab's "Text to Columns" feature and set the column data format to Date. Alternatively, you can use a combination of the DATE, LEFT, and RIGHT functions in a new column.

Document & File Issues