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.

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?

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.

• 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.