CODEWITHSUNDEEP
google-sheetsconditional-statementsgoogle-sheets-formulags-conditional-formatting
Tfmgvi_971
Tfmgvi_971

Reputation: 81

Google Sheets multiple conditional formatting for date range and text

I'm trying to apply multiple conditional formatting to a google sheets column. I want two conditions:

A Column needs to equal/contain "Accepted" from a dropdown cell

B Column needs to contain a future due date that is within 14 days to today

I want to highlight C column based on those two conditions being met with a priority flag for Accepted and has a due date within 14 days of today. Here's what I've got so far:

Custom conditional formatting for column C is:

=(A="Assigned")+(B<=TODAY()+14)

What I'd like to see is:

=(A="Assigned")+(B<=TODAY()+14)+(B>=TODAY())

But it's not working. Either I have to specify the exact reference columns (ex: A5) which aren't what I want, or it's not working at all. Any recommendations?

Google Sheets Example

Upvotes: 0

Views: 1165

Answers (2)

player0
player0

Reputation: 1

you were close... this is what would work as well:

=($A2="Assigned")*($B2<=TODAY()+14)*($B2>=TODAY())

Upvotes: 1

Tom Sharpe
Tom Sharpe

Reputation: 34220

Yes - just get it right for the first row (say row 2) and the other rows will follow by relative addressing:

=and(A2="Accepted",B2>=today(),B2<=today()+14)

enter image description here

If you want to apply the conditional formatting to more than one column, put in dollar signs to anchor the columns:

and($A2="Accepted",$B2>=today(),$B2<=today()+14)

Upvotes: 1

Related Questions