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?

Answer 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)

Answer 2

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

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