# 2007 Excel Conditioning question

October 24, 2011 at 16:05:49
Specs: Windows 7
 I have a 07 excel question that is killing me. I have a training spreadsheet that will have multiple dates inputted into it; the due dates will be 2 yrs after the initial date of training (which has a SUM formula in the cell). What I am trying to accomplish is to have the due date highlight green until it is within 60 days of the due date, and then I want it to highlight yellow and when it is within 30 days of the due date I want it to highlight red. Any and all assistance would be greatly appreciated.Mitch

See More: 2007 Excel Conditioning question

#1
October 24, 2011 at 17:23:43
 See if this works for you:This is for Excel 2007There 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 = Red60 Days = Yellow90 Days = GreenFirst 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 OK10) Click OKSecond 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 OK10) Click OKThird 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 OK10) Click OKMIKEhttp://www.skeptic.com/

Report •

#2
October 24, 2011 at 18:14:34
 FYI...All of the above rules can be shortened to this format:=TODAY()-90<=G4Using a complete IF statement is not required since the shortened version will return either TRUE or FALSE, which is all that Conditional Formatting requires.Try it in a cell to see it for yourself.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

#3
October 25, 2011 at 08:04:37
 First, thank you both for the quick replies......I tried both ways, once I put in a conditional format in my range they all turned green, then yellow, then red. I changed the initial dates, and my due dates stayed red. Did I do something wrong ??

Report •

Related Solutions

#4
October 25, 2011 at 09:13:37
 Did I do something wrong ??I don't know, I can't see your data from where I'm sitting....Post a sample of your spreadsheet, but first READ THIS:http://www.computing.net/howtos/sho...MIKEhttp://www.skeptic.com/

Report •

#5
October 25, 2011 at 10:14:38
 see if this works....``` XMX/DFU 30-Dec-2010 30-Dec-2012 451P 9-Apr-2010 9-Apr-2012 GPS 9-Apr-2010 9-Apr-2012 ```the second date is the due date (24 months from the initial date) it has a sum function in the cell

Report •

#6
October 25, 2011 at 12:43:34
 ok, so this is what I have been working on......the cf will be in cell B4 - B25=B3 (cell where initial training date is located) >today()-671 (should be yellow when today's date is 60 days before the due date)=B3<=today()-701 (should be red if todays date is 30 days before the due date)=B3>today()-670 (should be green if todays date is within the initial training date and before 60 days of due date)what I can not figure out is if my formulas are wrong or I have color blindness. My practice dates are not changing colors are not matching up to my expected due dates.

Report •

#7
October 25, 2011 at 12:44:10
 OK, try these, I'm adding one more:=IF((C1-TODAY())>91,TRUE,FALSE) < NO Formatting Color.=IF((C1-TODAY())<=30,TRUE,FALSE) < Format RED=IF((C1-TODAY())<=60,TRUE,FALSE) < Format YELLOW=IF(C1-TODAY())<=90,TRUE,FALSE) < Format GREENMIKE

Report •

#8
October 25, 2011 at 12:50:03
 why am I putting C1 as my cell if the initial training date is located in B3 and the due date will be in C3

Report •

#9
October 25, 2011 at 12:56:46
 I inputted those formulas and no matter what practice date I input, my due date cell stays greenI really do appreciate all the help you are giving me, I am learning as I go.

Report •

#10
October 25, 2011 at 13:08:44
 why am I putting C1 as my cellYou can use any cells you please,I used C1 thru C3 because I used a copy of your data, so on my spread sheet it looks like this:``` A B C 1) XMX/DFU 30-Dec-2010 30-Dec-2012 2) 451P 9-Apr-2010 9-Apr-2012 3) GPS 9-Apr-2010 9-Apr-2012 ```If you need it in B4-B25 just change the formula to reflect that:1) Select your cell or Range of Cells, B4 - B252) 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((B4-TODAY())>91,TRUE,FALSE)Repeat for the rest of the formulas.On your first Due Date ( 30-Dec-2012 ):you should see the first change On Oct 1, 2012 it should turn GreenOn Oct 31, 2012 it should go Yellow andOn November 30, 2012 it should go Red.I take it, that the Due Date is computed using the date in cell B1 on my sheet, correct?So when you change B1, C1 should update to a new Due Date.MIKEhttp://www.skeptic.com/

Report •

#11
October 25, 2011 at 13:27:52
 You do realize that all 3 formulas have to be applied to the cells at the same time, right?When you open Manage Rules you should see all three rules listed. If you don't, then only the Rule(s) you see listed will be used to Conditionally Format the cell.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

#12
October 25, 2011 at 14:14:46
 ok, so the formula I inputted is as follows:=IF((C1-TODAY())>91,TRUE,FALSE) -- No color format=IF((C1-TODAY())<=30,TRUE,FALSE) -- Format red=IF((C1-TODAY())<=60,TRUE,FALSE) -- Format Yellow=IF((C1-TODAY())<=90,TRUE,FALSE) -- format greenin that order, you are correct in saying that if I put an initial date in B3 (my spreadsheet) the due date in C3 will automatically update for a due date 2 yrs later.now when I put the formulas in, the due date cell will change to the color of the last formula I inputted, so in this case, the green was the last formula and I put an initial date of 2000 the due date is green with 2002.

Report •

#13
October 25, 2011 at 14:49:44
 now when I put the formulas in, the due date cell will change to the color of the last formula I inputted, so in this case, the green was the last formula and I put an initial date of 2000 the due date is green with 2002.Lost me....Try this, enter the below data.Apply the CF formulas I posted, to C1 - C3``` A B C 1) XMX/DFU 23-Jan-10 23-Jan-2012 2) 451P 24-Dec-09 24-Dec-2011 3) GPS 24-Nov-09 24-Nov-2011 ```You should get: cell C1 Greencell C2 Yellowcell C3 Red MIKEhttp://www.skeptic.com/

Report •

#14
October 25, 2011 at 15:08:06
 is there a way I can upload a spreadhseet on here to show you what I am talking about? I did just what you said in a new spreadsheet, and again, as I am inputting a new rule in, whatever color I am formatting with, is the color the cell changes to, and now all my cells are green since that is the last rule I inputted.

Report •

#15
October 25, 2011 at 15:13:03

Report •

#16
October 25, 2011 at 15:17:47
 Good call, got my head around this excel problem, was not thinking about the consequences.

Report •

#17
October 25, 2011 at 15:19:32
 Is my examples a fair representation of what your try to do?As best as I can tell, Column B is the Date of Last TrainingColumn C is the Next-Training Date, which is 2 years from the Last Training Date.You compute that Next Training Date by adding two years to the Date of Last Training. C1 = B1 + 2 YearsIf not, explain where it is wrong, and post an example of what it is your doing.Using Column Headings and explain each step in your process.EDIT ADDED:Also, try this, highlight cells C1 - C3On the Ribbon: Select Conditional Formatting Select Manage Rules, ( down at the bottom of the dropdown list.)Tell me what you see.MIKE

Report •

#18
October 25, 2011 at 15:28:21
 your representation is correct, Topic is listed in Col Adate of training is Col B (starting in Cell B1)Due date is Col C (=SUM(B1+730)Then I follow your directions in the order that you place them in your reply. Highlighting cells C1 - C3=IF((C1-TODAY())>91,TRUE,FALSE) -- No color format=IF((C1-TODAY())<=30,TRUE,FALSE) -- Format red=IF((C1-TODAY())<=60,TRUE,FALSE) -- Format Yellow=IF((C1-TODAY())<=90,TRUE,FALSE) -- format greenwith each format I hit ok, the cells change to the color I just entered as a rule. When I place practice dates in B1, my C1 cell does not change color.

Report •

#19
October 25, 2011 at 16:13:05
 I think what is confusing you is the =TODAY() function.``` A B C 1) XMX/DFU 23-Jan-10 23-Jan-2012 2) 451P 24-Dec-09 24-Dec-2011 3) GPS 24-Nov-09 24-Nov-2011 ```Using the above data:If you input the Dates from Column B and then in column C, using your formula: =SUM(B1+730)you should get the dates as shown above.Now apply CF using the four formulas to cells C1 - C3you should get: Cell C1 GreenCell C2 YellowCell C3 RedIf you do not, then something is amiss.Explain, step by step how you are applying the CF. MIKEhttp://www.skeptic.com/

Report •

#20
October 25, 2011 at 16:29:44
 so I opened a new spreadsheet all together, I placed the above information just as you have it, I highlight cells C1-C3.Click on cf, scroll to new rule click on use a formula to determine which cells to formatin the formula bar, I input the first formula no color, and cont with the other three with red, yellow and green in that order as my format colors.When I input the formula for red, you were right, cell C3 turned red, yellow formula turned cell C2 & C3 yellow, and when I inputted the green formula, all three cells turned green. so question, even though I highlighted all three cells prior to doing the CF, should I unhighlight and do each cell on it own with all three formulas?? (that will take forever........)

Report •

#21
October 25, 2011 at 17:26:08
 should I unhighlight and do each cell on it own with all three formulas?? No, selecting your range is correct.Try this again, highlight cells C1 - C3On the Ribbon: Select Conditional FormattingSelect Manage Rules, Tell me what you see.What is the sequence of the formulas?MIKEhttp://www.skeptic.com/

Report •

#22
October 25, 2011 at 18:13:25
 The sequence of formulas in the Manage Rules window should be:Top Row: NO Formatting Color Second: Format REDThird: Format YELLOWBottom: Format GREENIf they are not in this sequence, use the small up/down arrows to get them in this order.MIKEhttp://www.skeptic.com/

Report •

#23
October 26, 2011 at 08:41:25
 That worked PERFERCTLY.........now if I have a spreadsheet of dates, can I just copy cell C1 to all my dates ????

Report •

#24
October 26, 2011 at 08:54:46
 can I just copy cell C1 to all my dates ????Lost me again. Please explain.MIKEhttp://www.skeptic.com/

Report •

#25
October 26, 2011 at 09:13:40
 on the practice sheet, we highlighted the cells C1-C3 and inputted the formulas, can I just copy those cells for the rest of my sheet.Ex, for one person, I have 43 training topics, and I have 20 personnel I have to track. Can I copy the cell and paste into 20 Due date columns?

Report •

#26
October 26, 2011 at 09:52:57
 You should be able to do a: Copy - Paste Special - FORMATSJust make sure you don't also copy the other data along with it.If needed you can just recreate the formulas on the new sheet.You have the basic formula, all you need do is modify the cell rangeto suit your needs.MIKEhttp://www.skeptic.com/

Report •

#27
October 26, 2011 at 10:01:02
 that worked well for me, but now I am stumped again.......I looked at the practice sheet and that is working correctly, three cells, three dates, three colors.for my training spreadsheet, same format, no color, then red, yellow, green. Same formulas, but I am only getting the red and yellow showing up. I am thinking that maybe green really is not that important. : ) I want to thank you for all your time and assistance, I would let anybody to asks, to check out this site if they need help.Mitch

Report •

#28
October 26, 2011 at 10:09:16
 Did you apply the Copy/Paste/Formats to all of your target cells?Do you have an understanding of what the formulas are doing?Do you know how Excel stores Dates?Conditional Formatting only executed on a TRUE statement.So if any of the formulas return FALSE, no CF.That is also why the colors have to be in a specific order,because we are cascading down the list until we get to aTRUE statement, which kicks of the CF.MIKEhttp://www.skeptic.com/

Report •

#29
October 26, 2011 at 10:25:38
 What I did was I went into manage rules, under the applies to: I went back to my spreadsheet, hit shift and ctrl through out the sheet to make sure that the rules will cover only the due date columns (ie..D3:D25,H3:H25 etc) I did this for all the rules.For a new spreadsheet, I did do the copy/Paste specials/formats.I do understand the basic of the formulas, which is why I am confused. for our cascade, we have no color, red, yellow, and green. as long as we are not with-in 30 or 60 days of our due date (red and yellow), the cell rule should go to the green format and turn the cell green Just like in the practice sheet right??

Report •

#30
October 26, 2011 at 10:53:20
 Not sure what it is your doing, but:It should work as:91 days or greater No color30 days or less Red60 days or less Yellow90 days or less GreenAll date greater then 91 days No ColorAll the dates between 90 - 61 should be GreenAll the dates between 60 - 31 should be YellowAll the dates between 30 - 0 should be RedIf all of your Due Dates are 2 years in the future, then they should have no color.Why do you have Two columns of Due Dates? .D3:D25,H3:H25?Lets try to get it working for One columnClear all the CF from your sheets,Select CF off the RibbonSelect Clear RulesSelect Clear Rules from Entire SheetNow: 1) Select your cell or Range of Cells, D3:D25 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 first formula 6) Click on the Format button 7) Select the Fill Tab 8) Select color 9) Click OK10) Click OKRepeat for the next 3 formulasYou should also understand that since we are using the =TODAY() function,every calculation has to relate to Todays Date. Just changing cell values can be tricky and may not get you what you think it will.MIKEhttp://www.skeptic.com/

Report •

#31
October 26, 2011 at 12:21:47
 Just a suggestion:If you are trying to find the correct formulas to conditionally format cells, try putting the formulas in cells before trying them in Conditional Formatting.Once they return TRUE for the conditions you are trying to test for, you should be able to Copy/Paste them into the Rules fields in Conditional Formatting.This method allows you test the formulas without have to go back and forth with the Conditional Formatting wizard.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •