'Finding rows with matching id's and the using the date fields in each row to find the time difference in days between them

I have rows of data where up to 3 id's match. I need to try and find the number of days between each of the rows with matching id's using the date field. I am looking to find the number of days between each row in date sequence. So between the first and second row in date sequence, or second and third row but not the first and third row.

So a simplified version of my rows can be seen in the attached image.

image showing 2 ID's with multiple entries. John Smith (highlight yellow) who has 3 entries, two on the same day and one later and Bill Wright who has just two entries on separate days.

The results I would be looking for would ideally be shown in column 4 of the image.

Is there any way I can do this in a formula or pivot table? (basically avoiding VBA) Any suggestions hugely appreciated! Thanks.

Solution 1:^[1]

This array formula

=IFERROR(B2-INDEX($B$1:$B$9,MAX((A2=$A$1:A1)*ROW($A$1:A1))),0)

will work as long as your data are sorted by date (which your screenshot indicates they are):

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source
Solution 1	Spectral Instance

'Finding rows with matching id's and the using the date fields in each row to find the time difference in days between them

Solution 1:[1]

Sources

Related Questions

Solution 1:^[1]