Formatting Issues

How to Prevent Excel from Automatically Adding a Year to Dates

Algirdas JasaitisAlgirdas Jasaitis Nov 10, 2026 869 views

Question details

The user needs a way to enter partial dates (like 15-Jan) into a spreadsheet without Excel automatically appending the current year to the value.

How to Prevent Excel from Automatically Adding a Year to Dates
Product
Excel
Device & OS
not provided
Scenario
Entering partial dates into monthly reconciliation files where only the day and month are necessary for the record.
Observed behavior
Excel automatically converts the partial input into a full date by adding the current year, which causes inconsistent year data across different reconciliation files over time.
Before you start

Determine whether your dates need to be used in mathematical calculations. If they are purely for display or reconciliation labels, storing them as text is the most effective approach.

Solution 1Recommended

Format Cells as Text Before Data Entry

Change the cell format to Text beforehand so Excel does not attempt to convert your input into a standard calculable date.

By default, Excel's 'General' format attempts to guess your data type. Pre-formatting the destination cells as Text overrides this behavior, forcing Excel to accept the exact characters you type without any automatic date adjustments.

1
Select the target cells

Highlight the specific cells, column, or row where you plan to enter the partial dates.

2
Access the Number Format dropdown

Go to the 'Home' tab on the ribbon and locate the 'Number' group. Click the dropdown menu that usually displays 'General'.

3
Choose the Text format

Scroll to the bottom of the dropdown list and select 'Text'.

4
Enter your dates

Type your partial date (e.g., '15-Jan'). Excel will now display exactly what you typed and will not append a year.

Format Cells as Text Before Data Entry
Consistent Data: This method is highly recommended for bulk data entry as it ensures all inputs in the formatted range remain exactly as typed.
Manage Data Formatting Easier

Take Full Control of Date Formatting with WPS Spreadsheet

WPS Spreadsheet offers powerful and intuitive formatting tools to handle exact data entry seamlessly. It is fully compatible with Microsoft Excel files, meaning you can open, edit, and save your monthly reconciliation files without losing any custom formats or text settings.

  1. 1. Open your file in WPS Spreadsheet: Launch WPS Office and open your monthly reconciliation file.
  2. 2. Highlight the data range: Select the column or cells where you intend to input your month/day dates.
  3. 3. Access the format menu: Right-click the selected area and choose 'Format Cells', or use the 'Ctrl+1' shortcut.
  4. 4. Set to Text: Under the 'Number' tab, select 'Text' from the category list and click 'OK' to prevent any automatic date conversions.
Fully compatible with Microsoft Excel (.xlsx, .xls) files and custom formats.Intuitive interface for quickly changing Number, Date, and Text cell formats.Lightweight software that runs smoothly for managing large monthly reconciliation sheets.Free and highly accessible on Windows, Mac, Linux, iOS, and Android.
microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel automatically add a year to dates?

Excel is programmed to recognize date formats to enable powerful time-based calculations. When a day and month are entered without a year, Excel automatically assumes the current year so it can store the value as a complete, calculable date serial number.

Can I remove the year from a date I have already entered?

If Excel has already converted your entry into a full date, you cannot just delete the year without deleting the entire value. You must change the cell format to Text or type an apostrophe, and then retype the partial date. Alternatively, you can use Custom Formatting to visually hide the year.

Will dates formatted as text still work in my formulas?

No, dates formatted as text using the apostrophe method or Text cell format are treated as standard alphanumeric strings. Most date-specific functions, like DATEDIF or subtracting days, will return an error when trying to calculate text values.

How do I make the year consistent in past reconciliation files?

If the data must remain as a real date for formulas, you should enter the complete date manually, including the specific historical year (e.g., 15-Jan-2022). Do not rely on Excel's automatic year appending when working with historical data, as it will always default to the current year.