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.
- =A1 + DATE(2,4,9)
- =EDATE(A1, 2*12+4) + 9
- =A1 + 2*365 + 4*30 + 9
Surprisingly, each formula gives a different result! So which one should you use?
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.
- 10-NOV-2018
- 11-DEC-2018
- 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.
- Find the last day of any month with this simple trick
- 42 tips for Excel time travelers
- How to highlight over due items in Excel
- How many Mondays between 2 given dates?
- Calculate Friday the 13ths in an year
- More tips on date & time calculations in Excel
This post is part of our Awesome August Excel Festival.
21 Responses to “Add any number of days, months or years to a date with this simple trick”
I use the following, A9 is having today' date
=DATE(YEAR(A9)+2,MONTH(A9)+4,DAY(A9)+9)
Intuitively that makes a lot more sense, and is therefore easier to remember, than that edate formula.
I use EDATE() for the same.
Yes, same here:
=DATE(YEAR(A1)+2,MONTH(A1)+4,DAY(A1)+9)
dear marcel but it is incorrect date appear after putting this formula
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.
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!
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.
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.
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))
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?
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.
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.
Will this be right in a leap year say 10 years down the road?
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!
I second Vinodh's praise for Chandoo, as well as his request for more information on why the other options didn't work.
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.
Your excel tips and trick always works for me. I used it office a lot and a good way to impress by manager. thanks
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.
Intuitively that makes a lot more sense, and is therefore easier to remember, than that edate formula. thanks for sharing..
I usually need to find the number of weeks in a given year, most are 52, but about every 4 years, there's 53.