Highlight cell on certain dates

  • Thread starter Thread starter Lise
  • Start date Start date
L

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
 
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?
 
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?
 
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
 
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?
 
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
 
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?
 
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!
 
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!
 
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
 
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
 
Thanks anyway Max - Have tried all suggestions and no luck. Most frustrating,
I'll keep fiddling and let you know if I suceed :-)
 
Thanks anyway Max - Have tried all suggestions and no luck. Most frustrating,
I'll keep fiddling and let you know if I suceed :-)
 
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...
 
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...
 
Back
Top