Google Spreadsheet Conditional Formatting Based on 2 Cell Values

Question

I'm using Google spreadsheet as an overview of our outgoing invoices. If the invoice is paid, it's green and I add the date of payment. If not, it's red(column B) and empty. Column C has the invoice date in it and column D the expiration date.

How can I make the specific cell in column B turn orange when the payment hasn't been payed and the expiration date has been exceeded?

And yes, I've read the answers on the question Google Spreadsheet Conditional Formatting Based on another Cell Value

Answer 1

Assuming data as in the image and that the current date is 2017:

Clear any conditional formatting from ColumnB and apply standard red fill to it. With ColumnB selected: Format, Conditional formatting..., Format cells if... Cell is not empty with green fill. Add another rule with Custom formula is:

=D1<today()  

select orange fill and Done.

This is an unconventional colour scheme so you might want to consider switching red and orange.

Answer 2

=AND(B4="", C4<TODAY())

That formula changes C4 if B4 is empty AND C4 is before today's date.

Answer 3

If I understood well, your header is :

Paid Date (col B); Invoice Date (col C); Expiration Date (col D).

You want to add conditional formatting and choose "Format cell if ..." : Custom formula is

Then enter the formula :

 = D2 > Today()