Aug 13 2017
05:24 PM
- last edited on
Jul 25 2018
09:54 AM
by
TechCommunityAP
Aug 13 2017
05:24 PM
- last edited on
Jul 25 2018
09:54 AM
by
TechCommunityAP
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 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 |
Aug 13 2017 07:53 PM
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
Aug 13 2017 08:19 PM