Can anyone help on this?
Formulas: Add a number like 46 to a date so that it increase
-
-
-
=EOMONTH(A1,E1)
A1 has the start date, E1 has the number of months. Try it, it seems to work.
It adds the number of months in E1 to the start date and returns the last day of the month, so 16/07/03 in A1 add 12 in E1 will return 31/07/04 -
EOMONTH gives you the last day of the month, regardless of the day in the initial date.
Try =EDATE(A1,46). This will add 46 months to the date in A1, keeping the same day. Or use a cell reference (=EDATE(A1,E1)) for the number of months as Roy has shown in his example.
-
Will be trying it out today at work thank you. EDATE looks good
-
Just a note. You will need the Analysis ToolPak Add-in installed for EDATE or EOMONTH to work.
Richard
-
-
If it is not installed, any other ways to get this thing to work ? Any other forumulaes?
-
As you probably know, Excel calculates using the number of days that the date is from the year 1900 (or 1904 if specified).
I would suggest simply adding the total number of days in the number of months you want to add (eg. 46months x 30.35 = 1396days) to the date. So you could enter 16/07/03 in one cell as the starting date and have another cell which has the number of days, eg. 1396, and add the two to give the new date. Its not perfect so you may need to play slightly with the average number of days in a month.Regards,
Derick
-
Assuming that you want the same day of the month 46 months (which is 3 years plus 10 months) from the start date, use the following (I'll try to match the parens correctly -- if I miss I think you will get the idea). Assume the starting date is in cell A2:
=IF(MONTH(A2) > 2,DATE(YEAR(A2)+4,MONTH(A2)+10,DAY(A2)),DATE(YEAR(A2)+3,MONTH(A2)+10,DAY(A2))
Hope this helps.
-
Derick pointed out that your 46 months was probably just an example, rather than a permanent number of months to add. He also pointed out a missing last parens.(Thanks Derick) I think the solution can be generalized to any number of months as follows (assume the number of months to add is in cell B2).
=IF(MONTH(A2) > (12-MOD(B2,12),DATE(YEAR(A2)+ROUNDUP(B2/12,0),MONTH(A2)+MOD(B2,12),DAY(A2)),DATE(YEAR(A2)+ROUNDDOWN(B2/12,0),MONTH(A2)+MOD(B2,12),DAY(A2)))
Hope this works. (I mentioned in a U2U to Derick that I am trapped this week -- visiting my folks -- without a working version of Excel, so I can't test anything I post here. I can't even download posted files to look at them.
:mad: What a week!) -
The following shorter formula will also work. Assuming the starting date is in cell A1 and the number of months is in cell B1, try the formula
=DATE(YEAR(A1),MONTH(A1)+B1,DAY(A1))
This will work for positive or negative number of months. -
-
Thanks guys. Will try that when I get back to work on monday. Great help
-
Guys those cannot work
-
Hi tradertt
Could you eleborate on "those do not work" please?
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!