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

Report •

#1
October 24, 2011 at 17:23:43
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

MIKE

http://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<=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.


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

MIKE

http://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 GREEN

MIKE

http://www.skeptic.com/


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 green

I 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 cell

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

http://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 green

in 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 Green
cell C2 Yellow
cell C3 Red

MIKE

http://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
Remove your email address, unless you really enjoy SPAM.

Never post your email on an open forum.

MIKE

http://www.skeptic.com/


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 Training
Column 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 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

http://www.skeptic.com/


Report •

#18
October 25, 2011 at 15:28:21
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 green

with 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 - C3
you should get:
Cell C1 Green
Cell C2 Yellow
Cell C3 Red

If you do not, then something is amiss.

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

MIKE

http://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 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........)


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 - C3
On the Ribbon: Select Conditional Formatting
Select Manage Rules,

Tell me what you see.
What is the sequence of the formulas?

MIKE

http://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 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

http://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.

MIKE

http://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 - 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

http://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 a
TRUE statement, which kicks of the CF.

MIKE

http://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 color
30 days or less Red
60 days or less Yellow
90 days or less Green

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

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

Now:

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 OK

Repeat 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

http://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 •

Ask Question