Difference in date

V

vmjan02

Well-known Member
Joined
Aug 15, 2012
Messages
1,059
Office Version
  1. 365
  2. 2021
  3. 2019
  4. 2016
  5. 2013
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_IDINCIDENT_CREATION_DATEPROJECT_NUMBERResult
13667296202-11-2019 09:51:01112298608Repeat
13667252202-11-2019 09:51:01112298601
13667258102-11-2019 09:51:01112298602
13667296902-12-2019 09:51:01112298608
13667299102-10-2019 09:51:01112298690
13667299902-11-2019 09:51:01112298661Repeat
13667281002-11-2019 09:51:01112298685
136672101002-15-2019 09:51:01112298661

<tbody>
</tbody>
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Sort by date Sort by votes
Change the $20 to whatever is your maximum row.

ABCD
1INCIDENT_IDINCIDENT_CREATION_DATEPROJECT_NUMBERResult
21366729622/11/2019 9:51112298608Repeat
31366725222/11/2019 9:51112298601
41366725812/11/2019 9:51112298602
51366729692/12/2019 9:51112298608
61366729912/10/2019 9:51112298690
71366729992/11/2019 9:51112298661Repeat
81366728102/11/2019 9:51112298685
913667210102/15/2019 9:51112298661

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

Worksheet Formulas
CellFormula
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>
 
Upvote 0
Toadstool said:
Change the $20 to whatever is your maximum row.

ABCD
1INCIDENT_IDINCIDENT_CREATION_DATEPROJECT_NUMBERResult
21366729622/11/2019 9:51112298608Repeat
31366725222/11/2019 9:51112298601
41366725812/11/2019 9:51112298602
51366729692/12/2019 9:51112298608
61366729912/10/2019 9:51112298690
71366729992/11/2019 9:51112298661Repeat
81366728102/11/2019 9:51112298685
913667210102/15/2019 9:51112298661

<tbody>
</tbody>
Sheet1​

Worksheet Formulas
CellFormula
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>
Click to expand...

It is working all correct.. Thanks
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question
Count out of date record
Replies
5
Views
611
Fluff
Fluff

Forum statistics

Threads
1,214,940
Messages
6,122,356
Members
449,080
Latest member
Armadillos

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top