1. ## 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

2. ## 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. ## 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. ## 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

5. ## 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. ## 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

7. ## 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

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

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

9. ## 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

10. ## 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. ## 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

12. ## 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

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

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

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

Hi Hans

Phew there's quick and there's quicker.

Grateful Thanks