is it possible to add 6 months to a given date?
i.e. if in cell A1 the date is 04/05/03, then cell B1 should return the date
plus 6 months ie 04/12/03. the only way ive been able to get something near
is by adding 182.5 which is half a year in days but this obviously doesnt
account for the different months having varying amounts of days.

Any Ideas?

Regards

Hoyt

You could try this:

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

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"hoyt" wrote in message
...
is it possible to add 6 months to a given date?
i.e. if in cell A1 the date is 04/05/03, then cell B1 should return the date
plus 6 months ie 04/12/03. the only way ive been able to get something near
is by adding 182.5 which is half a year in days but this obviously doesnt
account for the different months having varying amounts of days.

Any Ideas?

Regards

Hoyt

To add 6 months, but cater for that month having less days and not
spilling-over,

=MIN(DATE(YEAR(A1),MONTH(A1)+{7,6},DAY(A1)*{0,1}))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"hoyt" wrote in message
...
is it possible to add 6 months to a given date?
i.e. if in cell A1 the date is 04/05/03, then cell B1 should return the
date
plus 6 months ie 04/12/03. the only way ive been able to get something
near
is by adding 182.5 which is half a year in days but this obviously doesnt
account for the different months having varying amounts of days.

Any Ideas?

Regards

Hoyt

Hi

If you have the Analysis Toolpak loaded, ToolsAddinsAnalysis Toolpak
then
=EOMONTH(A1,6)

Otherwise the formula recently posted by Bob Phillips
=MIN(DATE(YEAR(A1),MONTH(A1)+{7,6},DAY(A1)*{0,1}))

which caters for the fact that adding 6 months to 31 March, returns 01
October and not 30 September when adding 6 months by the standard
method.

--
Regards

Roger Govier


"hoyt" wrote in message
...
is it possible to add 6 months to a given date?
i.e. if in cell A1 the date is 04/05/03, then cell B1 should return
the date
plus 6 months ie 04/12/03. the only way ive been able to get something
near
is by adding 182.5 which is half a year in days but this obviously
doesnt
account for the different months having varying amounts of days.

Any Ideas?

Regards

Hoyt

Bob
You always can type faster than me<bg

--
Regards

Roger Govier


"Bob Phillips" wrote in message
...

To add 6 months, but cater for that month having less days and not
spilling-over,

=MIN(DATE(YEAR(A1),MONTH(A1)+{7,6},DAY(A1)*{0,1}))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"hoyt" wrote in message
...
is it possible to add 6 months to a given date?
i.e. if in cell A1 the date is 04/05/03, then cell B1 should return
the
date
plus 6 months ie 04/12/03. the only way ive been able to get
something
near
is by adding 182.5 which is half a year in days but this obviously
doesnt
account for the different months having varying amounts of days.

Any Ideas?

Regards

Hoyt

Hope you can get Excel to do it as you've made a mistake doing it by hand
:)

6 calendar months on would be 04/11/03.

Nice solution using MIN( with array - must get used to using that more.


On Sat, 08 Jul 2006 19:46:02 +0100, hoyt
wrote:

is it possible to add 6 months to a given date?
i.e. if in cell A1 the date is 04/05/03, then cell B1 should return the
date
plus 6 months ie 04/12/03. the only way ive been able to get something
near
is by adding 182.5 which is half a year in days but this obviously doesnt
account for the different months having varying amounts of days.

Any Ideas?

Regards

Hoyt



--
Steve (3)

That's because I don't type it. I have a library of stuff that I just cut
and paste from in many instances <vbg

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Roger Govier" wrote in message
...
Bob
You always can type faster than me<bg

--
Regards

Roger Govier


"Bob Phillips" wrote in message
...

To add 6 months, but cater for that month having less days and not
spilling-over,

=MIN(DATE(YEAR(A1),MONTH(A1)+{7,6},DAY(A1)*{0,1}))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"hoyt" wrote in message
...
is it possible to add 6 months to a given date?
i.e. if in cell A1 the date is 04/05/03, then cell B1 should return
the
date
plus 6 months ie 04/12/03. the only way ive been able to get
something
near
is by adding 182.5 which is half a year in days but this obviously
doesnt
account for the different months having varying amounts of days.

Any Ideas?

Regards

Hoyt

Thanks Bob, this works Brilliant.

Regards

Hoyt

"Bob Phillips" wrote:


To add 6 months, but cater for that month having less days and not
spilling-over,

=MIN(DATE(YEAR(A1),MONTH(A1)+{7,6},DAY(A1)*{0,1}))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"hoyt" wrote in message
...
is it possible to add 6 months to a given date?
i.e. if in cell A1 the date is 04/05/03, then cell B1 should return the
date
plus 6 months ie 04/12/03. the only way ive been able to get something
near
is by adding 182.5 which is half a year in days but this obviously doesnt
account for the different months having varying amounts of days.

Any Ideas?

Regards

Hoyt