Adding 6 months to any given date hoyt 08.07.2006 18:46:02 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

Re: Adding 6 months to any given date "RagDyeR" <ragdyer[ at ]cutoutmsn.com> 08.07.2006 19:07:29 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" <hoyt[ at ]discussions.microsoft.com> wrote in message news:435339DA-C5D0-4DFF-8D23-9A8628AB3722[ at ]microsoft.com... 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

Re: Adding 6 months to any given date "Bob Phillips" <bob.NGs[ at ]somewhere.com> 08.07.2006 21:16:12 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" <hoyt[ at ]discussions.microsoft.com> wrote in message news:435339DA-C5D0-4DFF-8D23-9A8628AB3722[ at ]microsoft.com... [Quoted Text] > 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

Re: Adding 6 months to any given date "Roger Govier" <roger[ at ]technologyNOSPAM4u.co.uk> 08.07.2006 21:20:33 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" <hoyt[ at ]discussions.microsoft.com> wrote in message news:435339DA-C5D0-4DFF-8D23-9A8628AB3722[ at ]microsoft.com... [Quoted Text] > 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

Re: Adding 6 months to any given date "Roger Govier" <roger[ at ]technologyNOSPAM4u.co.uk> 08.07.2006 21:29:44 Bob You always can type faster than me<bg> -- Regards Roger Govier "Bob Phillips" <bob.NGs[ at ]somewhere.com> wrote in message news:OZMaPPtoGHA.3288[ at ]TK2MSFTNGP03.phx.gbl... [Quoted Text] > > 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" <hoyt[ at ]discussions.microsoft.com> wrote in message > news:435339DA-C5D0-4DFF-8D23-9A8628AB3722[ at ]microsoft.com... >> 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 > >

Re: Adding 6 months to any given date SteveW <sj_walton[ at ]nothotmail.com> 09.07.2006 08:39:17 Hope you can get Excel to do it as you've made a mistake doing it by han= d = :) 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 <hoyt[ at ]discussions.microsoft.com= [Quoted Text] > = 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 th= e = > 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 doe= snt > account for the different months having varying amounts of days. > > Any Ideas? > > Regards > > Hoyt -- = Steve (3)

Re: Adding 6 months to any given date "Bob Phillips" <bob.NGs[ at ]somewhere.com> 09.07.2006 08:54:41 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" <roger[ at ]technologyNOSPAM4u.co.uk> wrote in message news:eQRQlWtoGHA.4932[ at ]TK2MSFTNGP05.phx.gbl... [Quoted Text] > Bob > You always can type faster than me<bg> > > -- > Regards > > Roger Govier > > > "Bob Phillips" <bob.NGs[ at ]somewhere.com> wrote in message > news:OZMaPPtoGHA.3288[ at ]TK2MSFTNGP03.phx.gbl... > > > > 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" <hoyt[ at ]discussions.microsoft.com> wrote in message > > news:435339DA-C5D0-4DFF-8D23-9A8628AB3722[ at ]microsoft.com... > >> 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 > > > > > >

Re: Adding 6 months to any given date hoyt 09.07.2006 10:14:01 Thanks Bob, this works Brilliant. Regards Hoyt "Bob Phillips" wrote: [Quoted Text] > > 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" <hoyt[ at ]discussions.microsoft.com> wrote in message > news:435339DA-C5D0-4DFF-8D23-9A8628AB3722[ at ]microsoft.com... > > 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 > > >