Conditional formatting with date

[honkeyman01]

Version: 2008
Operating System: Mac OS X 10.6 (Snow Leopard)

I am working on a spreadsheet that keeps my invoices at work organised.
I have columns for invoice number, amount, date issued, payment due date, Payed (Y/N), date payed etc. I already use conditional formatting to highlight the whole rows for the invoices that have not been payed (N), and i believe i can also highlight the same row if the 'payment due date' is past todays date todays date (using the formula Is and highlighting the row).
What i would like is: Payed with N to highlight the whole row one colour (say yellow), then if still N when past the due date, the whole row changes to another colour (say red). However when payment has ben made (Y), formatting clears.
I can't seem to put priority on the formatting (no payment after due date in RED, more serious than yellow), plus once Y has been entered to clear the formatting from that expired due date.
Hope this makes sense!

 

[Bob Greenblatt]

Version: 2008
Operating System: Mac OS X 10.6 (Snow Leopard)

I am working on a spreadsheet that keeps my invoices at work organised.
I have columns for invoice number, amount, date issued, payment due date,
Payed (Y/N), date payed etc. I already use conditional formatting to highlight
the whole rows for the invoices that have not been payed (N), and i believe i
can also highlight the same row if the 'payment due date' is past todays date
todays date (using the formula Is and highlighting the row).
What i would like is: Payed with N to highlight the whole row one colour (say
yellow), then if still N when past the due date, the whole row changes to
another colour (say red). However when payment has ben made (Y), formatting
clears.
I can't seem to put priority on the formatting (no payment after due date in
RED, more serious than yellow), plus once Y has been entered to clear the
formatting from that expired due date.
Hope this makes sense!

You can set up three conditions for the formatting. Make condition 1, a
formula that checks the payment column for a Y. To check for a due date,
simply use today() in the formula. Be careful when setting up the formulas
to get the absolute and relative references correct.