Dates in Formulae

[Aardvark]

Dear all,

Suppose I want to create a basic formula eg one which adds 6 months to a
date, how would I do it? Doing it by date + 6 months worth of days will
obviously not work as the number of days in 6 months will vary.

Any thoughts?

Thanks,

Danny

 

[Niek Otten]

Hi Danny,

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

But do think about what you want the day to be in case the source date is,
for example, august 31.

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

 

[Ron Rosenfeld]

Dear all,

Suppose I want to create a basic formula eg one which adds 6 months to a
date, how would I do it? Doing it by date + 6 months worth of days will
obviously not work as the number of days in 6 months will vary.

Any thoughts?

Thanks,

Danny

Well, first you have to answer the question: what is Jan 31, 2004 plus one
month?

If it is Mar 2, 2004, then, with your base date in A6,the formula:

=DATE(YEAR(A6),MONTH(A6)+1,DAY(A6))

However, if it is Feb 29, 2004, then:

=edate(A6,1)

If the EDATE function is not available, and returns the #NAME? error, install
and load the Analysis ToolPak add-in.

On the Tools menu, click Add-Ins.
In the Add-Ins available list, select the Analysis ToolPak box, and then click
OK.
If necessary, follow the instructions in the setup program.


--ron

 

[Norman Harker]

Hi Aardvark!

If you want a "general" solution that works for creating any series of
evenly spaced months:

=DATE(YEAR(A1),MONTH(A1)+AddMons,MIN(DAY($A$1),DAY(DATE(YEAR(A1),MONTH
(A1)+AddMons+1,0))))

Where AddMons is the number of months to be added.



And here’s an alternative that does the same by Peter Dorigo:



=MIN(DATE(YEAR(A1),MONTH(A1)+ AddMons +{1,0},DAY($A$1)*{0,1}))




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