Conditional Coloring

**USMCavi** · 01-27-2011, 02:44 PM

I run a workcenter where we do a LOT of traing and I need to keep track of expiration dates. I have found similar threads but none that quite have what I need. I have a spreadsheet with cells that have the date something expires in it. Everyone has different dates. If I have to format each cell individually I will no problem. I would like to set up the cell so that the icon (I like the traffic lights) changes from green to yellow when it is 30 days away from expiration and red when it is 14 days away. Can anyone help me? I really appreciate it!

Glen

**pMzQ** · 01-27-2011, 03:10 PM

Hy USMCavi,

What you could do is add an extra column with the same date (so just a copy). Change the cell properties of these cells to text format.

Also setup an extra cell with =Now(). Also change the cell property of this cell to text.

They will then turn to numbers. This number wil be the days from somewhere in 1900 or so.

What you can do on this column is an extra column with an if statement:
if((New_Date_Cell-NOW_Cell)<30,1,if((New_Date_Cell-NOW_Cell)<14,2,0))

The cell with this formula will return 1 if there is less then 30 days left. 2 when there is less then 14 days left. Else it returns 0.

Then on this cell you put your conditional formatting like when cell value = 1 cell background = yellow, if cell value = 2 cell background = red and if cell value = 0 cell background = green...

Hope this helps a bit. Didn't test this but something like it could work...

**arthurbr** · 01-27-2011, 03:51 PM

As you mentioned traffic lights, I suppose you are using 2007 ?
Please adapt your profile version accordingly - Thx

**USMCavi** · 01-27-2011, 06:02 PM

Updated profile to 2007.

**USMCavi** · 01-27-2011, 06:57 PM

pMzQ,
Thanks for helping this far, it's almost working. However, even if it is 12 days out it still registers as <30 so it will ony display the values of 1 or 0. How can I modify this formula to show the 2 under 14 days?

**pMzQ** · 02-01-2011, 03:25 AM

Hy USMCavi,

Sorry the if statement was not optimal.

New version:
if(AND((New_Date_Cell-NOW_Cell)<30,(New_Date_Cell-NOW_Cell)>14,1,if((New_Date_Cell-NOW_Cell)<15,2,0))

I think this could do the trick...