Formatting cell based upon values w/in a selection

  • Thread starter Thread starter Carissa
  • Start date Start date
C

Carissa

Background: I have a spreadsheet which tracks 10+ training course dates for
100+ individuals. All dates have conditional formatting applied to turn red
if the date is <=TODAY(), yellow if btwn TODAY()+1 and TODAY()+30, and amber
if the date in the cell is btwn TODAY()+31 and TODAY()+60.

Dilemma: Column A, for each individual, needs to reflect their overall
status (preferrably using the stoplight icon set) in their training. If any
of their items are in red, Column A would show the red stoplight...if no
items are red but yellow or amber dates are present, Column A would show the
yellow stoplight...if none of any individual's dates are highlighted, a green
stoplight would appear in Column A.

I haven't found any items within the discussion group or through the Help
function that are identical to what I'm looking for...but have made attempts
to manipulate the formulas of what was offered with no results.

Any help would be HIGHLY appreciated!
 
You'll need to apply a conditional format to the A column based on TODAY() as
well.
Assume your other dates that you've conditionally formatted are in columns C:F

Column A conditional format
condition1
=MIN(C2:F2)<=TODAY()
condition2
=AND(MIN(C2:F2)>=TODAY()+1,MIN(C2:F2)<=TODAY()+30)
conidtion3
AND(MIN(C2:F2)>=TODAY()+31,MIN(C2:F2)<=TODAY()+60)
 
Back
Top