Conditional Formatting

[n18]

Hi

I have conditional formatting, basically there's a date in a cell and if today is within 15 days of the date it turns red. It works fine, but I'm applying it to a few hundred cells and the formatting is also turning current blank cells red. I need to adjust my below formula to ensure that any blank cell is not changed, simple I know! but I've tried a few conditions and cant seem to get it to work. I need to check if B2 is blank or not. I'm already checking to see if D2 is blank.

=AND((B2-TODAY())<15,D2="")

Thanks

 

[Andrew Poulsom]

Is it?

=AND((B2-TODAY())<15,B2<>"",D2="")

 

[n18]

Thanks Andrew, that's exactly what i tried and assumed would have worked but unfortunately not, The data is coming from a linked worksheet.

 

[Andrew Poulsom]

You mean B2 contains a formula? If so what is it?

 

[n18]

B2 is simply getting its data from another worksheet so its linked

='Sales'!J2

I think your formula is right its just not picking up the cell as being blank I guess that that may be another issue relating to the linkage

 

[Andrew Poulsom]

What's in J2 on sheet Sales?

 

[n18]

Thanks for the reply. I have a date in J2 in the format dd/mm/yyyy. The format type is Custom dd/mm/yyyy;"";"" the column which is linking to it has the same custom format type.

I am using this custom type because when I linked the sheets any blank cell in sales sheet was appearing as 01/01/1900 in my 2nd sheet.

 

[Andrew Poulsom]

So it's zero formatted to display null. In that case try:

=AND((B2-TODAY())<15,B2>0,D2="")

 

[n18]

Works now, cheers Andrew, appreciate the help, couldn't seem to figure that out myself. Thanks a lot