Conditional formatting dates?

[ReapeR]

I've been through all the questions in here and I'm still having difficulty
grasping this.

I want to conditional format dates that are 60 days, 30 days out and then
when they are overdue. I keep seeing everyone saying to change the formula
to "formula is", but I can't find that anywhere. All I can get to is the
drop down box (Excel 2007) from the CF tab. I go to new rule and get 5
options. (Format all cells..., Format only cells..., Format only top or
bottom..., etc. I've been through them and still no "formula is" option.
Any help would be greatly appreciated.

Just to clarify, I'm trying to color fill green when 60 days or more out,
yellow when 30 days or less out and then of course red, when actually overdue.

Thanks in advance for any help.

James

 

[Shane Devenshire]

Hi,

Assuming you already have the formulas

In 2007:
1. Highlight all the cells on the rows you want formatted
2. Choose Home, Conditional Formatting, New Rule
3. Choose Use a formula to determine which cell to format
4. In the Format values where this formula is true enter the following
formula:
enter your formula here.
5. Click the Format button and choose a format.
6. Click OK twice

 

[Shane Devenshire]

Hi,

Just in case you don't have the formulas, try something like this
#1 =$A1<TODAY()
#2 =$A1>=TODAY()+60
#3 =$A1>=30

You can go back into the Conditional formatting dialog box by either doing
New Rule or Manage Rules.

 

[ReapeR]

Nope, didn't work. I used the formulas you gave me, "=$A1<TODAY() red

=$A1>=TODAY()+60 green

=$$A1>=30 yellow

All I get is a red fill for everything I type in there. However, now that I
think about it, I might be asking for the wrong thing. Let me try to explain:

I have an excel sheet set up to track due dates for training classes. Most
of these are annual classes. I want to set it up so that when I put in the
current completion date, that a year later, at 60 days out, it turns green,
at 30 days out it turns yellow and finally, once it's overdue, red.

I think I was asking for something slightly different than that. However,
help is still needed.

Thanks,

james

 

[Fred Smith]

For Shane's third formula, use:
=$a1>=today()+30

When you say "didn't work", are you using the correct cell address? When you
don't tell us what cells you are using, we can only generalize, and tend to
use a1 as the example. I expect when you adjust this to fit your situation,
the formulas will work.

Regards,
Fred.

 

[ReapeR]

I was using a test sheet with A1, A2, B1 and B2.

Nothing worked other than red fill on everything.

Here's an example of what I'm trying to do:

A1: 1 Jan 09 (Green - More than 60 days out. 1 Jan 10)
B1: 29 Jun 08 (Yellow - Still within 30 days, tomorrow overdue)
A2: 1 Jun 08 ( Yellow - Within 30 days)
B2: 17 Feb 08 (Red - Overdue. 17 Feb 09)
I hope this explains it better.

Thanks again for all the help.

James

 

[Fred Smith]

We know what you're trying to do. It's a very common task that many people
use successfully.

Do you have Excel dates or text in your cells? What result do you get when
you enter =today()-a1?
Or are your cells red because you used 2008 rather than 2009?

Regards,
Fred.

 

[ReapeR]

Well, not really sure. Here's what I type in:
29 Jun 09

When I tab or move out of it, it reads:
29-Jun-09


but up top, in the dialog box???, it reads as:
06/29/2009

Hope that helps.

James

 

[Fred Smith]

From the information provided, you're using real Excel dates, so that's not
the problem.

As stated, it looks like you're getting red shading because you used dates
in 2008 rather than 2009.

Regards,
Fred.