Difference in date

[vmjan02]

I have this records below

If the “PROJECT_NUMBER” is repeated AND “INCIDENT_CREATION_DATE” between the two incidents is less than 31 days then mark it as a "repeat"

I am confused as how shall I do it, as did countif as well to find the duplicate and then the diff in date but the records can be in any row.

INCIDENT_ID	INCIDENT_CREATION_DATE	PROJECT_NUMBER	Result
136672962	02-11-2019 09:51:01	112298608	Repeat
136672522	02-11-2019 09:51:01	112298601
136672581	02-11-2019 09:51:01	112298602
136672969	02-12-2019 09:51:01	112298608
136672991	02-10-2019 09:51:01	112298690
136672999	02-11-2019 09:51:01	112298661	Repeat
136672810	02-11-2019 09:51:01	112298685
1366721010	02-15-2019 09:51:01	112298661

<tbody>
</tbody>

 

[Toadstool]

Change the $20 to whatever is your maximum row.

	A	B	C	D
1	INCIDENT_ID	INCIDENT_CREATION_DATE	PROJECT_NUMBER	Result
2	136672962	2/11/2019 9:51	112298608	Repeat
3	136672522	2/11/2019 9:51	112298601
4	136672581	2/11/2019 9:51	112298602
5	136672969	2/12/2019 9:51	112298608
6	136672991	2/10/2019 9:51	112298690
7	136672999	2/11/2019 9:51	112298661	Repeat
8	136672810	2/11/2019 9:51	112298685
9	1366721010	2/15/2019 9:51	112298661

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula D2 to Dnnn =IF(COUNTIF($C3:$C$20,C2)<1,"",IF(ABS(B2-INDEX($B3:$B$20,MATCH(C2,$C3:$C$20,0)))<31,"Repeat","")) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[vmjan02]

thanks, will check and update you.

 

[vmjan02]

Change the $20 to whatever is your maximum row.

	A	B	C	D
1	INCIDENT_ID	INCIDENT_CREATION_DATE	PROJECT_NUMBER	Result
2	136672962	2/11/2019 9:51	112298608	Repeat
3	136672522	2/11/2019 9:51	112298601
4	136672581	2/11/2019 9:51	112298602
5	136672969	2/12/2019 9:51	112298608
6	136672991	2/10/2019 9:51	112298690
7	136672999	2/11/2019 9:51	112298661	Repeat
8	136672810	2/11/2019 9:51	112298685
9	1366721010	2/15/2019 9:51	112298661

<tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula D2 to Dnnn =IF(COUNTIF($C3:$C$20,C2)<1,"",IF(ABS(B2-INDEX($B3:$B$20,MATCH(C2,$C3:$C$20,0)))<31,"Repeat","")) <tbody> </tbody>

<tbody>
</tbody>

It is working all correct.. Thanks

 

[Toadstool]

You're welcome!