Function Problems

How to Change the Year in Excel Dates While Keeping Month and Day

Adam Davis Oct 22, 2026 868 views

Question details

The user needs to change the year of existing dates in a CSV file to a specific year (such as 2022 or 2023) without altering the month and day.

How to Change the Year in Excel Dates While Keeping Month and Day

Product: Excel
Device & OS: not provided
Scenario: Standardizing dates from a CSV file for accurate anniversary calculations and applying conditional formatting based on updated yearly dates.
Observed behavior: Dates are currently in dd/mm/yyyy format with varying or incorrect years, requiring conversion to a uniform target year or the current year.

Before you start

Ensure your date column is formatted as standard Excel dates, not as plain text, so the date formulas can correctly extract the month and day values.

Solution 1Recommended

Use the DATE Function to Change Dates to a Specific Year

Extract the month and day from your original date and combine them with a new fixed year using the DATE function.

The most reliable way to change the year of a date while retaining the specific month and day is to break the date apart and reconstruct it. The DATE function allows you to specify a new year while pulling the exact month and day from the original cell.

Select an empty cell

Click on a blank cell in a new column next to your first date (for example, select cell E2 if your original date is in D2).

Enter the DATE formula

Type the formula =DATE(2023,MONTH(D2),DAY(D2)) into the formula bar and press Enter. Replace 2023 with your desired year.

Apply to the entire column

Click the bottom-right corner of the cell containing the new formula and drag the fill handle down to apply the calculation to the rest of your data.

Use the DATE Function to Change Dates to a Specific Year

Formatting Tip: Once calculated, you can copy the new formula column and use 'Paste as Values' over your original data to permanently replace the old dates.

Solution 2

Calculate the Most Recent Anniversary Using TODAY and IF Functions

Dynamically update the year to the current or previous year depending on whether the anniversary has already passed.

Solution 3

Use the LET Function for Simplified Dynamic Date Calculations

Use the modern LET function to shorten complex date formulas when calculating dynamic anniversaries.

Advanced Spreadsheet Features

Easily Manage and Calculate Excel Dates with WPS Office

WPS Spreadsheet provides full support for advanced date functions like DATE, MONTH, DAY, and LET, allowing you to seamlessly process CSV files and format anniversaries without compatibility issues.

1. Open your file in WPS Spreadsheet: Launch WPS Office and open your CSV or Excel file containing the dates.
2. Select a blank cell: Click on a blank cell next to your existing anniversary date column.
3. Apply the DATE formula: Input the formula =DATE(2023,MONTH(D2),DAY(D2)) and press Enter to generate the new date.
4. Fill the column: Drag the cell's fill handle downwards to quickly update the year for all dates in your list.

100% compatible with Microsoft Excel date formulasLightweight application with incredibly fast CSV processingFree advanced conditional formatting tools for date rangesCross-platform support for Windows, Mac, and mobile devices

microsoft office alternative - wps office

Frequently Asked Questions

Why is my DATE formula returning a #VALUE! error?

This usually happens if your original dates are stored as text rather than actual Excel serial dates. You can fix this by selecting your original date column, going to the Data tab, and using the 'Text to Columns' feature to convert the text back to a recognized date format before applying the formula.

Can I use the Find and Replace tool to change the year instead of a formula?

While Find and Replace can work for simple text replacements, it is highly prone to errors with dates (for example, accidentally replacing a '20' in the day or month instead of just the year). Using the DATE function is much safer and ensures the date remains mathematically valid.

How do I format the newly calculated dates back to dd/mm/yyyy?

Select the cells containing your new formula, right-click, and choose 'Format Cells'. Navigate to the 'Number' tab, select 'Custom', type 'dd/mm/yyyy' in the Type input box, and click OK.

Does WPS Office support the LET function for date calculations?

Yes, the latest versions of WPS Office fully support the LET function. This allows you to write cleaner, more efficient formulas for dynamic date and anniversary calculations, fully matching Microsoft 365 capabilities.

Excel & Data Problems