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

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

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,114,360
Messages
5,547,469
Members
410,797
Latest member
mlfuson
Top