Looking for formula to determine time elapsed between dates

[Grumpy88]

Hi.

I have a spreadsheet database consisting of a series of events, each of which has a unique identifying number and a date. I also have a list of names of people who have attended these events.

Is there a formula anyone can give me that will allow me quickly to determine the longest periods of time that have elapsed between a given person's attendance of any two consecutive events?

Thanks.

 

[Chihiro]

Can you upload sample with enough data? Not sure which column contains which data.

 

[Grumpy88]

I can, but it's nothing really that you can't replicate very quickly yourself - Column A has the event unique ID number (e.g. 1 - 300), Column B has the event date and Column C has the names.

The events are all unique (each has a different ID and date), but the same people appear multiple times - many of them attend the same event, and most of them attend multiple events over time.

I would then ideally like to create a pivot table for that data to list each person, see which events they have determined and then hopefully be able to sort it according to the longest time gaps between attended events. That's why I was thinking maybe a help column in the spreadsheet with that formula? I don't know.

Hope that helps.

 

[Chihiro]

Ok here's sample. However, this assumes Event ID is sequential (I.E. later event has larger ID#).

In D2:
=IFERROR(B2-LARGE(IF(($A$2:$A$17<A2)*($C$2:$C$17=C2),$B$2:$B$17),1),"")

Confirmed as array (CSE).

 

[Grumpy88]

That looks superb, thanks!

Am going to try it out in my spreadsheet now and will report back.

Really appreciated!

 

[Grumpy88]

Hi Chihiro.

Have tried out your formula and it works a treat thanks! I have been doing these calculations by hand up until now (besides being rather laborious, I was always worried that I'd overlook some instances), and the formula results are a perfect symmetry to those findings.

I'm looking forward to handling this job far faster (and more accurately) in future thanks to you! Thank you so much.