Difference between First and Last date for same event

James01

Board Regular
Joined
Oct 29, 2008
Messages
124
Hi

I have data in 3 columns as shown in the image below.

Datedifference.PNG


Now what I am trying to do is to find the difference between dates for the first time a code occurs and the last time it occurs.

For example the code "13193875" occurs 3 times first on 4-march-08 and last on 19-march-08. The required answer in this case is 15.



Thanks for your help.
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
TRy

=MAX(INDEX((A1:A11=3193875)*C1:C11,,1))-MIN(INDEX(((A1:A11=3193875)*C1:C11)+((A1:A11<>3193875)*1E+100),,1))
 
Upvote 0
For unsorted data the following array formula works:
=MAX(IF(A2:A11=13193875,C2:C11))-MIN(IF(A2:A11=13193875,C2:C11))

Note: use Ctrl-Shift-Enter for entering of array formula.
 
Last edited:
Upvote 0
Hi All

Thanks for replies, I'll try them out.

Barry your suggestion looks very interesting and I am sure will result in faster calculation as I have a very large data set.

The only difference I see is that the first part is an exact match while the second is not. How does excel know which one is the max or min value?
 
Upvote 0
MATCH without zero will find the largest row with a value less than or equal to that value, which means the last instance of that value. Because the dates are sorted in descending order, the last item is the MIN date.

MATCH with 0 finds the first instace, which is the MAX because of your date sort order.
 
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,064
Members
448,545
Latest member
kj9

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