Expert: Bill Hermanson - 10/28/2009

Question
I am using Excell 2007 and have a spreadsheet with dates completed. I want the cell to be green if training is current, yellow if it's within 60 days and red if it's within 30 days. I read one of you posts with the same subject:Everyone else wants this also, so Excel has a 'function' called CONDITIONAL FORMATTING that does exactly what you want.  You MUST know about this already since you referred to an "icon set" which is only available in the Conditional Formatting section of Excel 2007.

  So all you need to do is use the FORMULA mode of Conditional Formatting to compare the date in the cell with TODAY().  Depending on the results, the cell will change colors accordingly.  Your formula will be something like:

=(cell-with-date)<TODAY() to turn RED,

=(cell-with-date)<=TODAY()+30  to turn yellow

=(cell-with-date)>TODAY()+30 to turn green

I open up cf and use "new rule w/3 color scale" I use the formula for all three and type in for ex, =$D$2<$I$2--CELL I2 has the =TODAY() formula. This isn't working. Is there anyway to break this down for me so I can get it to work? I would greatly appreciate it. Thank you, MSgt Robert Campos

Answer
Robert,

   Before I can help you properly, I need to know more about how 'it doesn't work'.  What exactly is wrong?  

   My GUESS is that you are trying to format a number of cells, not just one... although your question refers repeatedly to THE cell, perhaps implying a single cell instead of a column.  If my GUESS is correct, I;'d say off hand that your formula is using an Absolute Reference to the Time cell ($D$2) so all the cells have the same formula, and thus they all format the same and don't seem to 'listen to' the contents of the proper cells.  If this seems like your difficulty, then what you'd want is D2, not $D$2.

 Let me know additional details and I surely can help you.

   >>> Bill