=xldate+45where 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:
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.
This problem also occurs when you add a month to 3/31/2005, resulting in 5/1/2005 instead of 4/30/2005.
I updated the original post with a formula to solve the problem.
just use =edate(date, months)
EDATE is a function that is available in the Analysis ToolPak add-in
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
EDATE is only available in Excel 2007, or if you have a toolpack add-on.
Amazing! Thanks a lot! Who needs MS Help when you have Google!
I WANT 24/05/2005+60 MONTHS
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
what about if you try to add 8 1/2 months to any date
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
thank you sooo much...
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.
I want to calculate the retirement date from the Date of Birth, will it calculate leap year or not. Kindly clarify?
Edate Formula is Best..!
how can I add a month to a date from another worksheet?
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?
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
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??
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"
How to add 6 months to a date without showing the date
like 26/5/2013 will appear as Novmeber 2013
THANK YOU! Update was exactly what I needed.
=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.
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
Post a Comment