Date function

  • Thread starter Thread starter Guest
  • Start date Start date
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
 
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?
 
Hello Again,
There are 30 different dates that need 6 months added,
copy and paste won't work, is there a way?
Thanks again.
 
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
 
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.
 
Back
Top