Conditional Formatting based on 2 Criteria.

[Philip1957]

Greetings,

I'm sure that when I get this figured out it will be one of those "duh" moments, but none-the-less here's my question.

I want to apply conditional formatting to ($X3) only if cell ($Y3) is blank and the date in ($X3) is older than today.

I've tried variations of =AND($X3<TODAY(), ISBLANK($Y3)) applied to the X column and I can get the cell to format based on the date criteria but not the condition of Y being blank. I need both conditions to be True for the formatting to be applied.

Thanks in advance,
~ Phil

 

[63falcondude]

ok, thanks falcon

Glad to help!

 

[Philip1957]

The "Thank You" was directed at the folks who explained how to make < and > show up in postings.

I still can't get the formatting to work using the suggested solution of =AND($X3 < TODAY(),$Y3="").

It appear5s to randomly apply the formatting regardless of the date of the contents of Y.

 

[63falcondude]

The "Thank You" was directed at the folks who explained how to make < and > show up in postings.

I still can't get the formatting to work using the suggested solution of =AND($X3 < TODAY(),$Y3="").

It appear5s to randomly apply the formatting regardless of the date of the contents of Y.

Can you post a small selection of sample data?

 

[Joe4]

I still can't get the formatting to work using the suggested solution of =AND($X3 < TODAY(),$Y3="").

Are the values in column X really dates, or are they text strings?

Try putting this formula in any blank cell on your sheet:
=ISNUMBER(X3)
Does it return TRUE or FALSE? If it returns FALSE, then X3 is not a valid date and you cannot do Date math on it without converting/coercing it to a date.

And are the values in column Y really blank, or are there spaces or other special characters in there?
What does this formula return?
=LEN(Y3)
If it returns a number greater than 0, then Y3 is not blank, but contains a space or some special character.

If either of those conditions exist, then the Conditional Formatting formula will not work without making adjustments to it (which would be based on what is actually in those columns).

 

[Philip1957]

Are the values in column X really dates, or are they text strings?

Try putting this formula in any blank cell on your sheet:
=ISNUMBER(X3)
Does it return TRUE or FALSE? If it returns FALSE, then X3 is not a valid date and you cannot do Date math on it without converting/coercing it to a date.

And are the values in column Y really blank, or are there spaces or other special characters in there?
What does this formula return?
=LEN(Y3)
If it returns a number greater than 0, then Y3 is not blank, but contains a space or some special character.

If either of those conditions exist, then the Conditional Formatting formula will not work without making adjustments to it (which would be based on what is actually in those columns).

Yes. I am using true date formatting and the cells in column Y are truly blank. Using your tests above I get "True" and "0".

 

[Joe4]

OK. Find two examples in your data, one where it should highlighted and one where it should be not.

Let's say that the the row that should be true (highlighted) is row 7. Then enter this formula in any blank cell:

[COLOR=#333333]=AND($X7 < TODAY(),$Y7="")[/COLOR]

It should return TRUE. Does it?

Let's say that the the row that should be false (not highlighted) is row 9. Then enter this formula in any blank cell:

[COLOR=#333333]=AND($X9 < TODAY(),$Y9="")[/COLOR]

It should return FALSE. Does it?

If both these things do not return what you expect, then there is a problem with your data that you are not recognizing.

If both these return the expected values, but the Conditional Formatting is not working correctly then either:
- You have applied the Conditional Formatting incorrectly
or
- There are other Conditional Formatting Rules interfering with this one.
or
- There is something else interfering, like automated VBA code, or formatting you have applied manually.

 

[Philip1957]

All,

First off, thanks for taking the time to assist me with this.

I tried once again to use the recommended tools for inserting examples of data into the forum and as is always the case, the fail to install properly. Perhaps they have not been updated to run with Office 365 yet.

At any rate, I have placed an abbreviated copy of my file on my Google Drive. You can download Audit Metrics.xlsx from the following link. https://drive.google.com/open?id=0B78uV6HtO7QSN1dhY2FMeDRHd2s

Thanks again for your time and patience.

~ Phil

 

[Joe4]

For corporate security purposes a lot of us (including myself) are unable (or unwilling) to download files from the internet (at least from my current location).

However, in my previous post, I can you instructions on how you should be able to debug the situation yourself. Try that, and see where it leads you.

 

[snjpverma]

All,

First off, thanks for taking the time to assist me with this.

I tried once again to use the recommended tools for inserting examples of data into the forum and as is always the case, the fail to install properly. Perhaps they have not been updated to run with Office 365 yet.

At any rate, I have placed an abbreviated copy of my file on my Google Drive. You can download Audit Metrics.xlsx from the following link. https://drive.google.com/open?id=0B78uV6HtO7QSN1dhY2FMeDRHd2s

Thanks again for your time and patience.

~ Phil

The below given formula works perfectly fine in your file.
=AND(X$3 < TODAY(),Y$3="")

Which row, do you say, is not being highlighted correctly?

 

[Philip1957]

Joe4,

When I apply the AND statements per your recommendation, I get the expected results.

This is an .xlsx file, not xlsm file and as such, the only VBA is the RbbonX code that every spreadsheet has.

There are no other Conditional Formatting rules in my columns X & Y.



sanjeev1234,

I don't know what to say.
Your example, =AND(X$3 < TODAY(),Y$3=""), doesn't work in my spreadsheet and it has row 3 absolute. I need the columns to be absolute, =AND($X3 < TODAY(),$Y3="")


Thanks,

~ Phil