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" <(E-Mail Removed)> wrote in message
news:435339DA-C5D0-4DFF-8D23-(E-Mail Removed)...
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" <(E-Mail Removed)> wrote in message
news:435339DA-C5D0-4DFF-8D23-(E-Mail Removed)...
> 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, Tools>Addins>Analysis 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" <(E-Mail Removed)> wrote in message
news:435339DA-C5D0-4DFF-8D23-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>
> 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" <(E-Mail Removed)> wrote in message
> news:435339DA-C5D0-4DFF-8D23-(E-Mail Removed)...
>> 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 <(E-Mail Removed)>
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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Bob
> You always can type faster than me<bg>
>
> --
> Regards
>
> Roger Govier
>
>
> "Bob Phillips" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> >
> > 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" <(E-Mail Removed)> wrote in message
> > news:435339DA-C5D0-4DFF-8D23-(E-Mail Removed)...
> >> 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" <(E-Mail Removed)> wrote in message
> news:435339DA-C5D0-4DFF-8D23-(E-Mail Removed)...
> > 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
>
>
>