conditional formatting for 3-color scales with formulas

Nothnless

Board Regular
Joined
Apr 28, 2016
Messages
92
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:

Nothnless

Board Regular
Joined
Apr 28, 2016
Messages
92
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.
 

Forum statistics

Threads
1,085,815
Messages
5,386,051
Members
401,980
Latest member
chaithanyakrishnagck

Some videos you may like

This Week's Hot Topics

Top