D
Debbie
If DOB is in a1; how would you add 18 months to that DOB?
Thanks!
Thanks!
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
J
JP Ronse
Hi Debbie
=DATE(YEAR(J2);MONTH(J2)+18;DAY(J2))
With J2 = DOB
Note that my locale use ";" instead of ",". You might have to change this.
Wkr,
JP
=DATE(YEAR(J2);MONTH(J2)+18;DAY(J2))
With J2 = DOB
Note that my locale use ";" instead of ",". You might have to change this.
Wkr,
JP
D
Debbie
Hi Debbie
=DATE(YEAR(J2);MONTH(J2)+18;DAY(J2))
With J2 = DOB
Note that my locale use ";" instead of ",". You might have to change this..
Wkr,
JP
- Show quoted text -
Awesome, thank you! Works great.
M
macropod
Hi Debbie,
The safest formula is:
=MIN(DATE(YEAR(A1),MONTH(A1)+18+{0,1},DAY(A1)*{1,0}))
With this, if the starting month has more days than the end month, and the start day is on the last day of the month, the returned
date will be the last day of the month that's 18 months hence. For example, if the start date is 31/August/2008, the returned date
would be 28/February/2010.
However, a formula like:
=DATE(YEAR(A1),MONTH(A1)+18,DAY(A1))
is liable to return a date in the following month in such circumstances. For example, if the start date is 31/August/2008, the
returned date would be 3/March/2010.
As barry houdini says, you can use EDATE function, but this requires the Analysis ToolPak to be activated in versions before Excel
2007.
The safest formula is:
=MIN(DATE(YEAR(A1),MONTH(A1)+18+{0,1},DAY(A1)*{1,0}))
With this, if the starting month has more days than the end month, and the start day is on the last day of the month, the returned
date will be the last day of the month that's 18 months hence. For example, if the start date is 31/August/2008, the returned date
would be 28/February/2010.
However, a formula like:
=DATE(YEAR(A1),MONTH(A1)+18,DAY(A1))
is liable to return a date in the following month in such circumstances. For example, if the start date is 31/August/2008, the
returned date would be 3/March/2010.
As barry houdini says, you can use EDATE function, but this requires the Analysis ToolPak to be activated in versions before Excel
2007.