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

JRBradford

New Member
Joined
May 23, 2018
Messages
2
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!

 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

mse330

Well-known Member
Joined
Oct 18, 2007
Messages
697
Office Version
  1. 365
Platform
  1. Windows
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")
 

mse330

Well-known Member
Joined
Oct 18, 2007
Messages
697
Office Version
  1. 365
Platform
  1. Windows
Glad I could help & thanks for reporting back :)
 

Watch MrExcel Video

Forum statistics

Threads
1,109,510
Messages
5,529,278
Members
409,859
Latest member
emperorgenghiskhan
Top