3 color scale conditional formatting but ONLY applying to certain date range

Question

Hello, I'm having difficulty creating a conditional formatting 3 Color Scale Rule that only applies to a certain date range, specifically the last year from whatever the current day is [=TODAY()].

In Conditional Formatting Rules Manager, under 'Format all cells based on their values' I've tried using Minimum Formula as "=TODAY()-365", Midpoint as PERCENTILE "50", and Maximum Formula as "=TODAY()", and applying to =$C$2:$C$99" for the entire contents of column "C".

However, the formatting/formula keeps applying to the entire column, even if it is outside the date range.

I also tried Minimum Formula as "=TODAY()-360", Midpoint Formula as "=TODAY()-180", and Maximum Formula as "=TODAY()", and applying to =$C$2:$C$99" for the entire contents of column "C", and it still applies to the entire column, even if outside the date range.

I ONLY want the Conditional Formatting/3 Color Scale to apply to dates that are within the last year/365 days of today. I plan to do something else with dates that are beyond a year old. However, the formula keeps applying to the entire column, regardless of the date. Does anyone have a solution or alteration that will work for this problem?

Answer 1

You should set additional two rules to stop the formatting out of your range since the 3 color scale conditional formatting will apply the same color if the value is greater than the max or less than the min.