Forum Discussion
Highlight duplicate values relative to current date
I have some sample data in which I am trying to highlight duplicate values relative by date. In this case I only want to the rule to account for dates in the past 3 weeks (current week + 2 weeks back...
Select B2:B100 and use the following formulas in conditional formatting rules of type 'Use a formula to determine which cells to format':
=COUNTIFS($B$2:$B$100, $B2, $B$2:$B$100, ">="&TODAY()-21, $B$2:$B$100, "<="&TODAY() >= 3
=COUNTIFS($B$2:$B$100, $B2, $B$2:$B$100, ">="&TODAY()-21, $B$2:$B$100, "<="&TODAY() = 2
Enlarge the range B2:B100 if needed.