=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
15 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?
Post a Comment