=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)))
Posts
22 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
Post a Comment