+ Reply to Thread
Results 1 to 15 of 15

Adding 6 months to any given date

  1. #1
    hoyt
    Guest

    Adding 6 months to any given date

    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

  2. #2
    RagDyeR
    Guest

    Re: Adding 6 months to any given date

    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" <[email protected]> wrote in message
    news:[email protected]...
    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



  3. #3
    Bob Phillips
    Guest

    Re: Adding 6 months to any given date


    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" <[email protected]> wrote in message
    news:[email protected]...
    > 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




  4. #4
    Roger Govier
    Guest

    Re: Adding 6 months to any given date

    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" <[email protected]> wrote in message
    news:[email protected]...
    > 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




  5. #5
    Roger Govier
    Guest

    Re: Adding 6 months to any given date

    Bob
    You always can type faster than me<bg>

    --
    Regards

    Roger Govier


    "Bob Phillips" <[email protected]> wrote in message
    news:[email protected]...
    >
    > 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" <[email protected]> wrote in message
    > news:[email protected]...
    >> 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

    >
    >




  6. #6
    SteveW
    Guest

    Re: Adding 6 months to any given date

    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 <[email protected]=
    > =


    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)

  7. #7
    Bob Phillips
    Guest

    Re: Adding 6 months to any given date

    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" <[email protected]> wrote in message
    news:[email protected]...
    > Bob
    > You always can type faster than me<bg>
    >
    > --
    > Regards
    >
    > Roger Govier
    >
    >
    > "Bob Phillips" <[email protected]> wrote in message
    > news:[email protected]...
    > >
    > > 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" <[email protected]> wrote in message
    > > news:[email protected]...
    > >> 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

    > >
    > >

    >
    >




  8. #8
    hoyt
    Guest

    Re: Adding 6 months to any given date

    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" <[email protected]> wrote in message
    > news:[email protected]...
    > > 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

    >
    >
    >


  9. #9
    RagDyeR
    Guest

    Re: Adding 6 months to any given date

    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" <[email protected]> wrote in message
    news:[email protected]...
    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



  10. #10
    Bob Phillips
    Guest

    Re: Adding 6 months to any given date


    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" <[email protected]> wrote in message
    news:[email protected]...
    > 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




  11. #11
    Roger Govier
    Guest

    Re: Adding 6 months to any given date

    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" <[email protected]> wrote in message
    news:[email protected]...
    > 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




  12. #12
    Roger Govier
    Guest

    Re: Adding 6 months to any given date

    Bob
    You always can type faster than me<bg>

    --
    Regards

    Roger Govier


    "Bob Phillips" <[email protected]> wrote in message
    news:[email protected]...
    >
    > 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" <[email protected]> wrote in message
    > news:[email protected]...
    >> 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

    >
    >




  13. #13
    SteveW
    Guest

    Re: Adding 6 months to any given date

    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 <[email protected]=
    > =


    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)

  14. #14
    Bob Phillips
    Guest

    Re: Adding 6 months to any given date

    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" <[email protected]> wrote in message
    news:[email protected]...
    > Bob
    > You always can type faster than me<bg>
    >
    > --
    > Regards
    >
    > Roger Govier
    >
    >
    > "Bob Phillips" <[email protected]> wrote in message
    > news:[email protected]...
    > >
    > > 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" <[email protected]> wrote in message
    > > news:[email protected]...
    > >> 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

    > >
    > >

    >
    >




  15. #15
    hoyt
    Guest

    Re: Adding 6 months to any given date

    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" <[email protected]> wrote in message
    > news:[email protected]...
    > > 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

    >
    >
    >


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1