# Escalation based on future dates

#### Shoop

##### New Member
Hello,
We would like to input the month/year a project is expected to start and have a rate returned escalation if warranted.

Example: In cell D1 estimator would input the month and year a project is scheduled to start, March, 2021 and the formula in cell F1 would calculate/populate a rate of 2%. The bid worksheets in this workbook look to 'Sheet1'!\$F\$1 for the escalation rate.
• Today's date plus 0-3 months, rate of 0%
• Today's date plus 3-6 months, rate of 2%
• Today's date plus 6-12 months, rate of 3.5%
I hope this is a simple challenge but my Excel abilities are limited so solution would be greatly appreciated.

Thank you,

### Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

##### Well-known Member
Hi Shoop,

Does this do what you want?

Book1
DEFGHI
101-Mar-210.00%MonthsRate
200.00%
332.00%
463.50%
Sheet1
Cell Formulas
RangeFormula
F1F1=VLOOKUP(DATEDIF(TODAY(),D1,"M"),H2:I4,2,1)

#### Shoop

##### New Member
Works well, thanks!
If I wanted to use a table (instead of H2:I4) and move it to another worksheet, could you help me adjust the formula?

##### Well-known Member
So the table would look like this

Shoop.xlsx
AB
1MonthsRate
200.00%
332.00%
463.50%
Sheet2

...and the formula change would be

Shoop.xlsx
DEF
101-Mar-210.00%
Sheet1
Cell Formulas
RangeFormula
F1F1=VLOOKUP(DATEDIF(TODAY(),D1,"M"),Table1,2,1)

#### Shoop

##### New Member

Fantastic, Thanks again!

#### Dave Patton

##### Well-known Member
Two alternatives that are flexible. A lookup table does not need to be on the sheet.

T202012a.xlsm
ABCDEF
11-Jul-20210.035
21-Jul-20210.035uses a named array aRates ={0,0;3,0.02;6,0.035}
3
2b
Cell Formulas
RangeFormula
E1E1=LOOKUP(DATEDIF(TODAY(),D1,"M"),{0,0;3,0.02;6,0.035})
E2E2=LOOKUP(DATEDIF(TODAY(),D2,"M"),aRates)

#### Shoop

##### New Member
Thank you Dave. I will try these methods too.

Replies
0
Views
86
Replies
7
Views
707
Replies
29
Views
651
Replies
5
Views
177
Replies
8
Views
271

1,126,938
Messages
5,621,715
Members
415,853
Latest member
Newlife72

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back