conditional formatting for 3-color scales with formulas

Nothnless

Board Regular
Joined
Apr 28, 2016
Messages
142
Hi, I am trying to create a heat map using conditional formatting for 3-color scales with formulas.

The heat map is supposed to work like this:


  1. Input a target date in cell D2
  2. Input the date the Item was completed in cells A2-C2
  3. If the completion date is more than 60 days prior to the Target date then Green
  4. If the completion date is in between 60 days and 30 days prior to the completion date then Yellow
  5. If the completion date is less than 30 days prior to the Target date then Red

So basically a formula like this (example):
=IF(A2<workday(d2,-60),"green",if(and(a2>WORKDAY(D2,-60), A2<workday(d2,-30)),"yellow",if(a2>WORKDAY(D2,-30),"Red",)))

But how do I achieve a 3-color scale heat map for this?

For the conditional formatting formula I tried to enter:
=$A$2<workday($d$2,-60) but="" when="" i="" enter="" a="" date="" into="" a2="" it="" doesn’t="" change="" color.

Please help.</workday($d$2,-60)></workday(d2,-30)),"yellow",if(a2></workday(d2,-60),"green",if(and(a2>
 
Last edited:

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
Hmm.. I must not be way off base because just for giggles I typed in false in cell A2 and changed the conditional formatting formula to ="false" and it still didn't change color.
 
Upvote 0

Forum statistics

Threads
1,223,896
Messages
6,175,262
Members
452,627
Latest member
KitkatToby

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
Back
Top