Escalation based on future dates

Shoop

New Member
Joined
Nov 13, 2020
Messages
14
Office Version
  1. 365
Platform
  1. Windows
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

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
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)
 
Upvote 0
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?
(heading home for the day)
 
Upvote 0
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)
 
Upvote 0
Solution
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)
 
Upvote 0
Thank you Dave. I will try these methods too. (y)
 
Upvote 0

Forum statistics

Threads
1,213,530
Messages
6,114,162
Members
448,554
Latest member
Gleisner2

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top