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

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,464
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
Nov 13, 2020
Messages
14
Office Version
  1. 365
Platform
  1. Windows
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)
 

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,464
Office Version
  1. 2016
Platform
  1. Windows
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)
 
Solution

Shoop

New Member
Joined
Nov 13, 2020
Messages
14
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Fantastic, Thanks again!
 

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,667
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
Joined
Nov 13, 2020
Messages
14
Office Version
  1. 365
Platform
  1. Windows
Thank you Dave. I will try these methods too. (y)
 

Watch MrExcel Video

Forum statistics

Threads
1,130,154
Messages
5,640,445
Members
417,143
Latest member
boukadidanizar

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
Top