# Difference between First and Last date for same event

#### James01

##### Board Regular
Hi

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

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

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
TRy

=MAX(INDEX((A1:A11=3193875)*C1:C11,,1))-MIN(INDEX(((A1:A11=3193875)*C1:C11)+((A1:A11<>3193875)*1E+100),,1))

Try like this

=INDEX(C:C,MATCH(13193875,A:A,0))-INDEX(C:C,MATCH(13193875,A:A))

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:
ADVERTISEMENT
Try like this

=INDEX(C:C,MATCH(13193875,A:A,0))-INDEX(C:C,MATCH(13193875,A:A))

That is nice thinking, but it does rely on the dates being sorted in descending order. I know the example shows it thus, but ...

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?

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.

Replies
0
Views
412
Replies
0
Views
547
Replies
1
Views
629
Replies
2
Views
288
Replies
12
Views
586

Threads
1,196,042
Messages
6,013,047
Members
441,746
Latest member
ArtemisAlex

### 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

### 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