Highlight duplicate values relative to current date

Occasional Visitor

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

I want to also highlight based on how many occurrences, I've done this with the two following rules

  1. =COUNTIF($B$11:$B$36, $B11) >= 3

  2. =COUNTIF($B$11:$B$36, $B11) = 2

How would I incorporate this to look only at the last 3 weeks instead of pre defined ranges? There is a highlight with "A date occurring" but I'm unsure if there is way to link these. Would this be possible by using some kind of helper table to gather the data of the 'highlighted dates' first?

I've attached a screenshot of what this would look like.


1 Reply


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.