Conditional Formatting Dates in whole sheet - Dates before Today

[williamjtmarsh]

Hi guys,

Me again!

Trying to conditional format the whole sheet so that whatever date falls past today, then it will red out, however it conditions all blanks files to do this also, so everything in the file gets filled in

Would appreciate the help!

Thanks
Will

 

[etaf]

use an AND () with the formula for conditional formatting
a blank cell is seen as 0 which is 0/1/1900

AND( A1 < today() , A1 <> "" )

so if its the whole sheet including A1 , thats the formula
is the cell less than today - ie yesterday or earlier and is the cell NOT blank

 

[williamjtmarsh]

Hi Wayne,

Thank you for the response! However it doesn't seem to work

=AND($1:$1048576<TODAY(),$1:$1048576<>"") - This is the selection of the whole sheet

Thanks
Will

 

[etaf]

thats not the formula
you use in conditional formatting the formula I wrote
AND( A1 < today() , A1 <> "" )

does not need the range in the formula - thats the range you select in the conditional formatting first

 

[williamjtmarsh]

Perfect! Thanks

 

[etaf]

you are welcome