Conditional formatting dates?
 

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

Conditional formatting dates?
 

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
--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"ReapeR" wrote:

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

Conditional formatting dates?
 

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.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"ReapeR" wrote:

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

Conditional formatting dates?
 

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

"Shane Devenshire" wrote:

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.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"ReapeR" wrote:

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

Conditional formatting dates?
 

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" wrote in message
...
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

"Shane Devenshire" wrote:

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.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"ReapeR" wrote:

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

Conditional formatting dates?
 

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" wrote:

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" wrote in message
...
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

"Shane Devenshire" wrote:

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.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"ReapeR" wrote:

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

Conditional formatting dates?
 

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" wrote in message
...
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" wrote:

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" wrote in message
...
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

"Shane Devenshire" wrote:

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.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"ReapeR" wrote:

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

Conditional formatting dates?
 

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" wrote:

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" wrote in message
...
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" wrote:

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" wrote in message
...
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

"Shane Devenshire" wrote:

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.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"ReapeR" wrote:

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

Conditional formatting dates?
 

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.

"ReapeR" wrote in message
...
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" wrote:

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" wrote in message
...
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" wrote:

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" wrote in message
...
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

"Shane Devenshire" wrote:

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.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"ReapeR" wrote:

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