Formatting Issues

How to Enter Birth Dates as Numeric YYMMDD Values in Excel

Maira Mehtab Sep 27, 2026 869 views

Question details

The user needs to enter a birth date into a spreadsheet using a strict six-digit numeric format (YYMMDD), such as for an unemployment claim form.

Product: Excel
Device & OS: not provided
Scenario: Entering a strict six-digit numeric date format required for specific official claims or database entries.
Observed behavior: Excel attempts to automatically format the six-digit input as a standard date, which results in the software either rejecting the input or displaying it as a nonnumeric, incorrectly calculated date.

Before you start

Highlight the specific column or group of cells where you intend to input the birth dates before making any changes. Verify that these cells are empty, as modifying the format of existing standard dates to General or Text can display their underlying serial numbers instead.

Solution 1Recommended

Format Cells as General or Text to Enter Values Directly

By changing the cell format from Date to General or Text, you can bypass Excel's automatic date formatting and input the exact six-digit number.

Excel has built-in features that automatically convert recognizable numbers into standard dates (like MM/DD/YYYY). To force Excel to accept a strict six-digit numeric value like YYMMDD, you must remove the date formatting from the target cells entirely.

Select the target cells

Click and drag to select the cells, or click the column letter to select the entire column where you will enter the YYMMDD values.

Open the Format Cells dialog

Right-click the highlighted area and choose 'Format Cells' from the context menu, or press the 'Ctrl + 1' keyboard shortcut.

Change the category to General

Under the 'Number' tab, look at the Category list on the left. Select 'General' (or 'Text') and click 'OK'.

Enter the numeric value

Type your six-digit value directly into the cell. For example, for August 23, 1992, type 920823 and press Enter. The value will now stay exactly as typed.

Handling Leading Zeros: If the birth year starts with a zero (e.g., the year 2005 represented as 05), formatting the cell as 'General' will cause Excel to drop the leading zero. In this case, select 'Text' instead of 'General' in step 3 to preserve the exact characters entered.

Efficient Spreadsheet Management

Format and Enter Exact Date Values in WPS Spreadsheet

WPS Spreadsheet provides a highly compatible and user-friendly interface to manage custom cell formatting. It makes it incredibly easy to input exact numeric formats like YYMMDD without struggling with unwanted automatic date conversions.

1. Open your file in WPS Spreadsheet: Launch WPS Office, open your spreadsheet document, and highlight the cells you want to format.
2. Navigate to Number Formatting: Go to the 'Home' tab on the top ribbon and locate the Number Format dropdown menu.
3. Set format to Text: Select 'Text' from the dropdown list to ensure exact character retention, including any leading zeros.
4. Input the exact date: Type your YYMMDD value (e.g., 920823) and press Enter. The entry will remain exactly as typed.

Seamlessly compatible with Microsoft Excel (.xlsx) file formats.Easily switch cell formats to Text or General to prevent auto-formatting.Lightweight, fast, and free to download for daily productivity tasks.Intuitive interface that feels familiar to all spreadsheet users.

microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel change my YYMMDD input into a different date format automatically?

Excel's default behavior is designed to recognize and convert date patterns into its standard date format. If a cell is set to 'Date' or even 'General' in some contexts, Excel interprets your six-digit number based on its own internal calendar serial numbers, leading to unexpected formatting or calculation errors.

How do I keep a leading zero when typing a YYMMDD date for years in the 2000s?

If the birth year is in the 2000s (e.g., 05 for 2005), formatting the cell as 'General' will automatically drop the leading zero because it treats it as a standard number. To fix this, change the cell format to 'Text' before typing the number, or type an apostrophe (') right before the number (e.g., '050823).

Can I use a custom number format to display normal dates as YYMMDD?

Yes. If you prefer to enter standard dates (like 8/23/1992) but want them displayed on the sheet as YYMMDD, select the cells, press Ctrl+1 to open Format Cells, choose 'Custom' under the Number tab, and type 'yymmdd' in the Type box.

Document & File Issues