# 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

#1
March 15, 2011 at 15:09:47
 See if this thread helps:http://www.computing.net/answers/of...MIKEhttp://www.skeptic.com/

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 INSPECTTHE RANGE BETWEEN 59 DAYS BEFORE TO 89 DAYSAND THE 90 DAY AFTER AND BEYOND MARKIM 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 BEFORESo:From 89 days before Today to 60 days before Today = GreenFrom 59 days before Today to 1 day before Today = YellowFrom Today to 90 days after Today = RedIt 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...MIKEhttp://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 DB= 90 days before inspection is dueC=Next annual inspection dueD=90 days after inspection is dueA=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) = GreenIf Column B is 60 Days before Next Inspection ( Column C - 21-Jun-11) = YellowHow and why are the dates being entered?Why all the columns?Is there more to this then what your showing?MIKEhttp://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.MIKEhttp://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 ColorGreen YellowFirst Formula:1) Select your cell B12) On the ribbon click Conditional Formatting3) 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 button7) Select the Fill Tab8) Select a Green color9) Click OK10) Click OKSecond formula:1) Select your cell B12) On the ribbon click Conditional Formatting3) 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 button7) Select the Fill Tab8) Select a Yellow color9) Click OK10) Click OKThird Formula:1) Select your cell B12) On the ribbon click Conditional Formatting3) 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

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.examplecolumns A B C D last annual exam 90 days before due next annual exam 90 days over duecolumn 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.MIKEhttp://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, DSelect the individual rows which are of concern, IE Row 2, 3, 4, 5, 6It 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 C1So with 21-Jun-11 in cell C1Cell B1 will turn Green beginning 23-Mar-11 Turn Cell B1 Yellow 60 days before the Date in Cell C1So with 21-June-11 in cell C1Cell B1 will turn Yellow beginning 22-Apr-11Before 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. MIKEhttp://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?MIKEhttp://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 ColorRedYellowGreenFirst Formula:1) Select your cell B12) On the ribbon click Conditional Formatting3) 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 button7) Select the Fill Tab8) Select a Green color9) Click OK10) Click OKSecond formula:1) Select your cell B12) On the ribbon click Conditional Formatting3) 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 button7) Select the Fill Tab8) Select a Yellow color9) Click OK10) Click OKThird Formula:1) Select your cell B12) On the ribbon click Conditional Formatting3) 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 button7) Select the Fill Tab8) Select a Red color9) Click OK10) Click OKFourth Formula:1) Select your cell B12) On the ribbon click Conditional Formatting3) 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 button7) Select the Fill Tab8) Select NO Fill color9) Click OK10) Click OKWhen the date in cell B1 is greater than or equal to the date in cell C1, then cell B1 will turn RedWhen the date in cell B1 is 91 days or more before the date in cell C1 it will turn to no color.MIKE

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 •