Microsoft Excel - I need to apply a discount to a cell based on the date. ie if C2 is less than 7days fromA1

I need to apply a discount to a cell based on the date. ie if C2 is less than 7 days from A1 (A1 is =Today() ) then multiply C2 by .25 if it is less than 14 days away then multiply by .20 if it is less then 21 days away then multiply by .15 if it is less than 28 days away then multiply by .10

Thanks!

Hello Matt

I have reinterpreted what you have written and assume that you wish to multiply a value in D2 by the various factors.

The easiest way is to set up a table of the time intervals and discounts and use a lookup to get the appropriate value.

Enter into H1:I5 the following values

0	0.25
7	0.2
14	0.15
21	0.1
28	0

Then, in E2 enter the following formula
=VLOOKUP($A$1-C2,$H$1:$I$5,2)*D2
This will be able to be copied down.

Of course the table and calculation cell(s) can be located anywhere with appropriate changes to the addresses.

The job can also be done with a complex IF formula but I think it is better to go this way.

Regards
Harry

=VLOOKUP($A$1-C2,$H$1:$I$5,2)*D2

Hi Harry,

Thank you for your help. I do not think I explained this right... So here is a snapshot of what I have. A1 represents today's date. Below that are the names of different homes that I rent out. To the right are the rates per night for each home (it extends all the way through 2015). I need the rates to be discounted as the dates get closer to todays date. So if we are within a 7 day window the rates are all discounted by 25% if we are in a 14 day window they are discounted by 20% 21 days is discounted by 15% and 28 days is 10%. Does that make more sense?

Thanks again for your help!

11/17/2014	1/1/2026	1/2/2026	1/3/2026	1/4/2026
Alta Mirada	744.63	864.25	239.25	239.25
Las Sendas	789.63	954.25	329.25	329.25
Ocotillo	789.63	954.25	329.25	329.25
Seville	789.63	954.25	329.25	329.25
Sonoma Gilbert	789.63	954.25	329.25	329.25
Chandler	827.13	1029.25	404.25	404.25
La Jolla	827.13	1029.25	404.25	404.25
Solara	827.13	1029.25	404.25	404.25
Elmwood Place	849.63	1074.25	449.25	449.25

Hi Matt,
That does make more sense.  Have a look at this file.
matt_discount.zip
I think it should be self explanatory.  When the date in A1 gets into January the cells for January will show #N/A.  That can be hidden, if desired, by using conditional formatting.
Hope this helps.
Harry

Wow, that is perfect! Thank you very much!