Excel 3-color scale based on dynamic data

[Jon Firooz]

Hi,

I have a spreadsheet that includes a date column.  What I would like is to figure out a way to use Excel's 3 color scale so the date furthest back is shaded color 1, today is color 2, and the furthest date out is color 3 and dates in between are along the color gradient.  I know I could set up three conditional formatting rules that color based on past/present/future but I really want something that uses the color gradient so the furthest date out is a solid green, a date halfway in between is a lighter green, etc.  I've tried experimenting and Googling and haven't found anything that works.  Is it possible to do what I want?

Thanks!

[#2 · Accepted solution]

Hi Jon,

Perhaps like this

[#3]

That is brilliantly simple!  I didn't know you could leave the min and max formulas blank.  That's a great solution, thank you!!

[#4]

You are welcome