Conditional cell formatting (due dates) in Mac Numbers

Question

username2022 Author

Level 1

4 points

Conditional cell formatting (due dates) in Mac Numbers

Hoping that someone can please help me figure out how to set the conditional cell formatting for a Mac Numbers file. Your help is much appreciated, I've tried searching the forums but am getting a little lost in trying to figure this out. Here is what I'm hoping to accomplish:

Column I lists whether the invoice has been paid ("received" or "invoiced")

Column H lists the payment due date (based on a formula calculating 30 days from the invoice date)

When an invoice is unpaid (Column 1 invoiced), I would like Column H to highlight:

Green if within 7 days of the due date
Yellow if 0-30 days after the due date
Orange if 31-60 days after the due date
Red if 61-90 days after the due date

Thank you very much for your help, truly appreciated.

iMac Line (2012 and Later)

Posted on Mar 15, 2022 10:15 AM

Reply

Answer 1

Top-ranking reply

Barry

Level 9

59,951 points

Mar 15, 2022 1:05 PM in response to username2022

Hi un'22,

Conditional highlighting rules are read by numbers starting at Rule 1 and progressing down the list. The first rule containing a condition that is TRUE is applied, and the rest are not read. If none of the conditions are met (TRUE), no highlighting is applied.

Cell A1 contains the date of "Today" when the screen shot below was taken.

Here are the rules I applied to the cells in column I:

I did not include a rule to turn off the highlighting if the due date was more than 90 days ago.

If you do want one, the rule would be:

Date is before

90

days ago

with your choice of what fill (grey text? ) to apply.

This rule would be the latest to return TRUE, so it would need to be placed as Rule 1.

Regards,

Barry

Reply

Conditional cell formatting (due dates) in Mac Numbers

Similar questions