Add any number of days, months or years to a date with this simple trick
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.
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.
My name is Chandoo. Thanks for dropping by. My mission is to make you awesome in Excel & your work. I live in Wellington, New Zealand. When I am not F9ing my formulas, I cycle, cook or play lego with my kids. Know more about me.
Thank you and see you around.
Leave a Reply
|« 5 Shortcuts for you, one for every weekday [Awesome August]||Correlation vs. Causation [Charting Chatter] »|