2024-12-29

Worksheet function – How to add/subtract months from given date in Excel

DATARECOVERYUNION.COM02 mins

Here is my scenario:

I have to populate list with dates and some metadata of upcoming events for the following year in my Excel application. Some of the events are occurring on the same day each month, e.g. the 3rd day of month.

So the question is:

Is there any function so that I can add one month to given date, like:

  • cell A1: 1/23/2013
  • cell A2: 2/23/2013
  • cell A3: 3/23/2013

What did not work:

Apparently

A3=$A2+30

produces 3/25/2013 which is wrong.

Solution:

As far as I did not found answer here on superuser I post my own answer:

According to this link Excel: Adding/Taking n Month(s) to a Date. Add Months to an Excel Date you have to:

  • Enable Analysis Toolpak
    • File -> Options -> Add-ins -> Manage: Excel Add-ins -> Go

and use the function EDATE(reference_cell, offset_integer), here’s an example:

  • A2 = 2/23/2013
  • A3 = EDATE($A1, 1) -> 3/23/2013

EDIT:

As barry houdini pointed out in comment, Analysis Toolpak is needed only for Excel 2003 and earlier, as far as EDATE function is built-in in later versions of Excel.

Related News