Conditional Formatting?

[allieoop78]

My boss wants to color code dates based on certain date ranges and have them update automatically when the document is opened. (This is to show whether an employee's qualification is current, due to expire within 6 months, due to expire within 1 month, and expired.) Is there any way to do this? I've been experimenting with the Conditional Formatting in the Format menu, but I haven't been successful.

The criteria I've been using is:

(Within 6 mos)
Condition 1: Cell value is between ="Today()+31" and ="Today()+180"

(Within 1 mo)
Condition 2: Cell value is between ="Today()"+1 and ="Today()+30"

(Expired)
Condition 3: Cell value is less than or equal to ="Today()"


The dates listed in each cell are in the following format: 8/1/2008

All the cells are showing as expired.

Is there another way to do this or am I entering the formulas or dates incorrectly?

Thank you!

[Paul]

Hi allieoop,

Conditional Formatting can sometimes be tricky. Try this:

Select one of the cells with the date you want to check (let's say C1 as an example), then:

1. Click Format -> Conditional Formatting...
2. For Condition1, select 'Formula Is' and set the formula to: =AND(C1>=TODAY()+31,C1<=TODAY()+180)
3. Click the 'Format' button and set the background (pattern tab) color to green (and click OK)
4. Click the 'Add' button
5. For Condition2, select 'Formula Is' and set the formula to: =AND(C1>=TODAY(),C1<=TODAY()+30)
6. Click the 'Format' button and set the background color to Yellow, click OK
7. Click the 'Add' button
8. For Condition3, select 'Formula Is' and set the formula to: =C1<TODAY()
9. Click the 'Format' button again, yada yada..

After clicking OK to close the Conditional Formatting window, your cell should now be colored appropriately. You can then copy that cell, select other cells and use Edit -> PasteSpecial -> Formats -> OK.

[olasa]

Take away the " and it should work (I just tried it).
//Ola

[shg]

Since the first active conditional format dictates, you can simplify somewhat if you put them in the right order, and you can also have four possibilities.

Set three format conditions:

• Cell value is less than or equal to =TODAY() (Red, expired)
  
• Cell value is less than or equal to =TODAY() + 30 (Yellow, one month)
  
• Cell value is less than or equal to =TODAY() + 180 (Green, six months)

Then format the cell Blue (> six months)

[allieoop78]

Thank you for your suggestions!

I tried both but haven't been successful with either. Paul yours seems to want to work, but not quite right.

I copied and pasted relevant columns here. As you can see colors have been assigned to the cells, but it seems quite random. What have I done wrong? :-) LOL

Thanks again!

[shg]

Try mine ...?

[Paul]

The issue with the spreadsheet you uploaded is that the conditional formatting formula for cell A1 references cell A65534. Not sure how that happened.

Select your range of cells, e.g. A1:D200, then go into conditional formatting and setup the formulas as shown but reference A1 as the cell. (Reference A1 because it is the top-left cell in the range selected.)

That should fix it.

Otherwise, try shg's or olasa's suggestions using your original setup, adjusting according to their posts.

[daddylonglegs]

Try mine ...?

But won't that lead to blank cells being formatted?

Try this

select column A
Format > conditional formatting > formula is

condition 1
=(A1<TODAY())*(A1<>"")
format red

condition2
=(A1<=TODAY()+30)*(A1<>"")
format yellow

condition 3
=(A1<=TODAY()+180)*(A1<>"")
format green

Note: If your dates are really as posted in your example, i.e. nearly all 1st of the month does it make sense to look at specific dates or are you just interested in the month?

[olasa]

You were very close (see encl. file). The copy/paste must become @'d up.

I entered the Conditional Formatting formula in cell A1.
Then I copied that cell, marked all other cells and used Paste special... and ticked Formats.
That's it.

I also added ISNUMBER() to the last condition.
=AND(A1<TODAY(),ISNUMBER(A1)) --> cells with no number will be colorless.
But you can remove that if you want.

Hope this helped
Ola