Gradient conditional formatting for due dates?

diversification

New Member
Joined
Jun 24, 2020
Messages
15
Office Version
  1. 365
Platform
  1. Windows
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!
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,882
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jun 24, 2020
Messages
15
Office Version
  1. 365
Platform
  1. Windows
AB
1DateDesired 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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,882
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jun 24, 2020
Messages
15
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,757
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,246
Messages
5,595,044
Members
413,963
Latest member
teggl97

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top