Excel 2010 conditional formatting ignoring blanks in 2 columns

[chasidar]

Hi! I am trying to do conditional formatting so that if the date in a specific cell in column O is less than or equal to the date in the specific cell in column M, the cell in column O is filled in. I want to ignore blanks in both columns.
I started with this formula: =$O2<=$M2 but that fills in the blanks as well.

So I tried =AND(=$O2<=$M2,$O2<>"") but this highlights cells where the cell in column M is blank

So I tried =AND(=$O2<=$M2,$O2<>"",$M2<>"") but this gives me the same result.

Any assistance would be appreciated

Fern

 

[Marcelo Branco]

Try

=AND(O2<>"",M2<>"",$O2<=$M2)

M.

 

[chasidar]

Thanks Marcelo. Unfortunately, I get the same result - highlighted cells where the cell column M is blank.

 

[Marcelo Branco]

Thanks Marcelo. Unfortunately, I get the same result - highlighted cells where the cell column M is blank.

It worked for me.

Are there formulas in M2 and/or O2?

M.

 

[Marcelo Branco]

Try this new version

=AND(ISNUMBER(M2),ISNUMBER(O2),O2<=M2)

 

[chasidar]

No formulas or numbers, just dates which are formatted as dates. The new version didn't work and I tried the previous again and it colors the blank cells as well.

 

[Marcelo Branco]

No formulas or numbers, just dates which are formatted as dates. The new version didn't work and I tried the previous again and it colors the blank cells as well.

Both versions worked for me with dates in M2 and O2 - Excel 2010 using Formula option

So i dont know what else i can do to help you.

M.