Excel Conditional Formatting 3-Color Scale Formula

Question

Hello, 

I hope someone will be able to help me:

I am using Office 365. I have a set of dates in one column, say C and another set of dates in D. The dates in D are always lower (earlier) than the corresponding dates in C. I want to apply three-color grading to dates in D based on the difference between the corresponding date in C. For example, if C4-D4 is less than a week (7), D4 should be green. If C2-D2 is between 1-2 weeks (7<C2-D2<14), D2 should be amber. If C8-D8 is more than four weeks (>28), D8 should be red. Right now, I have three separate rules for each such situation - hence I have three distinct colors and not gradient colors. I am just wondering if there is a way to optimize this?

I am sorry if this is a repetitive question. I tried to search for similar questions but could not find the right one. If there are similar posts, could you please re-direct me?

EDIT: Please see further explanation in reply to a post below

Answer 1

Hi Faysal, 

Thank you for your reply. However, let me explain a bit more with the following screenshot (Figure 1). For the Diff column, I have used the conditional formatting rule in Figure 2. My aim is to get the same kind of color grading (not just three colors but graded colors) for column D as E without using the Diff column at all. 

  

Answer 2

You are doing right to use distinct rules for 3 conditions. You need to specify clearly each rules with AND() so that Excel can identify what values exactly to format. like this:

For <7 Days:

• Select D2:D15 cell.
• Go to: Home | Conditional Formatting | New Rules
• Apply this formula and Select Green Color: =(C2-D2)<7
• Click Ok

For <14 Days:

• Select D2:D15 cell.
• Again Go to: Home | Conditional Formatting | New Rules
• Apply this formula and Select Amber Color: =AND((C2-D2)>=7,(C2-D2)<=14)
• Click Ok

For >28 Days:

• Select D2:D15 cell.
• Again Go to: Home | Conditional Formatting | New Rules
• Apply this formula and Select Red Color: =(C2-D2)>28
• Click Ok

Hope this will help you.

Answer 3

Hi Thenga,

It seems that Faysal’s suggestion can meet your requirements. If yes, you can try the steps he mentioned above to check the result. If no, about “My aim is to get the same kind of color grading (not just three colors but graded colors) for column D as E without using the Diff column at all. “, could you give some examples here for us to clarify your requirements?

Best Regards,

Cliff

Answer 4

Hi Thenga,

Welcome to share any updates at your convenience if you need further help on this issue.

Best Regards,

Cliff

Answer 5

Hi Cliff, 

I hope this explanation makes my requirements clearer. I am currently using a method similar to what Faysal had suggested and this gives me three distinct colors for my Date 2 column. For this, I have three separate rules for which I use the rule type "Use a formula to determine which cells to format".

However, I want graded color scheme (not three distinct colors but different shades proportional to deviation from colors allocated for minimum and for maximum values) for my Date 2 column. In Figure 2 above, the graded color scheme has been applied to the Diff column by using 3-color format style under the rule type "Format all cells based on their values". Is there a possibility that I can apply such a rule type and 3-color format style on the Date 2 column? 

If there is a way to match the color of Diff column cells in the respective cells in Date 2 column, that will also meet my requirement. 

Hope this makes my requirement clearer. 

Kind Regards, 

thenga