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

J

jmazorra

Well-known Member
Joined
Mar 19, 2011
Messages
715
Hello:

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

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

The formula on the last column is:
Code: 
=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:


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

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Sort by date Sort by votes
=IF(COUNTIF($A$10:A10,A10)=1,NETWORKDAYS(I10,J10),"")

Copy down.
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Solved
Help Please
2 3
Replies
21
Views
405
Asbestos_Jen
Asbestos_Jen
R
  • Solved
Age Group Ranging by gender
Replies
2
Views
231
Raj1979
R
R
  • Solved
transpose data with same value in column A by date order
Replies
5
Views
389
Fluff
Fluff
sharky12345
  • Solved
Issue trying to identify earliest date & time
2
Replies
15
Views
496
sharky12345
sharky12345
N
  • Solved
Formula For Entering Loan Repayment On The Same Day For Each Month
Replies
4
Views
400
Dave Patton
D

Forum statistics

Threads
1,223,227
Messages
6,170,847
Members
452,361
Latest member
d3ad3y3

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