Google Sheets multiple conditional formatting for date range and text

Question

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

Tom Sharpe · Accepted Answer · 2019-11-26 19:09:11Z

1

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)

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)

answered Nov 26, 2019 at 19:09

Tom Sharpe

35k5 gold badges29 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Tom Sharpe Over a year ago

You're welcome! (Plz tick accepted box if it works for you)

player0 · Accepted Answer · 2019-11-26 20:38:31Z

1

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

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

answered Nov 26, 2019 at 20:38

player0

131k14 gold badges91 silver badges149 bronze badges

Collectives™ on Stack Overflow

Google Sheets multiple conditional formatting for date range and text

2 Answers 2

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Related