Conditional formatting but ignoring the blank cells

Question

Hello,&nbsp;&nbsp;Thanks in advance for any help!&nbsp;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.&nbsp;&nbsp;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 = REDInvoices due within the next 7 days = OrangeInvoices due in 2 weeks = GreenInvoices due in 3 weeks = PurpleInvoices due EOM = Blue&nbsp;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.&nbsp;Not sure if this makes any sense, i hope it does and if anyone could help me it would be highly appreciated!!&nbsp;Below is an example of the exported table I am using.&nbsp;Invoice DateDue DateInvoice NumberInvoice Reference&nbsp;&nbsp;&nbsp;&nbsp;Customer X04 Jul 201730 Aug 2017INV-XPO18 Jul 201710/8/2017INV-BPOTotal&nbsp;Customer X&nbsp;&nbsp;&nbsp;

yury tokarev · Answer

Hi Penny,&nbsp;please find attached my&nbsp;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()).&nbsp;Then I have&nbsp;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'.&nbsp;Hope this helps&nbsp;RegardsYury&nbsp;&nbsp;&nbsp;

penny mcfall · Answer

Hi Yury,Thank you so much for that, it is exactly what I needed!Have a good day!Kind RegardsPenny

Forum Discussion

Conditional formatting but ignoring the blank cells

2 Replies

Resources

Invoice Date	Due Date	Invoice Number	Invoice Reference

Customer X
04 Jul 2017	30 Aug 2017	INV-X	PO
18 Jul 2017	10/8/2017	INV-B	PO
Total Customer X