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

Asked By Matt Demo on 17-Nov-14 03:41 PM

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!

Harry Boughen replied to Matt Demo on 17-Nov-14 05:47 PM
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
Matt Demo replied to Harry Boughen on 17-Nov-14 06:57 PM

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/2015 1/2/2015 1/3/2015 1/4/2015
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
Harry Boughen replied to Matt Demo on 17-Nov-14 07:47 PM
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
Matt Demo replied to Harry Boughen on 17-Nov-14 09:17 PM
Wow, that is perfect! Thank you very much!