Highlight Duplicates with same date versus within a Timeline

[Marsman]

Hello - I am currently using the following formula to show Duplicate values in column C based on the criteria of the date being the same in Column A.

Column B is a manually entered Time stamp that I need to be able to now implement. So, if the info in Column C duplicates within 24 hours of each other I need to change the Highlight Duplicate to that.

I am at a loss on how to change the Date reference to a Date and Time Reference to get the 24 hour window.

any help would great be appreciated.

Book1
	A	B	C	D	E	F	G	H	I
1	7/15/2021	5:30 AM	test	Current Date Results		Desired Results	7/15/2021	5:30 AM	test
2	7/15/2021	5:45 AM	test1				7/15/2021	5:45 AM	test1
3	7/15/2021	6:05 AM	test2				7/15/2021	6:05 AM	test2
4	7/15/2021	6:12 AM	test				7/15/2021	6:12 AM	test4
5	7/16/2021	6:22 AM	test2				7/16/2021	6:22 AM	test2
6	7/16/2021	6:47 AM	test3				7/16/2021	6:47 AM	test3
7	7/16/2021	7:18 AM	test4				7/16/2021	7:18 AM	test4
8	7/16/2021	7:21 AM	test5				7/16/2021	7:21 AM	test5
9	7/16/2021	9:54 AM	test6				7/16/2021	9:54 AM	test6
10	7/16/2021	10:38 AM	test7				7/16/2021	10:38 AM	test7
11	7/16/2021	11:45 AM	test5				7/16/2021	11:45 AM	test5
12	7/16/2021	10:58 PM	test9				7/16/2021	10:58 PM	test9
13	7/17/2021	2:13 AM	test				7/17/2021	2:13 AM	test
14	7/17/2021	3:46 AM	test1				7/17/2021	3:46 AM	test1
15	7/17/2021	4:42 AM	test2				7/17/2021	4:42 AM	test2
16	7/17/2021	4:44 AM	test3				7/17/2021	4:44 AM	test3
17	7/17/2021	7:35 AM	test				7/17/2021	7:35 AM	test4
18	7/17/2021	11:52 AM	test5				7/17/2021	11:52 AM	test5
19	7/18/2021	1:11 PM	test6				7/18/2021	1:11 PM	test6
20	7/18/2021	12:30 AM	test1				7/18/2021	12:30 AM	test1
21	7/18/2021	1:30 AM	test1				7/18/2021	1:30 AM	test
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C1:C21	Expression	=COUNTIFS($A$1:$A$21,A1,$A$1:$A$21,"<>",$C$1:$C$21,C1,$C$1:$C$21,"<>")>1	text	NO

 

[Marcelo Branco]

See if does what you want

15072021 Testes.xlsx
	A	B	C
1	15/07/2021	05:30 AM	test
2	15/07/2021	05:45 AM	test1
3	15/07/2021	06:05 AM	test2
4	15/07/2021	06:12 AM	test4
5	16/07/2021	06:22 AM	test2
6	16/07/2021	06:47 AM	test3
7	16/07/2021	07:18 AM	test4
8	16/07/2021	07:21 AM	test5
9	16/07/2021	09:54 AM	test6
10	16/07/2021	10:38 AM	test7
11	16/07/2021	11:45 AM	test5
12	16/07/2021	10:58 PM	test9
13	17/07/2021	02:13 AM	test
14	17/07/2021	03:46 AM	test1
15	17/07/2021	04:42 AM	test2
16	17/07/2021	04:44 AM	test3
17	17/07/2021	07:35 AM	test4
18	17/07/2021	11:52 AM	test5
19	18/07/2021	01:11 PM	test6
20	18/07/2021	12:30 AM	test1
21	18/07/2021	01:30 AM	test
Plan5

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:C21	Expression	=--(SOMARPRODUTO(--($C$1:$C$21=$C1);--(ARRED($A$1:$A$21+$B$1:$B$21;6)>=ARRED($A1+$B1-1;6));--(ARRED($A$1:$A$21+$B$1:$B$21;6)<=ARRED($A1+$B1+1;6)))>1)	text	NO

XL2BB does not convert formulas used in CF in Portuguese to English so, follows English version to be used in CF
=--(SUMPRODUCT(--($C$1:$C$21=$C1),--(ROUND($A$1:$A$21+$B$1:$B$21,6)>=ROUND($A1+$B1-1,6)),--(ROUND($A$1:$A$21+$B$1:$B$21,6)<=ROUND($A1+$B1+1,6)))>1)

Hope this helps

M.

 

[Marsman]

ok... i have placed into CF and it is not giving me any results? should i be highlighting just column C1:C21 or A1:C21 and inputting the CF that way?

XL2BB does not convert formulas used in CF in Portuguese to English so, follows English version to be used in CF
=--(SUMPRODUCT(--($C$1:$C$21=$C1),--(ROUND($A$1:$A$21+$B$1:$B$21,6)>=ROUND($A1+$B1-1,6)),--(ROUND($A$1:$A$21+$B$1:$B$21,6)<=ROUND($A1+$B1+1,6)))>1)

 

[Marcelo Branco]

Select A1:C21
Home > Conditional Formatting > New rule > Use a formula...
Insert the formula and pick the format you want.

It worked for me

M.

 

[Marsman]

Oddly enough i did your steps, step by step and still nothing.

would it matter that i am using this version of excel maybe? i know there are other limitations to it like VBA but have not come across any formula issues?

Microsoft® Excel® for Microsoft 365 MSO (16.0.14131.20296) 64-bit

 

[Marcelo Branco]

I tried the formula one more time and everything worked. There must be problems with your data.

Check that the dates in column A are real dates (numbers), not text that look like dates.

Also check that column B contains times, not dates - when I copied and pasted your data I noticed wrong values in cells B20 and B21 (they contained dates, not times)

M.

 

[Marsman]

thanks again... i got it now.

Once i change the format of the date from dd/mm/yyy to mm/dd/yyyy the highlighting works.

Much appreciate your help with this.

 

[Marcelo Branco]

You're welcome. Thanks for the feedback.

M.