| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Highlight cell on certain dates
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Highlight cell on certain dates
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? "Lise" wrote: 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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Highlight cell on certain dates
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" wrote: 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? |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Highlight cell on certain dates
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 Lise "Sheeloo" wrote: 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? "Lise" wrote: 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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Highlight cell on certain dates
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 Lise "Max" wrote: 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" wrote: 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? |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Highlight cell on certain dates
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" wrote: 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! |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Highlight cell on certain dates
Thanks anyway Max - Have tried all suggestions and no luck. Most frustrating,
I'll keep fiddling and let you know if I suceed :-) -- Thanks Lise "Max" wrote: 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" wrote: 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! |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Highlight cell on certain dates
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... "Lise" wrote: 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 Lise "Sheeloo" wrote: 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? "Lise" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| how to highlight more related cells if cell highlight | Excel Discussion (Misc queries) | |||
| Highlight Expired dates | Excel Discussion (Misc queries) | |||
| Highlight Specific dates/cells | Excel Discussion (Misc queries) | |||
| How do I highlight a cell using conditional formatting and dates | Excel Worksheet Functions | |||
| Highlight cells with ctrl-click but only un-highlight one cell | Excel Discussion (Misc queries) | |||
Linear Mode
