Gradient conditional formatting for due dates?

[diversification]

Hello,

I'm trying to conditionally format a column that's filled with due dates. I'd like it to to be conditionally formatted with a 3-color gradient starting at yellow, moving to orange, and finally to red. I'd like the fill to begin with yellow when the due date in the cell is about 10 days away, then progress to be fully orange sometime around the 5-day-away mark, and then becoming fully red once the due date has arrived or passed. I'm not really certain where to start with with his one. Dates in the date column are formatted as DD/MM/YYYY if that matters.

I would like to avoid any VBA code - I haven't gotten into that stuff yet. Thanks!

 

[Joe4]

You can do this by setting up three different Conditional Formatting rules (one for each color), using the Formula option.

Note that when you have multiple rules, you can set them up in any order, and tell it to stop and not look at the other rules once a "TRUE" condition is found (see "Manage Rules").
Or, you can make your formulas restrictive (i.e. only run if between 5 and 10 days) to ensure that only one rule can be met at a time.

If you need help setting them up, please provide more details, and maybe post a data sample), so we can see the exact ranges you are applying this to.

 

[diversification]

	A	B
1	Date	Desired Fill Color
2	1/23/2021​	no fill
3	1/28/2021​	no fill
4	2/18/2021​	orange-yellow gradient
5	1/15/2021​	orange-red gradient
6	1/12/2021​	red
7	1/14/2021​	red-orange gradient
8	3/27/2021​	no fill
9	1/20/2021​	yellow-orange gradient
10	1/23/2021​	no fill
11	1/4/2021​	red
12	1/9/2021​	red

With today = 01/12/2021, this is essentially what I'm trying to create. I want a continuous gradient of color changing based on how close it is to the due date. Yellow will start at day 10 from due date, 9 days will be mostly yellow with a bit of orange, 8 days will be mostly yellow but a bit more orange, and so on until 5 days when it's fully changed to orange. Days 4 to 0 will have it slowly change from orange to red.

Can you explain your proposed method a bit? Thanks!

 

[Joe4]

Can you explain your proposed method a bit? Thanks!

Sorry, I misunderstood. My proposed method would produce only three colors, red, orange, yellow, without and blending.
I do a ton with Conditional Formatting formulas, but really do not ever use the Gradient options.

 

[diversification]

Sorry, I misunderstood. My proposed method would produce only three colors, red, orange, yellow, without and blending.
I do a ton with Conditional Formatting formulas, but really do not ever use the Gradient options.

Yeah, solids are the extent of my experience too, and I'm not finding a whole lot about how to setup gradients this way.

 

[Fluff]

As far as I'm aware you cannot do what you're after using colour scales, you would need to set-up 10 different rules one for each colour.

 

[healer213]

It's not a perfect solution, but I figured out a way to do it. I was trying to do it for my personal budgeting, calculating when the next charge would come, from green to red as it approached. Here's a couple screens to show what I'm talking about.

I know this is an old thread, but in case it shows up for anyone else (like me) who stumbled on it via Google - here's my solution:
3-color scale: Minimum =TODAY(), Midpoint =50 percentile, Max = 75 percentile

Adjust the percentiles to match how far out you want it showing things as "green" or "yellow"

 

[healer213]

It's not a perfect solution, but I figured out a way to do it. I was trying to do it for my personal budgeting, calculating when the next charge would come, from green to red as it approached. Here's a couple screens to show what I'm talking about.

I know this is an old thread, but in case it shows up for anyone else (like me) who stumbled on it via Google - here's my solution:
3-color scale: Minimum =TODAY(), Midpoint =50 percentile, Max = 75 percentile

Adjust the percentiles to match how far out you want it showing things as "green" or "yellow"

Correction: I've already broken it with other sheets now. It might not work as nicely as you expect - especially as due dates pass. You'd need a separate rule for those to clear the red from the gradient.

 

[Leemichelle]

Hello,

I'm trying to conditionally format a column that's filled with due dates. I'd like it to to be conditionally formatted with a 3-color gradient starting at yellow, moving to orange, and finally to red. I'd like the fill to begin with yellow when the due date in the cell is about 10 days away, then progress to be fully orange sometime around the 5-day-away mark, and then becoming fully red once the due date has arrived or passed. I'm not really certain where to start with with his one. Dates in the date column are formatted as DD/MM/YYYY if that matters.

I would like to avoid any VBA code - I haven't gotten into that stuff yet. Thanks!

This is something that I was trying to do. I have found what has worked for me and might work for you too. When using gradients you need to give red/yellow/green a specific value so that the gradient can then be applied between them. I used:
red - today()-7 (any dates more than 7 days in the past are then also red)
yellow - today()+1
green - today()+14

can increase the number of days in the plus/minus to spread the gradient depending on what you want.

 

[diversification]

This is something that I was trying to do. I have found what has worked for me and might work for you too. When using gradients you need to give red/yellow/green a specific value so that the gradient can then be applied between them. I used:
red - today()-7 (any dates more than 7 days in the past are then also red)
yellow - today()+1
green - today()+14

can increase the number of days in the plus/minus to spread the gradient depending on what you want.

This seems like it'll probably do exactly what I'm after, but I'll test it to verify first. Thank you!