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 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 = 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 OK

10) 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 OK

10) 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 OK

10) Click OKMIKE

FYI... All of the above rules can be shortened to this format:

=TODAY()-90<=G4

Using 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.

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 ??

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...

MIKE

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-2012the second date is the due date (24 months from the initial date) it has a sum function in the cell

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.

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 GREEN

MIKE

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

I inputted those formulas and no matter what practice date I input, my due date cell stays green I really do appreciate all the help you are giving me, I am learning as I go.

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-2012If you need it in B4-B25 just change the formula to reflect that:

1) Select your cell or Range of Cells, B4 - B25

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((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 Green

On Oct 31, 2012 it should go Yellow and

On 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.

MIKE

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.

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.

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 - C3A 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-2011You should get:

cell C1 Green

cell C2 Yellow

cell C3 RedMIKE

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.

Remove your email address, unless you really enjoy SPAM. Never post your email on an open forum.

MIKE

Good call, got my head around this excel problem, was not thinking about the consequences.

Is my examples a fair representation of what your try to do? As best as I can tell,

Column B is theDate of Last Training

Column C is theNext-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 Years

If 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 - C3

On the Ribbon: Select Conditional Formatting

Select Manage Rules, ( down at the bottom of the dropdown list.)Tell me what you see.

MIKE

your representation is correct, Topic is listed in Col A

date 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.

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-2011Using 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 - C3

you should get:

Cell C1 Green

Cell C2 Yellow

Cell C3 RedIf you do not, then something is amiss.

Explain, step by step how you are applying the CF.

MIKE

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 format

in 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........)

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 - C3

On the Ribbon: Select Conditional Formatting

Select Manage Rules,Tell me what you see.

What is the sequence of the formulas?MIKE

The sequence of formulas in the Manage Rules window should be: Top Row: NO Formatting Color

Second: Format RED

Third: Format YELLOW

Bottom: Format GREEN

If they are not in this sequence,

use the small up/down arrows to get them in this order.MIKE

That worked PERFERCTLY.........now if I have a spreadsheet of dates, can I just copy cell C1 to all my dates ????

can I just copy cell C1 to all my dates ????Lost me again.

Please explain.MIKE

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?

You should be able to do a: Copy - Paste Special - FORMATS

Just 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 range

to suit your needs.MIKE

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

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 a

TRUE statement, which kicks of the CF.MIKE

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??

Not sure what it is your doing, but: It should work as:

91 days or greater No color

30 days or less Red

60 days or less Yellow

90 days or less GreenAll date greater then 91 days No Color

All the dates between 90 - 61 should be Green

All the dates between 60 - 31 should be Yellow

All 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 column

Clear all the CF from your sheets,

Select CF off the Ribbon

Select Clear Rules

Select 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 OK

10) Click OKRepeat for the next 3 formulas

You 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.MIKE

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.

Ask Your Question

Weekly Poll

Do you think Monopoly should update its pieces?

Discuss in The Lounge

Poll History