i want to highlight close occurring dates in yellow

[sleek12]

I want the date in column I to highlight in yellow with column L:Y to highlight in yellow as well , eg if the date in column I is 14/12/2023 then to highlight in yellow if the date is 2 days back or 3 days front , 12/12/2023,13/12/2023,15/12/2023/16/12/2023/17/12/2023. i am using office 365, formula, power query ,LET, lambda, VBA macros are ok

38205

C28716907

Q-JB0765

Type C - Private Car

Trident Insurance Company Ltd.

DIRECT

LucyMakena

PAUL KURIA WAWERU

column I 14/12/2023​

08007018565252023

KBK972E

column L 23/01/2024​

column M 23/01/2024​

and so forth... 16/01/2024​

03/01/2024​

38067

C28716898

Q-JB0608

Type C - Private Car

Trident Insurance Company Ltd.

DIRECT

LucyMakena

ZACHARIAH MUTHII KABUGA

14/12/2023​

08007018565232023

KAD480C

29/01/2024​

24/01/2024​

16/01/2024​

21/12/2023​

14/11/2023​

36701

C28716235

Q-JA8855

Type C - Private Car

Trident Insurance Company Ltd.

DIRECT

LucyMakena

ELIAS KINYUA KIUGU

14/12/2023​

08007017744412023

KAX033G

13/01/2024​

12/01/2024​

13/01/2024​

12/01/2024​

10449

B13355322

Q-JB7516

Type B - Commercial Vehicle

Trident Insurance Company Ltd.

DIRECT

LucyMakena

KINYUA ERIC MAWIRA

15/12/2023​

08008013526522023

KBH624S

12/01/2024​

12/01/2024​

11700

B13357275

Q-JC1980

Type B - Commercial Vehicle

Trident Insurance Company Ltd.

DIRECT

LucyMakena

JOHN KABURU

15/12/2023​

08008013625422023

KAR025P

21/12/2023​

15/12/2023​

05/12/2023​

53498

C28724678

Q-JC3901

Type C - Private Car

Trident Insurance Company Ltd.

DIRECT

LucyMakena

ERIC MUTUMA

15/12/2023​

08007018339942023

KAV81OA

29/11/2023​

53193

C28724649

Q-JC3716

Type C - Private Car

Trident Insurance Company Ltd.

DIRECT

LucyMakena

PETER ROY BUNDI KIMEU

15/12/2023​

08007018545522023

KAY995R

13/01/2024​

13/01/2024​

11/11/2023​

10/11/2023​

53157

C28724644

Q-JC3664

Type C - Private Car

Trident Insurance Company Ltd.

DIRECT

LucyMakena

REV.NICHOLAS MWENDA BIRITHU

15/12/2023​

08007017440722023

KDH038N

not found

53070

C28724637

Q-JC3615

Type C - Private Car

Trident Insurance Company Ltd.

DIRECT

LucyMakena

CHARLES MATHIU KIRIMI

15/12/2023​

08007018565242023

KDB882R

11/01/2024​

11/01/2024​

29/11/2023​

52413

C28724320

Q-JC2948

Type C - Private Car

Trident Insurance Company Ltd.

DIRECT

LucyMakena

NAOMI LEKISAAT

15/12/2023​

08007018562532023

KBL593C

24/01/2024​

24/11/2023​

02/11/2023​

51981

C28723899

Q-JC2525

Type C - Private Car

Trident Insurance Company Ltd.

DIRECT

LucyMakena

GODFFREY MITHIKA THAIRU

15/12/2023​

08007113526652023

KMGK696U

not found

44568

C28718870

Q-JB7410

Type C - Private Car

Trident Insurance Company Ltd.

DIRECT

LucyMakena

BENJAMIN PAUL MUKORA

15/12/2023​

08007016393432022

KAX088Z

13/01/2024​

12/01/2024​

13/01/2024​

12/01/2024​

42967

C28718709

Q-JB6288

Type C - Private Car

Trident Insurance Company Ltd.

DIRECT

LucyMakena

WILSON GATOBU MBWIRIA

15/12/2023​

08007017817732023

KSF713

not found

42940

C28718703

Q-JB6251

Type C - Private Car

Trident Insurance Company Ltd.

DIRECT

LucyMakena

MARTIN MURITHI GIOTIMIBU

15/12/2023​

08007018525652023

KBQ683B

23/01/2024​

16/01/2024​

09/01/2024​

25/11/2023​

03/11/2023​

42302

C28718538

Q-JB5804

Type C - Private Car

Trident Insurance Company Ltd.

DIRECT

LucyMakena

MICHAEL KINYUA

15/12/2023​

08007018516402023

KAD734L

29/01/2024​

24/01/2024​

16/01/2024​

21/12/2023​

14/11/2023​

 

[etaf]

is it exactly 2 days and 3 days and NOT between -2 to 3 days
if the latter
=AND($I$2-L2>-2,$I$2-L2<3)

for 2007, 2010 , 2013 , 2016 , 2019 or 365 Subscription excel version
Conditional Formatting

Highlight applicable range >>
L2:Y100 - Change, reduce or extend the rows to meet your data range of rows

Home Tab >> Styles >> Conditional Formatting
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true:

=AND($I$2-L2>-2,$I$2-L2<3)

Format [Number, Font, Border, Fill]
choose the format you would like to apply when the condition is true
OK >> OK

ONLY 1 in the sample meets that

Book5
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1		38205	C28716907	Q-JB0765	Type C - Private Car	Trident Insurance Company Ltd.	LucyMakena	PAUL KURIA WAWERU	column I		KBK972E	column L	column M	and so forth...	1/3/24
2									12/14/23			1/23/24	1/23/24	1/16/24
3		38067	C28716898	Q-JB0608	Type C - Private Car	Trident Insurance Company Ltd.	LucyMakena	ZACHARIAH MUTHII KABUGA	12/14/23		KAD480C	1/29/24	1/24/24	1/16/24	12/21/23	11/14/23
4		36701	C28716235	Q-JA8855	Type C - Private Car	Trident Insurance Company Ltd.	LucyMakena	ELIAS KINYUA KIUGU	12/14/23		KAX033G	1/13/24	1/12/24	1/13/24	1/12/24
5		10449	B13355322	Q-JB7516	Type B - Commercial Vehicle	Trident Insurance Company Ltd.	LucyMakena	KINYUA ERIC MAWIRA	12/15/23		KBH624S	1/12/24	1/12/24
6		11700	B13357275	Q-JC1980	Type B - Commercial Vehicle	Trident Insurance Company Ltd.	LucyMakena	JOHN KABURU	12/15/23		KAR025P	12/21/23	12/15/23	12/5/23
7		53498	C28724678	Q-JC3901	Type C - Private Car	Trident Insurance Company Ltd.	LucyMakena	ERIC MUTUMA	12/15/23		KAV81OA	11/29/23
8		53193	C28724649	Q-JC3716	Type C - Private Car	Trident Insurance Company Ltd.	LucyMakena	PETER ROY BUNDI KIMEU	12/15/23		KAY995R	1/13/24	1/13/24	11/11/23	11/10/23
9		53157	C28724644	Q-JC3664	Type C - Private Car	Trident Insurance Company Ltd.	LucyMakena	REV.NICHOLAS MWENDA BIRITHU	12/15/23		KDH038N	not found
10		53070	C28724637	Q-JC3615	Type C - Private Car	Trident Insurance Company Ltd.	LucyMakena	CHARLES MATHIU KIRIMI	12/15/23		KDB882R	1/11/24	1/11/24	11/29/23
11		52413	C28724320	Q-JC2948	Type C - Private Car	Trident Insurance Company Ltd.	LucyMakena	NAOMI LEKISAAT	12/15/23		KBL593C	1/24/24	11/24/23	11/2/23
12		51981	C28723899	Q-JC2525	Type C - Private Car	Trident Insurance Company Ltd.	LucyMakena	GODFFREY MITHIKA THAIRU	12/15/23		KMGK696U	not found
13		44568	C28718870	Q-JB7410	Type C - Private Car	Trident Insurance Company Ltd.	LucyMakena	BENJAMIN PAUL MUKORA	12/15/23		KAX088Z	1/13/24	1/12/24	1/13/24	1/12/24
14		42967	C28718709	Q-JB6288	Type C - Private Car	Trident Insurance Company Ltd.	LucyMakena	WILSON GATOBU MBWIRIA	12/15/23		KSF713	not found
15		42940	C28718703	Q-JB6251	Type C - Private Car	Trident Insurance Company Ltd.	LucyMakena	MARTIN MURITHI GIOTIMIBU	12/15/23		KBQ683B	1/23/24	1/16/24	1/9/24	11/25/23	11/3/23
16		42302	C28718538	Q-JB5804	Type C - Private Car	Trident Insurance Company Ltd.	LucyMakena	MICHAEL KINYUA	12/15/23		KAD734L	1/29/24	1/24/24	1/16/24	12/21/23	11/14/23
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
L2:Y16	Expression	=AND($I$2-L2>-2,$I$2-L2<3)	text	NO

 

[etaf]

deleted

 

[sleek12]

is it exactly 2 days and 3 days and NOT between -2 to 3 days

its between -2 to 3 days

 

[etaf]

its between -2 to 3 days

should be
=AND($I$2-L2>=-2,$I$2-L2<=3)

otherwise as shown
to include =

 

[sleek12]

ummm.... maybe i am going wrong somewhere ??

lucymakenadec2024query (1) (2).xlsm
	I	J	K	L	M	N	O	P	Q	R
77	19/12/2023	08007018554212023	KCJ902R	23/01/2024	20/12/2023	13/12/2023	05/12/2023	16/11/2023	09/11/2023	09/11/2023
78	19/12/2023	08007018541372023	KDB451E	11/01/2024	11/01/2024	29/11/2023
79	19/12/2023	08007018552492023	KCK647G	14/12/2023	03/11/2023
80	19/12/2023	08007018552512023	KAM207E	02/12/2023
81	20/12/2023	08007018561452023	KAL458F	22/12/2023	04/12/2023
82	20/12/2023	08007018566552023	KCT498E	08/01/2024	06/12/2023
83	20/12/2023	0800701245632023	KBN469G	26/01/2024	28/12/2023	30/11/2023
partialmatches

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
L2:Y236	Expression	=AND($I$2-L2>=-2,$I$2-L2<=3)	text	NO

 

[etaf]

Yes , you have I2 fixed at I2, so every row & column is comapred to L2 only
$I$2
and should be
$I2, so rows can change and be compared

time may have an issue , should not be - but as you do have a time in the cells then

=AND($I2-INT(L2)>=-2,$I2-INT(L2)<=3)

I have copied into row77 - and used that as the selection and formula - just for your example
see red

Book7
	I	J	K	L	M	N	O	P	Q	R
77	12/19/23	8.00702E+15	KCJ902R	1/23/24	12/20/23	12/13/23	12/5/23	11/16/23	11/9/23	11/9/23
78	12/19/23	8.00702E+15	KDB451E	1/11/24	1/11/24	11/29/23
79	12/19/23	8.00702E+15	KCK647G	12/14/23	11/3/23
80	12/19/23	8.00702E+15	KAM207E	12/2/23
81	12/20/23	8.00702E+15	KAL458F	12/22/23	12/4/23
82	12/20/23	8.00702E+15	KCT498E	1/8/24	12/6/23
83	12/20/23	8.00701E+14	KBN469G	1/26/24	12/28/23	11/30/23
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
L77:R83	Expression	=AND($I77-INT(L77)>=-2,$I77-INT(L77)<=3)	text	YES