Need Help with IF Statement on VLOOKUP on Dates

[MYeag]

I have a workbook that has several VLOOKUPS throughout it. The purpose is to take several pieces of information and link it to our individual customer pricing templates.

The item I need help is this. Some of our customers have price protection. So our price increase effective dates are different depending on the customer price protection.

My New effective date is a VLOOKUP - =VLOOKUP(G:G,'Product List - % Increase'!$D$35:$F$1099,3,FALSE)+Q1304

My Price Protection is a VLOOKUP - =VLOOKUP(B:B,'Price Protection'!$A$2:$O$100,15,FALSE)

How do I get my new effective date to round up to the 1st of the month? If I have a month with 30 days my effective date needs to be the 1st of the following month no matter whether there are 28 days, 30 days or 31 days in the month. Can you help me?

<TABLE style="WIDTH: 524pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=698><COLGROUP><COL style="WIDTH: 96pt; mso-width-source: userset; mso-width-alt: 4681" width=128><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2742" width=75><COL style="WIDTH: 61pt; mso-width-source: userset; mso-width-alt: 2962" width=81><COL style="WIDTH: 69pt; mso-width-source: userset; mso-width-alt: 3364" width=92><COL style="WIDTH: 105pt; mso-width-source: userset; mso-width-alt: 5120" width=140><COL style="WIDTH: 54pt; mso-width-source: userset; mso-width-alt: 2633" width=72><COL style="WIDTH: 83pt; mso-width-source: userset; mso-width-alt: 4022" width=110><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 96pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl70 height=20 width=128>New Effective Date</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 56pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl71 width=75>Effect Date</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 61pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl71 width=81>Expire Date</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 69pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl70 width=92>Current Price</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 105pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl70 width=140>Percentage Increase</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 54pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl70 width=72>New Price</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 83pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl70 width=110>Price Protection</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl74 height=20>7/31/2011</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl74>2/15/2011</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl74>12/31/2040</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl73>2.99</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl72>10%</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl73>3.29</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl73>30</TD></TR></TBODY></TABLE>

 

[shadow12345]

I If I have a month with 30 days my effective date needs to be the 1st of the following month no matter whether there are 28 days, 30 days or 31 days in the month.

Sorry just to check... if you a what month ? within 30 days?

are you saying if today is within 30 days of the effective date, or are you asking to move the effect date to the first of the month?

(in your example I think that would make the new effective date 03/01/2011 (1st March))

 

[MYeag]

No - our effective dates always start the 1st of every month - so we could have a new price go into effect 8/1/11. But with the customer price protection of 30 days the true effective date is 8/31/11. I am trying to get it to 9/1/11 - so basically round up to the next month if that makes sense. Say I have a price increase 2/1/11 - with 30 days price protection that would make true effective date 3/2/11 - I would want it to be 3/1/11.