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

Reputation: 41

Highlight Duplicate Within 24 hours timestamp but a different date

I am trying to figure if the following can be done. If there are duplicate names in the B column then it will see if the date and time were within 24 hours of each other in the A column, if so it will highlight the cell yellow.

Currently, the formula I have will only highlight if it was on the same date. Is there a way I can add to the formula to all take into account time? So that if one response is on 5/20/20 at 17:00 and the next duplicate name is at 5/21/20 at 16:00 then the cell would be highlighted.

Here is the formula I am using the just highlights if it is within the same date:

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

I am not sure if something like this is possible. I am guessing that the formula would have to compare both datevalue and timevalue. Any help would be appreciated.

Upvotes: 0

Views: 244

Answers (1)

ziganotschka
ziganotschka

Reputation: 26836

Instead of DATEVALUE you can use TO_PURE_NUMBER

This will return you the number of days from January 1, 1900 including the fraction for past hours and minutes opposed to DATEVALUE that rounds the value down to an integral day number.

Sample:

enter image description here

This allows you to calculate the real difference time between your timestamp.

For example like this:

=ARRAYFORMULA(or(COUNTIFS(B:B, B2, TO_PURE_NUMBER(A:A), ">"&TO_PURE_NUMBER(A2)-1, TO_PURE_NUMBER(A:A), "<"&TO_PURE_NUMBER(A2))>0,COUNTIFS(B:B, B2, TO_PURE_NUMBER(A:A), "<"&TO_PURE_NUMBER(A2)+1,TO_PURE_NUMBER(A:A), ">"&TO_PURE_NUMBER(A2))>0))

Upvotes: 1

Related Questions