Function Problems

How to Automatically Change the Month in Excel Dates

Maira Mehtab Sep 28, 2026 870 views

Question details

The user needs to dynamically update the month of existing date entries to the current month while retaining the original day and year.

Product: Excel
Device & OS: not provided
Scenario: Updating a list of dates (e.g., 4/28/2024, 5/14/2024) so that the month component reflects the current month for reporting or tracking purposes.
Observed behavior: A formula is required to output dates that maintain their original year and day, but have their month automatically updated to the current system month.

Before you start

Ensure your original data is formatted as valid dates in Excel and not as plain text, so the date functions can properly extract the year, month, and day components.

Solution 1Recommended

Use the DATE and TODAY Functions to Update the Month

Extract the year and day from your original date and combine them with the current month using the TODAY function.

This method combines the DATE, YEAR, MONTH, TODAY, and DAY functions to dynamically rebuild the date. By embedding MONTH(TODAY()) into the formula, the result will always reflect the current month your computer system is in.

Select a blank cell

Click on a blank cell adjacent to your first date. For example, select cell B2 if your original date is in A2.

Enter the formula

Type the formula =DATE(YEAR(A2),MONTH(TODAY()),DAY(A2)) into the formula bar and press Enter.

Apply to the remaining cells

Click the bottom-right corner of cell B2 and drag the fill handle down to apply this formula to the rest of the dates in your column.

Automatic Date Correction: If you update a date like January 31 to a month with only 30 days (e.g., April), Excel will automatically push the date forward to the 1st of the following month (e.g., May 1).

Efficient Spreadsheet Management

Easily Manage and Calculate Dates with WPS Spreadsheet

WPS Spreadsheet fully supports Excel's advanced date and time functions, allowing you to seamlessly update and manage your data. It's a lightweight, robust, and highly compatible tool for all your data processing needs.

1. Open your file in WPS Spreadsheet: Launch WPS Office and open your workbook containing the original dates.
2. Input the DATE formula: In an adjacent column, type =DATE(YEAR(A2),MONTH(TODAY()),DAY(A2)).
3. Drag to fill: Use the fill handle to drag down and instantly update all dates to the current month.

Fully compatible with Microsoft Excel formats (.xlsx, .xls) and formulas.Rich library of built-in date and time functions for complex calculations.Lightweight software that runs smoothly on various operating systems.Clean, intuitive interface for fast and error-free data processing.

microsoft office alternative - wps office

Frequently Asked Questions

How can I change the year to the current year instead of the month?

You can modify the formula to update the year by using =DATE(YEAR(TODAY()),MONTH(A2),DAY(A2)). This keeps the original month and day while updating the year to your current system year.

Why is my formula returning a 5-digit number instead of a date?

Excel stores dates as sequential serial numbers. If you see a number like 45413, select the cell, go to the Home tab, click the Number Format dropdown menu, and select 'Short Date'.

Can I add a specific number of months to a date rather than changing it to the current month?

Yes. To add a specific number of months to a date, use the EDATE function. For example, entering =EDATE(A2, 3) will add exactly 3 months to the date located in cell A2.

Excel & Data Problems