Find next closest date to date in first record by record set

[JRBradford]

Hello!

The data shown below has been sorted ascending by Case Number then by Last Modified Date to create an ordered record set for each case number. I need to get the next closest date/time in Last Modified Date to Row 1 of that same column for each ordered record set and return a “Null” if a row does not meet the next closest date/time condition. I'm not sure how to go about this using the Last Modified Date column as a relative search parameter. I do not use VBA, so I need to use formulas if possible. A sample of my data is below as well as a sample of the desired result (based on the first record set).

RowNum	Case Number	Last Modified By	Last Modified Date	Date/Time Opened	Duration
1	1111	System	3/1/2018 2:52	3/1/2018 2:52	0
2	1111	System	3/1/2018 2:52	3/1/2018 2:52	10.50083
3	1111	Member5	3/1/2018 13:22	3/1/2018 2:52	16.52694
4	1111	Member2	3/2/2018 5:53	3/1/2018 2:52	1.5175
5	1111	Member5	3/2/2018 7:24	3/1/2018 2:52	172.6678
6	1111	Member5	3/9/2018 12:04	3/1/2018 2:52	1774.684
1	2222	System	3/1/2018 3:26	3/1/2018 3:26	0.000278
2	2222	System	3/1/2018 3:26	3/1/2018 3:26	9.934167
3	2222	Member5	3/1/2018 13:22	3/1/2018 3:26	16.52694
4	2222	Member2	3/2/2018 5:53	3/1/2018 3:26	1.487222
5	2222	Member5	3/2/2018 7:22	3/1/2018 3:26	172.6981
6	2222	Member5	3/9/2018 12:04	3/1/2018 3:26	1774.684

<tbody>
</tbody>

Desired result:

RowNum	Case Number	Last Modified By	Last Modified Date	Date/Time Opened	Duration	First Touch
1	1111	System	3/1/2018 2:52	3/1/2018 2:52	0	Null
2	1111	System	3/1/2018 2:52	3/1/2018 2:52	10.50083	Null
3	1111	Member5	3/1/2018 13:22	3/1/2018 2:52	16.52694	3/1/2018 13:22
4	1111	Member2	3/2/2018 5:53	3/1/2018 2:52	1.5175	Null
5	1111	Member5	3/2/2018 7:24	3/1/2018 2:52	172.6678	Null
6	1111	Member5	3/9/2018 12:04	3/1/2018 2:52	1774.684	Null
1	2222	System	3/1/2018 3:26	3/1/2018 3:26	0.000278	Null
2	2222	System	3/1/2018 3:26	3/1/2018 3:26	9.934167	Null
3	2222	Member5	3/1/2018 13:22	3/1/2018 3:26	16.52694	3/1/2018 13:22
4	2222	Member2	3/2/2018 5:53	3/1/2018 3:26	1.487222	Null
5	2222	Member5	3/2/2018 7:22	3/1/2018 3:26	172.6981	Null
6	2222	Member5	3/9/2018 12:04	3/1/2018 3:26	1774.684	Null

<tbody>
</tbody>

Thank you so much for any help you can provide! Have a great day!

 

[mse330]

Hello & welcome to the forum

Assuming your data starts in column A, try the below formula in column F

=IF(MINIFS(C:C,A:A,A2,B:B,"<>System")=C2,C2,"Null")

 

[JRBradford]

Perfect! Thank you so much!

 

[mse330]

Glad I could help & thanks for reporting back