Highlight duplicates based on date range in another column

[neilskinner]

Hello,

Can anyone help me with a conditional formatting formula to highlight duplicate data in column H, but only if data in Column I (dates) are at least 2 weeks apart? We are keeping track on railcar numbers and their dates of arrival. If there is a duplicate entry of a railcar but the arrival dates are greater than two weeks apart, we know that this is accurate. However, if arrival dates are within two weeks we know that this data is duplicated and should be highlighted. Any help would be greatly appreciated!

 

[Eric W]

In my original formula, I had to find a way to not compare a row with itself, so I told it to ignore a row if it had the same date. Which as you found out will prevent some rows from highlighting. I found another way to do that, and as a benefit it's a shorter formula, so it should be more efficient:

Book1
	H	I	J	K
1	RAIL CAR	WEIGHTS	TONS	ARRIVED
2	ACFX 220125	147859	73.9295	1/11/2022
3	ACFX 220136	150887	75.4435
4	ADMX 1013	185950	92.975	4/4/2022
5	ADMX 112	187680	93.84
6	ADMX 21005	191760	95.88	3/30/2022
7	ADMX 21010	191740	95.87	3/27/2022
8	ADMX 21010	186600	93.3	4/2/2022
9	ADMX 25432	186360	93.18	4/25/2022
10	ADMX 25475	186320	93.16	4/13/2022
11	ADMX 25615	186720	93.36	3/25/2022
12	ADMX 25828	187140	93.57	4/4/2022
13	ADMX 26198	186720	93.36	4/17/2022
14	ADMX 26413	186480	93.24	3/30/2022
15	ADMX 30267	196280	98.14
16	ADMX 30273	196740	98.37	4/21/2022
17	ADMX 30731	221160	110.58
18	ADMX 30733	220200	110.1	4/13/2022
19	ADMX 30734	221240	110.62	4/6/2022
20	ADMX 30796	221180	110.59	4/21/2022
21	ADMX 30920	221160	110.58	4/1/2022
22	ADMX 30938	220240	110.12	4/8/2022
23	ADMX 30949	221120	110.56	4/6/2022
24	ADMX 30998	221120	110.56	4/8/2022
25	ADMX 31045	221180	110.59	4/17/2022
26	ADMX 1013	220780	110.39	4/2/2022
27	ADMX 30920	220260	110.13	4/1/2022
28	ADMX 31076	220820	110.41	3/25/2022
29	ADMX 31114	220640	110.32
30	ADMX 31180	221480	110.74	3/20/2022
31	ADMX 31190	198100	99.05	4/17/2022
32	ADMX 31224	221400	110.7	4/2/2022
33	ADMX 31228	220920	110.46	3/19/2022
34	ADMX 31256	220660	110.33
35	ADMX 31280	221340	110.67	3/27/2022
36	ADMX 31280	220820	110.41
37
38
Sheet12

Cell Formulas
Range	Formula
J2:J36	J2	=I2/2000

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H1:H20000	Expression	=(H1<>"")*(K1<>"")*(MIN(ABS(K1-IF(H1=H$1:H$20000,IF(ROW()<>ROW(H$1:H$20000),K$1:K$20000))))<14)	text	NO

Let me know how this works.

 

[neilskinner]

at first glance, it looks like you got it!! I really appreciate it!!