CODEWITHSUNDEEP
google-sheetsduplicatesgoogle-sheets-formulaarray-formulasgs-conditional-formatting
BrettG
BrettG

Reputation: 41

Highlight duplicate names if their timestamp is within 24 hours of each other

I am trying to figure out a formula that may be conditional formatting but I am starting to think I may need a bit more then just that.

What I am looking for is on the Raw Data page that if there are 2+ names that are the same (so duplicate names) in the B column I want it to check to look in the A column to see if those 2+ names were reported within 24 hours of each other, if so change the background color of the A column A (can be more columns but prefer just a).

I know I will have to use =COUNTIF(B:B)>1 to show duplicates but I’m not sure how to make it then look in the A cell of the duplicates it just found and see if the 2 or more duplicates timestamp’s is within 24 hours of one another.

I do not mean 1 day ago from now either. I’m guessing it’s going to be some sort of date / time math command to see if the result of the timestamp is less then 24 hours.

Any help / ideas would be appreciated.

Upvotes: 1

Views: 209

Answers (1)

player0
player0

Reputation: 1

try:

=ARRAYFORMULA(COUNTIFS(B:B, B2, DATEVALUE(A:A), DATEVALUE(A2))>1)

0

Upvotes: 1

Related Questions