Conditional formatting but ignoring the blank cells

Highlighted
New Contributor

Hello, 

 

Thanks in advance for any help!

 

I am trying to set some conditional formatting which I know how to do, but when I enter what I want it to mark the cells as it includes cells with no entry. 

 

What I am trying to do is condition due date cells to a specific colour but if possible showing a 4 week period as below:

Overdue invoices = RED

Invoices due within the next 7 days = Orange

Invoices due in 2 weeks = Green

Invoices due in 3 weeks = Purple

Invoices due EOM = Blue

 

The formula I found for this was:

=TODAY() or =TODAY()+7 (changing 7 to however many days required)

But I think i need to add =IFBLANK to this formula so blank cells are ignored.

 

Not sure if this makes any sense, i hope it does and if anyone could help me it would be highly appreciated!!

 

Below is an example of the exported table I am using.

 

Invoice DateDue DateInvoice NumberInvoice Reference
    
Customer X
04 Jul 201730 Aug 2017INV-XPO
18 Jul 201710/8/2017INV-BPO
Total Customer X   
2 Replies
Highlighted

Hi Penny,

 

please find attached my suggested solution. I have created extra columns for each condition, and used LEN() function to capture empty cells (as an alternative, you can also use ISBLANK()). Then I have created conditional formatting for each of the conditions and applied to the due date column in the order starting with 'Overdue', then 'Due Next 7 days', 'Due in 2 weeks' etc. For each condition I have ticked the box to 'Stop If True'.

 

Hope this helps 

Regards

Yury 

 

 

Highlighted
Hi Yury,

Thank you so much for that, it is exactly what I needed!

Have a good day!
Kind Regards
Penny