For a renewal or a review date, you want the same day a few months on. EDATE moves a date forward by whole months and keeps the day of the month sensible.
The formula
=EDATE(A2,12) in column B
What each part does
EDATE
Moves a date forward by whole months and keeps the day of the month.
12
How many months to add. Change it to any number; a negative number counts backwards to an earlier date.
How it works, and what to watch for
EDATE handles the tricky cases for you. Add a month to the 31st and it lands on the last day of the shorter month rather than spilling over.
The answer often shows up as a plain number at first. Format the cell as a date and it reads properly.
To go backwards, use a negative number of months. To add years, multiply: twelve months is a year.
Questions
Why is my new date showing as a number like 46000?
That is the date underneath. Format the cell as a date and it will read normally.
How do I add years instead?
Multiply by twelve inside EDATE, so three years is EDATE(date, 36). There is no separate EYEAR.
Set by your computer's language, not by the file. A frequent cause of a correct-looking formula being rejected.
Excel version
XLOOKUP, IFS, TEXTBEFORE and TEXTJOIN need Microsoft 365 or 2021; older versions return #NAME? instead. Choosing the older one switches every walkthrough to a formula that works there.
