Conditional Formatting in Excel

Question

Build a formula in Excel to Highlight days/times in Column B that do not have a matching day/time within a 24 hour period for the same person/ID Number in column A.

In the example below, the last two rows should be highlighted because there are no other times within 24 hours:

Answer 1

Assuming column A contains the Name/ID (e.g., Last Name, First Name (12345678)) and column B contains the Date/Time, apply this Conditional Formatting formula to the range in column B (for example, B2:B1000):

=COUNTIFS($A:$A,$A2,$B:$B,">="&$B2-1,$B:$B,"<="&$B2+1)=1

Set the format to highlight when the formula returns TRUE.

This works by counting how many records have the same ID in column A and a date/time within 24 hours (±1 day) of the value in column B. If the count is 1, it means the only match is itself, so there are no other times within 24 hours for that same person/ID, and the cell will be highlighted.

---

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin