Forum Discussion

Emilydb0310's avatar
Emilydb0310
Copper Contributor
Mar 06, 2024

Excel short date - month and year change only

Hello,

I have a spread sheet that has dates as example below:

29/03/2022

04/02/2021

11/06/2023

However I need to change the month and year only but keep the same day, so it looks like this:

29/04/2024

04/04/2024

11/04/2024

I have over 300 dates to amend and was wondering if there was a formula or shortcut to do this please?

Thanks in advance!

excel
Formulas and Functions

1 Reply

Sort By
  • Maciej_Kopczynski's avatar
    Maciej_Kopczynski
    Copper Contributor
    Mar 06, 2024

    Hi!
    There are many ways you can try, but assuming you want your year to be 2024 and month april for all your dates just try this formula:
    =DATE(2024, 4, DAY(D10))
    My formula is in E10 and references the date in D10. Adjust the referencing and drag and drop!
    Leap years won't be a problem because 2024 is fortunately also a leap year so there won't be errors for 29.02 either.

Resources