Conditional Formatting comparing multiple dates

[jrfii]

Hello!

I hope that someone is able to help me. I have gone to several places online and I cannot find the answer. I would like to use conditional formatting. I need to compare the dates in six consecutive columns (example H9:M9) and the one with the most recent date needs to turn yellow. In these six columns it is possible for them to be blank or contain NA, which would be ignored. Can someone help me figure this out? Thank you so much!

 

[SirJB7]

Hi, jrfii!


First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.


As a starting point I'd recommend you to read the three first green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).


Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.


Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.


And about your question...


If you haven't performed yet the search herein, try going to the topmost right zone of this page (Custom Search), type the keywords used in Tags field when creating the topic or other proper words and press Search button. You'd retrieve many links from this website, maybe you find useful information and even the solution. If not please advise so as people who read it could get back to you as soon as possible.


As per your question, enter this as CF formula for H9:M9 :

=H9=MAX(SI(ESNUMERO($H9:$M9);$H9:$M9;"")) -----> in english: =H9=MAX(IF(ISNUMBER($H9:$M9),$H9:$M9,""))


Regards!

 

[SirJB7]

@all


Hi!


Maybe I'm been using this feature since Visicalc but I never noticed this subtle detail.


For checking the above issue I used a help cell in N9 as:

{=MAX(SI(ESNUMERO(H9:M9);H9:M9;""))} -----> in english: {=MAX(IF(ISNUMBER(H9:M9),H9:M9,""))}

Array formula entered of course with Ctrl-Shift-Enter instead of Enter.


When entering the CF formula:

=H9=MAX(IF(ISNUMBER(H9:M9),H9:M9,""))} -----> in english: =H9=MAX(IF(ISNUMBER(H9:M9),H9:M9,""))

I entered it as a normal formula as usual, and as usual I didn't notice that in fact it was an array formula.


Being a normal procedure it's been always unseen for me, so digging a little I found this:

http://www.cpearson.com/excel/cformatting.htm

where it says that effectively Excel treats all formulas as array formulas in CF. Who knew it, explicitely? Not me, even using it everyday.


Additional references here:

http://chandoo.org/wp/2010/09/28/add-zebra-lines-when-value-changes/


Regards!

 

[jrfii]

Thank you very much! I'll try this.