Adding 6 months to any given date

[hoyt]

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

 

[RagDyeR]

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!
=====================================================

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 Phillips]

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)

 

[Roger Govier]

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.

 

[Roger Govier]

Bob
You always can type faster than me<bg>

 

[SteveW]

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.

 

[Bob Phillips]

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)

 

[hoyt]

Thanks Bob, this works Brilliant.

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)