How to use conditional formatting in excel?

April 14, 2011 at 07:22:36
Specs: Windows Vista
How to use conditional formatting in excel to project an anual inspection 90, 60, and 30 days using green, yellow and red to alert me prior to the due date .

See More: How to use conditional formatting in excel?

Report •

#1
April 14, 2011 at 15:31:33

Report •

#2
April 15, 2011 at 06:47:57
That’s the thread that I got all the formulas from and they all work great individually, but Im trying to get it all into one cell rather than 3 like Ryan. Is that possible? I would like the cell to change to different colors to alert me when its 90,60 and 30 days prior to the due date. It’s pretty much the same issue that Ryan had but I’m trying to keep my excel sheet as small as possible.

The first formula projects the inspection date out a year

=DATE(YEAR(F6),MONTH(F6)+12,DAY(F6))

Then these are the ones you posted, I changed the locations for the cells I needed.

=IF((F6-90)<=G6,TRUE,FALSE)
=IF((F6-60)<=G6,TRUE,FALSE)
=IF((F6-30)<=G6,TRUE,FALSE)


Report •

#3
April 15, 2011 at 12:08:38
Just put all three formulas in the Conditional Formatting.
After you do the first one, click ADD and add
the second, after the second, click ADD and do the third.

If your using 2003 then three is all you can do.
If your using 2007 then you can do a lot more.


I would like the cell to change to different colors to alert me when its 90,60 and 30 days prior to the due date.

Where is the Due Date, in what cell?
What cell do you want to highlight?

Post a small sample of your spreadsheet, but first read this How-To to see how to post data on the fourm:
http://www.computing.net/howtos/sho...

MIKE

http://www.skeptic.com/


Report •

Related Solutions

#4
April 20, 2011 at 06:14:21
Where is the Due Date, in what cell?
What cell do you want to highlight?
The due date is in cell G which is the same cell I need to change colors if thats possible.

SUPERVISOR       TRAINING   DUE DATE
      e	           f	      g
4 john doe	2/3/2011	2/3/2012
5 jane doe	2/4/2011	2/4/2012
6 timmi doe	2/5/2011	2/5/2012
7 sally doe	 	 #VALUE!


Report •

#5
April 20, 2011 at 13:12:48
See if this works for you:

This is for Excel 2007
There are Three separate formulas,
and they must be in the correct order,
so after your done entering all the formulas
they should be, in the following color order:

30 Days = Red
60 Days = Yellow
90 Days = Green

First Formula:

1) Select your cell or Range of Cells, G4 - G99
2) On the ribbon click Conditional Formatting
3) Click on New Rules, it’s near the bottom of the dialog box.
4) Click Use Formula to determine which cells to format.

5) Enter the formula: =IF((TODAY()-90)<=G4,TRUE,FALSE)

6) Click on the Format button
7) Select the Fill Tab
8) Select a Green color
9) Click OK
10) Click OK

Second formula:

1) Select your cell or Range of Cells, should be the same as above.
2) On the ribbon click Conditional Formatting
3) Click on New Rules, it’s near the bottom of the dialog box.
4) Click Use Formula to determine which cells to format.

5) Enter the formula: =IF((TODAY()-60)<=G4,TRUE,FALSE)

6) Click on the Format button
7) Select the Fill Tab
8) Select a Yellow color
9) Click OK
10) Click OK

Third Formula:

1) Select your cell or Range of Cells, should be the same as above
2) On the ribbon click Conditional Formatting
3) Click on New Rules, it’s near the bottom of the dialog box.
4) Click Use Formula to determine which cells to format.

5) Enter the formula: =IF((TODAY()-30)<=G4,TRUE,FALSE)

6) Click on the Format button
7) Select the Fill Tab
8) Select a Red color
9) Click OK
10) Click OK

When the Date in Cell G4 is 90 days before Today, it will turn Green
When the Date in Cell G4 is 60 days before Today, it will turn Yellow
When the Date in Cell G4 is 30 days before Today, it will turn Red

MIKE

http://www.skeptic.com/


Report •

Ask Question