excel 2007 and conditional formatting

March 15, 2011 at 11:58:57
Specs: Windows Vista
hello,

I have an excel spreadsheet that deals with dates and expiring dates that need to all work together. I have four columns of dates. 1 column is last annual exam, 2nd is 90 days before due date, 3rd is next annual due date, and 4th is 90 days after exam is due. what i need is to has the 2nd, 3rd and 4th column all work off of the dates in column 1 and change from green, yellow and red depending on how close or past due they are. Thank you for your help.


See More: excel 2007 and conditional formatting

Report •


#1
March 15, 2011 at 15:09:47

Report •

#2
March 16, 2011 at 05:39:13
MIKE,

WHAT WOULD BE THE FORMULA IF I WANTED THE GREEN COLUMN TO BE 60 DAYS BEFORE AND THEN THE YELLOW COLUMN TO THE RANGE BETWEEN THE 60 DAYS BEFORE AND 89 DAYS BEFORE THE 90 DAYS AFTER MARK.

PRETTY MUCH I HAVE 3 COLUMNS:

60DAYS BEFORE THE FIRST INSPECT

THE RANGE BETWEEN 59 DAYS BEFORE TO 89 DAYS

AND THE 90 DAY AFTER AND BEYOND MARK

IM GETTING CONFUSED WITH THE 3 COLUMNS AND THEY ARE ALL BASED OFF A 4TH COLUMN OF DATES THAT ARE CONSTANTLY UPDATED MANUALLY.

THANK YOU FOR YOUR RESPONSE.



Report •

#3
March 16, 2011 at 06:58:52
I WANTED THE GREEN COLUMN TO BE 60 DAYS BEFORE AND THEN THE YELLOW COLUMN TO THE RANGE BETWEEN THE 60 DAYS BEFORE AND 89 DAYS BEFORE

So:
From 89 days before Today to 60 days before Today = Green
From 59 days before Today to 1 day before Today = Yellow
From Today to 90 days after Today = Red

It would help if you post a bit of your table, it need not be a lot several lines will be enough, read this How-To to learn how to post your data in the forums:
http://www.computing.net/howtos/sho...

MIKE

http://www.skeptic.com/


Report •

Related Solutions

#4
March 16, 2011 at 07:14:14
4-Jun-10           23-Mar-11	        21-Jun-11	19-Sep-11

column A B C D

B= 90 days before inspection is due
C=Next annual inspection due
D=90 days after inspection is due

A=the column of dates that BCD are based off of


Report •

#5
March 16, 2011 at 08:12:06
Not sure what your doing, but from my understanding, column A is not necessary as it is column C, the Next Annual Inspection that your interested in.

So would it not be:

If Column B is 90 Days before Next Inspection ( Column C - 21-Jun-11) = Green
If Column B is 60 Days before Next Inspection ( Column C - 21-Jun-11) = Yellow

How and why are the dates being entered?
Why all the columns?
Is there more to this then what your showing?

MIKE

http://www.skeptic.com/


Report •

#6
March 16, 2011 at 08:50:01
yea its a huge spreadsheet, im in the military and we do inspections on vessels in the water and they want it all color coordinated so when something is updated the other dates change as well as their colors.

Report •

#7
March 16, 2011 at 08:52:38
pretty much column A is last annual exam and those dates are entered in manually by the inspector or whoever is incharge of updating the sheet.

Report •

#8
March 16, 2011 at 10:02:22
Sorry, I'm not getting it, I'm a bit "dense" today.

When is each date entered?

When is the Date in Column A is entered, does column C automatically calculate the date for the next yearly inspection or do you enter the date by hand?

When is the Date in Column B entered? Is it updated daily?
When is the Date in Column D entered?

Give me a quick step by step run through of your procedure.

MIKE

http://www.skeptic.com/


Report •

#9
March 16, 2011 at 11:08:13
its hard to explain is there an email you have, i can send you the whole document and explain it easier that way if you want?

Report •

#10
March 16, 2011 at 11:49:15
That would kinda defeat the whole purpose of the forums, would it not?


Try these and see if you get what you need, if not come back we'll work on it some more.

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 order, :

No Color
Green
Yellow

First Formula:

1) Select your cell B1

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((C1-90)<=B1,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 B1

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((C1-60)<=B1,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 B1

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(OR((C1-91)>B1,C1<B1),TRUE,FALSE)

6) Click on the Format button

7) Select the Fill Tab

8) Select NO Fill color

9) Click OK

10) Click OK

When the date in cell B1 is greater than the date in cell C1, then cell B1 will turn back to no color.

MIKE

http://www.skeptic.com/


Report •

#11
March 16, 2011 at 12:12:11
ok ill try it thanks

Report •

#12
March 17, 2011 at 06:50:17
hey it wasnt working right. . . maybe this description might help better for you.

i have 4 columns with dates that expire and i need them to change from green, yellow and red for when they are not expired, to getting close, to overdue. how do i get it to where if one column of dates is 90 days or before the next annual inspection to turn green (this is based off one column of dates). Then when the other column thats listed as 90 days after (overdue) when the dates are 90 days or greater they need to turn red. and when any of those dates are in the middle lets say 30 days before the next inspection and 89 days before they are overdue should be yellow.

example

columns A B C D
last annual exam 90 days before due next annual exam 90 days over due


column A dates are entered in manually, column B dates change colors due to how close they are relative to column C. Column D dates change color to how close they are relative to C as well. Column C dates change color to how close they are relative to column A. i hope this helps


Report •

#13
March 17, 2011 at 11:59:23
Read the how to again:

http://www.computing.net/howtos/sho...

Post a sample of your spreadsheet, several rows and columns and include column headings if you use them.

I'll see what I do in the mean time.

MIKE

http://www.skeptic.com/


Report •

#14
March 17, 2011 at 12:28:28
it wont let me. . my spreadsheet has to much in it to fit in this box it gets all mixed up and if i resize it it gets all squashed together which makes it unreadable. oh well thanks for your help imma see if i can find someone i can email the spreadsheet to and they can actually look at it as a whole.

Report •

#15
March 17, 2011 at 12:48:07
Select the individual column which are of concern, IE Column A, B, C, D
Select the individual rows which are of concern, IE Row 2, 3, 4, 5, 6

It does not have to be the whole sheet, but it does have to be a fair representation of what your working with.

Using the one line of data that you supplied

        A          B          C          D
1) 4-Jun-10   23-Mar-11   21-Jun-11   19-Sep-11

The three formula I posted will:

Turn Cell B1 Green 90 days before the Date in Cell C1
So with 21-Jun-11 in cell C1
Cell B1 will turn Green beginning 23-Mar-11

Turn Cell B1 Yellow 60 days before the Date in Cell C1
So with 21-June-11 in cell C1
Cell B1 will turn Yellow beginning 22-Apr-11

Before 23-Mar-11 and after 21-Jun-11 the cell will turn back to no color.

At least that is what happens on my test sheet, with your one row of data.

hey it wasnt working right.

Does not really give a lot of info as to what went wrong.


MIKE

http://www.skeptic.com/


Report •

#16
March 17, 2011 at 12:58:37
well let me try your formulas again one more time and maybe i just fat fingered it somewhere. ill let ya know

Report •

#17
March 17, 2011 at 13:02:18
the no fill color is that mean for the red cause i was wanting red, yellow green?

Report •

#18
March 17, 2011 at 13:48:08
when the dates are 90 days or greater they need to turn red.

I thought that's what column D was for?

MIKE

http://www.skeptic.com/


Report •

#19
March 17, 2011 at 14:19:35
There are now FOUR separate formulas, and they must be in the correct order, so after your done entering all the formulas they should be, in order, :

No Color
Red
Yellow
Green

First Formula:

1) Select your cell B1

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((C1-90)<=B1,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 B1

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((C1-60)<=B1,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 B1

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(B1>=C1,TRUE,FALSE)

6) Click on the Format button

7) Select the Fill Tab

8) Select a Red color

9) Click OK

10) Click OK

Fourth Formula:

1) Select your cell B1

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(OR((C1-91)>B1),TRUE,FALSE)

6) Click on the Format button

7) Select the Fill Tab

8) Select NO Fill color

9) Click OK

10) Click OK

When the date in cell B1 is greater than or equal to the date in cell C1, then cell B1 will turn Red
When the date in cell B1 is 91 days or more before the date in cell C1 it will turn to no color.

MIKE

http://www.skeptic.com/


Report •

#20
March 21, 2011 at 07:14:12
hey i fat fingered your formulas but they worked for me thank you for all the help and ur patience. its greatly appreciated.

Report •

#21
April 13, 2011 at 09:55:33
Hey guys I am in the same boat that Ryan is. mmcconaghy is there any way to have the color changes happen on the same cell for ex. I have to do an annual inspection. In one cell, I put the date the initial inspection was done. In the second cell, I input a formula to project it a yr out is there a way to consolidate my 90, 60, 30 day inspection to reflect by color in one cell rather than 3 or 4 different columns

Report •

Ask Question