How to extract Repeated data from specific Time duration

[faraz502]

Dear Sir,

Sorry for disturb again,

I need to extract data from my automatic location-wise data entry sheet, in this sheet thousand of rows with two times one is the Occurring and one is the Clearing time.

but from some days datasheet received with fake entries and hard to find fake entries because of a lot of entries.

Below mention one location entry example;
Occurring on 9/16/2021 12:00:00 AM
Cleared on 9/16/2021 8:37:26 AM
But the Next entry again comes from12am to 8 am.

first three rows are genuine others are fake

Location	Occurring	Clearing
CMN0609__S_warid_MDCM6039	2021-09-16 00:00:00	2021-09-16 08:37:26
CMN0609__S_warid_MDCM6039	2021-09-16 00:00:00	2021-09-16 08:37:25
CMN0609__S_warid_MDCM6039	2021-09-16 00:00:00	2021-09-16 08:37:24
CMN0609__S_warid_MDCM6039	2021-09-16 00:00:00	2021-09-16 06:18:30
CMN0609__S_warid_MDCM6039	2021-09-16 00:00:00	2021-09-16 06:15:56
CMN0609__S_warid_MDCM6039	2021-09-16 00:00:00	2021-09-16 06:15:53
CMN0609__S_warid_MDCM6039	2021-09-16 00:00:00	2021-09-16 06:15:52
CMN0609__S_warid_MDCM6039	2021-09-16 00:00:00	2021-09-16 06:15:51
CMN0609__S_warid_MDCM6039	2021-09-16 00:00:00	2021-09-16 06:15:50
CMN0609__S_warid_MDCM6039	2021-09-16 00:00:00	2021-09-16 06:15:49

 

[maabadi]

I think this is better for 4 hour limitation also:

Book1.xlsx
	A	B	C	D	E
1	Location	Occurring	Clearing	Result
2	CMN0609__S_warid_MDCM6039	9/16/21 0:00	9/16/21 8:37	OK
3	CMN0609__S_warid_MDCM6039	9/16/21 0:00	9/16/21 8:37	OK
4	CMN0609__S_warid_MDCM6039	9/16/21 0:00	9/16/21 8:37	OK
5	CMN0609__S_warid_MDCM6039	9/16/21 3:00	9/16/21 6:18	FAKE
6	CMN0609__S_warid_MDCM6039	9/16/21 0:00	9/16/21 6:15	FAKE
7	CMN0609__S_warid_MDCM6039	9/16/21 0:00	9/16/21 6:15	FAKE
8	CMN0609__S_warid_MDCM6039	9/16/21 0:00	9/16/21 6:15	FAKE
9	CMN0609__S_warid_MDCM6039	9/16/21 0:00	9/16/21 6:15	FAKE
10	CMN0609__S_warid_MDCM6039	9/16/21 0:00	9/16/21 6:15	FAKE
11	CMN0609__S_warid_MDCM6039	9/16/21 0:00	9/16/21 6:15	FAKE
12
13
Sheet1

Cell Formulas
Range	Formula
D2:D11	D2	=IF(SUMPRODUCT(--($A$2:A2=A2)*(--(ABS($B$2:B2-MIN(INDEX($B$2:B2,MATCH(B2 + (5/6),$B$2:B2,1))))<(1/6)))) >3,"FAKE","OK")