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

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

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 |

=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/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 |

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

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!