How to color cells based on two values

New Contributor



I have a spreadsheet where the C column different text values and the F column has dates.


I'd like to have a formula saying "if C2 has "new referral " and F2 is older than 7 days then highlight cell F2"


I tried adding a new column and adding IF(AND(C2= “New Referral”,F2<(Today()-7)),1,0) but no luck, and I tried =AND($A2 = “New Referral”, $C2 <= TODAY() - 7))


Thanks in advance 

2 Replies
best response confirmed by Sergei Baklan (MVP)


=AND(C2= "New Referral",F2<=TODAY()-7)

This rule for conditional formatting works in my sheet.

conditional formatting.JPG 

@Quadruple_Pawn ahh thanks i put an extra ) at the end