Highlight Duplicates, but only if less than 24 hours old

[Bortas]

Good day!

I'm running a simple spreadsheet:
-ColumnA: string input by user
-ColumnB: timestamp of when string was input (=IF(A2<>"", NOW(), "")

What I'm trying to do is have conditional formatting highlight duplicate values in Column A, but only if the timestamp is less than 24 hours old.

I know that highlighting duplicate values is: COUNTIF(A:A, A5)>1

And so I thought I'd try: =AND(IF(B:B, $B5>=NOW()-1),COUNTIF(A:A, A5)>1)
but that is not correct.

I feel like I'm close, but not quite there. Anyone have any tips?
Thanks!
-Bort

 

[jasonb75]

That's what COUNTIFS is for

=COUNTIFS(A:A,A5,B:B,">="&NOW()-1)

 

[gaz_chops]

.

 

[Bortas]

=COUNTIFS(A:A,A5,B:B,">="&NOW()-1)

Has highlighted all cells in the last 24 hours, not just the duplicate cells. Furthermore, if an instance happened in the last 24hours, and there is a duplicate that is OLDER than 24 hours, it highlights the old thing too.

I think your duplicate statement is missing the last little bit (>1), as the formula for just highlighting duplicates is: =COUNTIF(A:A, A5)>1

Older than 24 hours should have no formatting of any kind
Less than 24 hours should be highlighted only if cell in A is a duplicate of another item **within the last 24 hours**

Thank you for trying!
-Bort

 

[jasonb75]

Sorry, I did it as a cell formula to count the records instead of a cf formula your original formula was closer

=AND($B5>=NOW()-1),COUNTIF(A:A, A5)>1)

 

[Bortas]

Oh man, that's the ticket! Slight correction: =AND($B5>=NOW()-1,COUNTIF(A:A, A5)>1)

(Remove the ) after NOW()-1)

Thanks for your help!

-Bort

 

[Bortas]

You know, unfortunately, i'm figuring out that this formula doesn't quite do the trick. It did appear so, on initial testing, but I kept seeing some oddness I was having a hard time defining. Having been working with it for the last several weeks, I figured out the above formula ACTUALLY does the following behavior:
-Search column A for duplicates, highlight a cell if it is the duplicate of a cell in column A, but only if the item is less than 24 hours old.
(Specifically, if "Sally Smith" was entered twice, and one was over 24 hours old, only the recent one was highlighted, when the goal would be that neither would be)

Expected / hoped for behavior:
-Search column A within the last 24 hours only (determined in column b). Highlight duplicate values.

For example, if "Sally Smith" appears twice in column A, if both of those were entered in the last 24 hours, BOTH would be highlighted. If one or both was more than 24 hours old, NEITHER would be highlighted.

The goal is that the conditional formatting is only looking for duplicates that have occurred in the last 24 hours. And if the item duplicates something older than 24 hours, THEN IT DOESNT get highlighted or noted in an way.

 =AND($B5>=NOW()-1,COUNTIF(A:A, A5)>1)

Then I would think the logic would look something like:

 If A < 24h old, Then, count duplicates in column A

Thanks for any ideas!

 

[Bortas]

Hmmm, perhaps a nested if?

if(b>24h, countif(A duplicates))

 

[jasonb75]

Going back to a variation of my first suggestion, try

=COUNTIFS(A:A,A5,B:B,">="&(NOW()-1))>1