# 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

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

##### 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
74
Replies
8
Views
218
Replies
0
Views
115
Replies
3
Views
87
Replies
7
Views
775

1,129,890
Messages
5,638,860
Members
417,055
Latest member
SerenaCruz

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