Find the date difference between 2 dates, but only return the smallest value

[jmazorra]

Hello:

I know the title is a bit confusing, but here is my dilemma. I have the following sheet

Requisition ID	Applicant Candidate Status	Requisition Date Opened	Applicant Submission Date	Scheduled Interview - Date	Avg Days To First Candidate
req13	In Review	04/18/2014 15:48	04/19/2014 05:04
req13	Closed	04/18/2014 15:48	04/19/2014 07:14
req13	In Review	04/18/2014 15:48	04/26/2014 22:45
req13	New Submission	04/18/2014 15:48	05/06/2014 19:43
req13	New Submission	04/18/2014 15:48	05/08/2014 20:23
req13	New Submission	04/18/2014 15:48	05/10/2014 02:33
req13	Closed	04/18/2014 15:48	06/16/2014 20:28
req13	New Submission	04/18/2014 15:48	06/22/2014 03:21		46
req14	Closed	04/18/2014 20:06	04/19/2014 06:46
req14	In Review	04/18/2014 20:06	04/23/2014 17:56
req14	In Review	04/18/2014 20:06	04/24/2014 21:41
req14	In Review	04/18/2014 20:06	04/25/2014 15:29
req14	New Submission	04/18/2014 20:06	04/29/2014 01:15
req14	Closed	04/18/2014 20:06	04/29/2014 13:30
req14	Closed	04/18/2014 20:06	05/01/2014 22:12
req14	New Submission	04/18/2014 20:06	05/06/2014 19:48
req14	New Submission	04/18/2014 20:06	05/08/2014 20:19
req14	New Submission	04/18/2014 20:06	05/30/2014 02:07
req14	Closed	04/18/2014 20:06	06/21/2014 18:42		46
req15	Closed	04/18/2014 20:59	04/21/2014 11:00
req15	Closed	04/18/2014 20:59	04/21/2014 13:08
req15	Closed	04/18/2014 20:59	04/21/2014 15:19
req15	Closed	04/18/2014 20:59	04/22/2014 20:14
req15	Closed	04/18/2014 20:59	04/24/2014 17:41
req15	Closed	04/18/2014 20:59	04/26/2014 11:03

<tbody>
</tbody>

The formula on the last column is:

=IF(A10<>A11,NETWORKDAYS(I10,J10),"")

Essentially, I want to add the two dates but only the first row of where the req number is different. With the formula above is only adding the last row, I want the first row. It would look like this:

Requisition ID	Applicant Candidate Status	Requisition Date Opened	Applicant Submission Date	Scheduled Interview - Date	Avg Days To First Candidate
req13	In Review	04/18/2014 15:48	04/19/2014 05:04		1
req13	Closed	04/18/2014 15:48	04/19/2014 07:14
req13	In Review	04/18/2014 15:48	04/26/2014 22:45
req13	New Submission	04/18/2014 15:48	05/06/2014 19:43
req13	New Submission	04/18/2014 15:48	05/08/2014 20:23
req13	New Submission	04/18/2014 15:48	05/10/2014 02:33
req13	Closed	04/18/2014 15:48	06/16/2014 20:28
req13	New Submission	04/18/2014 15:48	06/22/2014 03:21
req14	Closed	04/18/2014 20:06	04/19/2014 06:46		1
req14	In Review	04/18/2014 20:06	04/23/2014 17:56
req14	In Review	04/18/2014 20:06	04/24/2014 21:41
req14	In Review	04/18/2014 20:06	04/25/2014 15:29
req14	New Submission	04/18/2014 20:06	04/29/2014 01:15
req14	Closed	04/18/2014 20:06	04/29/2014 13:30
req14	Closed	04/18/2014 20:06	05/01/2014 22:12
req14	New Submission	04/18/2014 20:06	05/06/2014 19:48
req14	New Submission	04/18/2014 20:06	05/08/2014 20:19
req14	New Submission	04/18/2014 20:06	05/30/2014 02:07
req14	Closed	04/18/2014 20:06	06/21/2014 18:42
req15	Closed	04/18/2014 20:59	04/21/2014 11:00		2
req15	Closed	04/18/2014 20:59	04/21/2014 13:08
req15	Closed	04/18/2014 20:59	04/21/2014 15:19

<tbody>
</tbody>

 

[Scott Huish]

=IF(COUNTIF($A$10:A10,A10)=1,NETWORKDAYS(I10,J10),"")

Copy down.

 

[jmazorra]

=IF(COUNTIF($A$10:A10,A10)=1,NETWORKDAYS(I10,J10),"")

Copy down.

Excellent, thank you