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.

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.