Date function

[Susan]

How do I add months to a date? EX 2-1-04 + 6 month
Possible? thanks

 

[Frank Kabel]

Hi
one way:
=DATE(YEAR(A1),MONTH(A1)+6,DAY(A1))

but this can cause problems if A1 is the end of a month. A more
generic/robust version would be
=DATE(YEAR(A1),MONTH(A1)+addmonths,MIN(DAY(A1),DAY(DATE(YEAR(A1),MONTH(
A1)+addmonths+1,0))))
replace addmonths with 6 in your example

 

[Arvi Laanemets]

Hi

=DATE(2004,1+6,2)

 

[JE McGimpsey]

One way:

=Date(YEAR(A1),MONTH(A1)+6,DAY(A1))

another:

=DATE(YEAR(A1),MONTH(A1)+6, MIN(DAY(A1), DAY(DATE(YEAR(A1),
MONTH(A1)+6+1,0))))



Note, however, that "months" is a rather imprecise term - is one month
after 31 January = 28 February, 29 February, 2 March? (the first formula
above, using 1 month instead of 6, produces 2 March for 2004, the
second, 29 February).

Is one month after 30 January (say 29 February) = one month after 31
January?

 

[Susan]

Please break down the formula. Thank you very very much.

 

[Susan]

Hello Again,
There are 30 different dates that need 6 months added,
copy and paste won't work, is there a way?
Thanks again.

 

[Frank Kabel]

Hi
if your dates are stored in column A (starting in row 1) enter the
following in cell B1:
=DATE(YEAR(A1),MONTH(A1)+6,MIN(DAY(A1),DAY(DATE(YEAR(A1),MONTH(A1)+7,0)
)))

and copy down

 

[Norman Harker]

Hi Susan!

Just so you're embarassed as to choice:

=EDATE(A1,6)

Where the input date is 31-Aug-2003, you'll find that EDATE adopts the
algorithm that if the day does not exist in the 6th month after the base
date, it will take the last day of that month.

EDATE is an Analysis ToolPak function. If you get #NAME! on entry of the
formula you should check with Tools > Addis whether Analysis ToolPak is
installed and selected.

--
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.