Add any number of days, months or years to a date with this simple trick

Posted on August 2nd, 2016 in Excel Howtos , Learn Excel - 19 comments

Let’s say you have a date in A1 and want to find out future date after 2 years, 4 months and 9 days.

Here are a few formulas you can try.

  1. =A1 + DATE(2,4,9)
  2. =EDATE(A1, 2*12+4) + 9
  3. =A1 + 2*365 + 4*30 + 9

Surprisingly, each formula gives a different result! So which one should you use?

date-add-mystery

Let’s test them with a sample date to see the results.

Assuming A1 has today’s date, ie 2nd of August, 2016, we get below results respectively.

  1. 10-NOV-2018
  2. 11-DEC-2018
  3. 9-DEC-2018

But which one is the correct answer?

We can use manual calculation to find the correct answer.

Today is 2nd of August 2016, so:

  • Adding 2 years to it, we get 2nd of August 2018
  • Adding 4 months, we get 2nd of December 2018
  • Adding 9 days, we get 11th of December 2018

That means, the correct formula would be =EDATE(A1, 2*12 + 4) + 9

Which one would you use?

My preference is to use EDATE() when doing any date arithmetic that involves months or years. For adding either days, I use simple date + number method. For adding workdays, I use either WORKDAY() or WORKDAY.INTL() formulas.

What about you? What formula would you use to add any number of years, months and days to a give date? Please share your formulas in the comment section.

More dating advice for you

If you and Excel are always on a bad date, you could use some advice. Check out below tutorials to have an amazing dating scene.

This post is part of our Awesome August Excel Festival.

Written by Chandoo
Tags: , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

19 Responses to “Add any number of days, months or years to a date with this simple trick”

  1. I use the following, A9 is having today' date
    =DATE(YEAR(A9)+2,MONTH(A9)+4,DAY(A9)+9)

  2. Marcel says:

    Yes, same here:
    =DATE(YEAR(A1)+2,MONTH(A1)+4,DAY(A1)+9)

  3. NARAYAN says:

    Hi Purna ,

    I think the mistake is in assuming that DATE(2,4,9) equates to a date after 2 years , 4 months and 9 days.

    DATE(2,4,9) is in reality April 9 , 1902 , which means it is 2 years , 3 months and 9 days from January 1 , 1900 , disregarding whether we should add 1 or not.

  4. Alawin says:

    Hi Chandoo,

    Until now, I would use this formula :
    =DATE(YEAR(A1)+2;MONTH(A1)+4;DAY(A1+9)) which yields the same result.

    Enjoy New Zealand!

  5. Matt Schneider says:

    Probably best to use the number of days that you are wanting to add. Both the EDATE and DATE functions will not account for a start date of Feb 29, 2016. The formulas return the value of 7/8/18 when it should be 7/9/16.

    • Jeff S says:

      A good point, the number of days should be understood the same way by everyone. The effect you describe can occur with many combinations where the start and target months have different numbers of days and where the start date plus the number days is either very close to the end of the month or crosses into the next month. How to approach it depends on the desired results.
      I would also use DATE as above, but I like the EDATE, too.

    • Jeff S says:

      Btw, I think you will get the result you're expecting like this, which adds the # of days first, then adds the years and months:
      =DATE(YEAR(A1+9)+2,MONTH(A1+9)+4,DAY(A1+9))

  6. Ryan C says:

    Chandoo,
    I usually need to find the number of weeks in a given year, most are 52, but about every 4 years, there's 53. I normally put a date in cell A1 and drag down for 5 years (that's my out year target) and count the weeks manually. Do you know of a simple formula to do this?

    • NARAYAN says:

      Hi ,

      Does the WEEKNUM function output match what you get when you count manually ?

      Using the WEEKNUM function I get 53 for every year , except every 28 years , when it goes to 54.

  7. Alan says:

    I'm not a fan - the question doesn't stack up, really, because I don't think it's a well-defined question. The answer depends on whether you add the days before or after the month, and I'm not aware of any standard approach.

    Consider 30 January 2015. What day is one month and nine days after that? Your formula gives 9 March 2015 (which is what I would agree with, personally).

    However, if you add the days first, you would get 8 March 2015.

    Can you say that either is correct? I don't think so, as I don't think that the question is well defined. In practice, I would have to add a lot of warnings to a model I produced using this formula.

  8. skip says:

    Will this be right in a leap year say 10 years down the road?

  9. Vinodh says:

    Hi Chandoo, in general I really like your website and informative articles, because I already learned a LOT in the few months since I discovered it. However, I have to say I'm a bit disappointed by this post. I was actually hoping you would explain the difference between the three formulas and WHY they give the results that they do.
    Maybe an idea for a follow-up to this article?

    Keep up the good work!

    • Erin says:

      I second Vinodh's praise for Chandoo, as well as his request for more information on why the other options didn't work.

  10. Peter says:

    I would go with the majority opinion and use DATE(), combined with YEAR() etc. At least DATE() works correctly in an array formula whereas EDATE() and EOMONTH() are somewhat flakey.

    I think the difficulty is that incrementing by months is not a well-defined process. One might like to believe that a calculated end date defined by

    = DATE(YEAR(start.date)+2, MONTH(start.date)+4, DAY(start.date)+9)

    would return the start date if placed in the formula

    = DATE(YEAR(end.date)-2, MONTH(end.date)-4, DAY(end.date)-9)

    but, at least for February, it doesn't.

  11. Hitesh says:

    Your excel tips and trick always works for me. I used it office a lot and a good way to impress by manager. thanks

  12. Piet says:

    Hello,

    Method number 3 is wrong in respect to leap-years. So the correct formula would be: =A1+2*365.25+4*30.4375+9. The answer is 11-DEC-2018.

Leave a Reply