Results 1 to 14 of 14
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Months in Advance ? (Excel 2002/2003)

    Hi

    In the attached I have tried to explain what I am trying to do. Because our prices increase is in March we have to try and keep reviews realistic.

    For example if you signed a contract in Sept you would no expect a price increase in 6 months.

    Many Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Months in Advance ? (Excel 2002/2003)

    If I understand your description correctly, if Current Date is 29 September 2007, the Revision Date would be 29 September 2008, but if Current Date is 30 September 2007, Revision Date would be 1 March 2009. Is that OK?

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Months in Advance ? (Excel 2002/2003)

    Or does this formula in P7 do what you want?

    =DATE(YEAR(O7)+1+(O7>DATE(YEAR(O7),9,29)),3,1)

  4. #4
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Months in Advance ? (Excel 2002/2003)

    Hi Hans

    Thanks for the prompt reply, all dates from the 30 Sept to December the 31st would be reviewed in March 2009. The months hence cannot be less than 12 Months.

    If the sign up date is less than 12 months to the next March it must go to the following March. Hope this is clear
    Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Months in Advance ? (Excel 2002/2003)

    I don't understand what the significance of the 29/30 September boundary is. The end of September is only 5 months away from the next March, not 12 months.

  6. #6
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Months in Advance ? (Excel 2002/2003)

    Hi Hans

    That is why it would have to go to the following March making it 17 months which is greater than the necessary 12.

    I have mad a slight modiciation to your previous formula and at first sight it looks to be what I need,

    =DATE(YEAR(Start_Date)+1+(Start_Date>DATE(YEAR(Sta rt_Date),9,29)),3,1) Start_Date being the cell containg the Start_ Date of the contract, But I think you may have sussed that. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    I will let you know how I get on with it.

    Many thanks

    Braddy
    If you are a fool at forty, you will always be a fool

  7. #7
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Months in Advance ? (Excel 2002/2003)

    Hi Hans

    No it didn't quite work, with my alteration to pick up the start_date, If I enter 1/8/07 it gives me March 08 only 7 months. Sorry about that.

    Any formula must be base on the name Start_Date.

    Regards

    Braddy
    If you are a fool at forty, you will always be a fool

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Months in Advance ? (Excel 2002/2003)

    I warned you that the September 29/30 boundary didn't make sense. How about this one:

    =DATE(YEAR(O7)+1+(MONTH(O7)>3),3,1)

  9. #9
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Months in Advance ? (Excel 2002/2003)

    Hi Hans

    Sorry to try your patience. Lets for forget about days of the month,

    January to August must be reviewed after 12 months. September to December must be reviewed past the first (March because it will be less than 12 months) to the next March

    January 07 to January 08 - April 07 to April 08 -September 07 to March 09 - October 07 to March 09 etc to December.

    Regards

    Braddy
    If you are a fool at forty, you will always be a fool

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Months in Advance ? (Excel 2002/2003)

    Still doesn't make sense to me, but try this

    =IF(MONTH(O7)>8,DATE(YEAR(O7)+2,3,1),DATE(YEAR(O7) +1,MONTH(O7),DAY(O7)))

  11. #11
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Months in Advance ? (Excel 2002/2003)

    Hi Hans

    Thanks for the patience, It matches my brief for this request. I think what is happening here because it is so late in the year they want to extend the review.


    Many Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

  12. #12
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Months in Advance ? (Excel 2002/2003)

    Hi Hans

    I bet you knew I would be back to you on this?

    I have a new breif now

    If cell named Start_date contains any month from Jan to August 2007, the review date would be March the following year ie March 08

    If cell named Star_Date contains any Month Sept to December 2007 the the review date would be March 09.

    Hope you can help with this.

    Many Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Months in Advance ? (Excel 2002/2003)

    =DATE(YEAR(Start_Date)+1+(MONTH(Start_Date)>8),3,1 )

  14. #14
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Months in Advance ? (Excel 2002/2003)

    Hi Hans

    Phew there's quick and there's quicker.

    Grateful Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

Posting Permissions

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