Highlight cell on certain dates

[Lise]

Hi

Wanting to highlight A3 if I3 is within 60 days from todays date - I have
read a couple of pointers that led me to write the following for three
conditional formats

1. =I3="""" Outcome wanted Blank Cell
2. =I3>=Today()-60 Outcome wanted White Background Black Font
3. =I3<=Today()-60 Outcome wanted Red Background Black Font

But they are not working what am I doing wrong please? should I have +'s
somewhere?

Thanks as always
Thanks

Lise

 

[Sheeloo]

First one should be
=I3=""
Excel puts extra " when you fist defirne it. Go back and remove the extra
quotes
othes are Ok... it is better if you Put () around today()- 60 to be clear

You may put the conditions in any cell first to see that they work... you
are Ok if you get TRUE...

You are applying the condition to A3, right?

 

[Sheeloo]

First one should be
=I3=""
Excel puts extra " when you fist defirne it. Go back and remove the extra
quotes
othes are Ok... it is better if you Put () around today()- 60 to be clear

You may put the conditions in any cell first to see that they work... you
are Ok if you get TRUE...

You are applying the condition to A3, right?

 

[Max]

With A3 as the active cell,
try these as the CF's "Formula Is"
Condition 1: =AND(ISNUMBER(I3),I3-TODAY()<=60)
Condition 2: =AND(ISNUMBER(I3),I3-TODAY()>60)
Format to taste.

Note that you should avoid overlapping conditions. When you craft it up, be
careful with the limits and the use of operators, eg: "<=" and ">" in the
above
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:300 Subscribers:70
xdemechanik

 

[Lise]

Thanks for that but its not working - yes I am applying to A3 - but as an
example dates between today and 60 days are staying white and dates for 2010
are highlighting read?

 

[Max]

With A3 as the active cell,
try these as the CF's "Formula Is"
Condition 1: =AND(ISNUMBER(I3),I3-TODAY()<=60)
Condition 2: =AND(ISNUMBER(I3),I3-TODAY()>60)
Format to taste.

Note that you should avoid overlapping conditions. When you craft it up, be
careful with the limits and the use of operators, eg: "<=" and ">" in the
above
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:300 Subscribers:70
xdemechanik

 

[Lise]

Thanks for that but its not working - yes I am applying to A3 - but as an
example dates between today and 60 days are staying white and dates for 2010
are highlighting read?

 

[Lise]

Thanks again - this is still not working continues to give me a highlighted
cell for dates in I3 that are 2010 and 2013 does highlighted some that are
dated between today and 60 days plus but not all - sorry to be awkward!

 

[Lise]

Thanks again - this is still not working continues to give me a highlighted
cell for dates in I3 that are 2010 and 2013 does highlighted some that are
dated between today and 60 days plus but not all - sorry to be awkward!

 

[Max]

Not very sure ..
but perhaps all that's needed is just this single CF condition:
=AND(ISNUMBER(I3),I3-TODAY()>0,I3-TODAY()<=60)

The above condition will trigger only if I3 contains a real date (ie a
number), and that date is a "future" date within 60 days from today's date
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:300 Subscribers:70
xdemechanik

 

[Max]

Not very sure ..
but perhaps all that's needed is just this single CF condition:
=AND(ISNUMBER(I3),I3-TODAY()>0,I3-TODAY()<=60)

The above condition will trigger only if I3 contains a real date (ie a
number), and that date is a "future" date within 60 days from today's date
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:300 Subscribers:70
xdemechanik

 

[Lise]

Thanks anyway Max - Have tried all suggestions and no luck. Most frustrating,
I'll keep fiddling and let you know if I suceed

 

[Lise]

Thanks anyway Max - Have tried all suggestions and no luck. Most frustrating,
I'll keep fiddling and let you know if I suceed

 

[Sheeloo]

Don't be frustrated. Just give us one sample of each condition...

Also make sure that I3 and A3 contain valid dates.

Do note that
2. =I3>=Today()-60
will be TRUE for all dates after 29th March, 2009...

Adjust your conditions accordingly...

 

[Sheeloo]

Don't be frustrated. Just give us one sample of each condition...

Also make sure that I3 and A3 contain valid dates.

Do note that
2. =I3>=Today()-60
will be TRUE for all dates after 29th March, 2009...

Adjust your conditions accordingly...