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)))

25 comments:

  1. Anonymous2:12 AM

    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.

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

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

    ReplyDelete
  4. Anonymous6:51 PM

    just use =edate(date, months)

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

    ReplyDelete
  6. Anonymous11:27 PM

    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

    ReplyDelete
  7. Anonymous11:00 AM

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

    ReplyDelete
  8. Hayley - RW4:27 AM

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

    ReplyDelete
  9. I WANT 24/05/2005+60 MONTHS

    ReplyDelete
  10. Anonymous9:50 AM

    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

    ReplyDelete
  11. Anonymous1:03 PM

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

    ReplyDelete
  12. Anonymous11:30 PM

    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

    ReplyDelete
  13. thank you sooo much...

    ReplyDelete
  14. Anonymous2:12 PM

    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.

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

    ReplyDelete
  16. Sunil Sarode12:01 AM

    Edate Formula is Best..!

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

    ReplyDelete
  18. Natalie3:32 AM

    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?

    ReplyDelete
  19. Anonymous12:05 PM

    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

    ReplyDelete
  20. Anonymous7:36 PM

    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??

    ReplyDelete
  21. Anonymous6:42 PM

    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"

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

    ReplyDelete
  23. THANK YOU! Update was exactly what I needed.

    ReplyDelete
  24. Anonymous8:12 AM

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

    ReplyDelete
  25. Anonymous7:01 PM

    Hire date in A1 cell which is unknown.
    need to set up the spreadsheet now to add 6 mo's in B2 cell for the review date.
    I want to have this set so when they are hired, I can just plug in the hire date and be done. Can anyone help me? Please & Thank you

    ReplyDelete