Adding Months to a Date in Excel

Adding days to a date in Excel is pretty straight forward. For example, the formula for adding 45 days would be
=xldate+45
where xldate is the named reference to a standard Excel date.

Adding months to a date is almost as easy. Let's say you want to add 5 months to today's date (8/11/2005). The formula is
=DATE(YEAR(xldate),MONTH(xldate)+5,DAY(xldate))
This formula works because 13/11/2005 is correctly changed to 1/11/2006. So, you don't have to worry about the problem with the year changing.

Update:
Although a bit lengthy, the following formula can be used to add n months. If the original day is greater than the number of days in the new month, then it will return the last day of the new month.
=IF(DAY(xldate) > DAY(DATE(YEAR(xldate),MONTH(xldate)+n+1,1)-1),
DATE(YEAR(xldate),MONTH(xldate)+n+1,1)-1,
DATE(YEAR(xldate),MONTH(xldate)+n,DAY(xldate)))

24 comments:

Anonymous said...

I tried the formula and it works except when the date is, for example, January 29 and the next month to be added is February (non-leap year). It would have been better if it calculates the last day of the month. But the figure displayed is March 1st. Is there a special formula for month ends that conerns February? Thanks.

Jon Wittwer said...

This problem also occurs when you add a month to 3/31/2005, resulting in 5/1/2005 instead of 4/30/2005.

Jon Wittwer said...

I updated the original post with a formula to solve the problem.

Anonymous said...

just use =edate(date, months)

Jon Wittwer said...

EDATE is a function that is available in the Analysis ToolPak add-in

Hemantha said...

Hey... thanks lot. I just use =edate(date, months). it works fine. hassel free. No need if conditions. Just go in excel menu -->tool>add ins..> analysis toolpack. The edaye funtion will display in your date time functions list. Its work like ADD_MONTHS in pl/sql.
- Hemantha@sampath Bank

Anonymous said...

EDATE is only available in Excel 2007, or if you have a toolpack add-on.

Hayley - RW said...

Amazing! Thanks a lot! Who needs MS Help when you have Google!

HAI said...

I WANT 24/05/2005+60 MONTHS

Anonymous said...

24/05/2005+60 MONTHS is easy:
say cell A1 is 5/24/2005.
enter the following in cell B1:
=EDATE(A1,60)
if you see 40322, then format the cell as date, you will see 5/24/10
Alex Lavitt

Anonymous said...

what about if you try to add 8 1/2 months to any date

Anonymous said...

Anonymous said...
I tried the formula and it works except when the date is, for example, January 29 and the next month to be added is February (non-leap year). It would have been better if it calculates the last day of the month. But the figure displayed is March 1st. Is there a special formula for month ends that conerns February? Thanks.


Just put -1 at the end of your formula to get last day of the previous month

Azizah said...

thank you sooo much...

Anonymous said...

Let's say you needed to generate a date end of month after the cell's information. Say Cell A1 = 12/01/2011, and you want Cell A2 to view as 01/31/2011.

mahesh said...

I want to calculate the retirement date from the Date of Birth, will it calculate leap year or not. Kindly clarify?

Sunil Sarode said...

Edate Formula is Best..!

Unknown said...

how can I add a month to a date from another worksheet?

Natalie said...

Does anyone know how to write a formula that adds 1 month to the initial input date, then two months to that date, then six months to that date, etc?

Anonymous said...

Spot on!!I had to send a report and i needed to know how to add 6 months to the existing date....found the solution here! and report was ready in 5 mins!! thank you! Ann

Anonymous said...

Using the Edate formula I'm trying to add 144 months to say 30/03/2002, yet the cell comes up with #VALUE!

Using the same formula and cell formatting, I do the same for 05/07/2022 and it comes up correctly with 05/05/2034.

What am I doing wrong despite using the same formula??

Anonymous said...

I need to add Cell B2 which worked out to 6months to a date formula:
=DATEDIF(B26,B27,"y")&" years "&DATEDIF(B26,B27,"ym")&" months "&DATEDIF(B26,B27,"md")& " days"

Latifa Al Shamlan said...

How to add 6 months to a date without showing the date
like 26/5/2013 will appear as Novmeber 2013

Becca said...

THANK YOU! Update was exactly what I needed.

Anonymous said...

=DATE(YEAR(C5),MONTH(C5)+5, DAY(C5)-1)

this did the trick of adding 5 months to the date. It adjusted the year even though my formula doesn't address it.