Calculating days between two oldest dates on varying amount of date values

Question

I'm trying to calculate a number of days between two oldest days. The task would be easy, if it were only two dates, however - there could be 3 or 4 dates - I need to take the oldest and the earliest and just subtract them, however the challenge is varying amount of dates per ID.

I've got a pivot table where on the A column I have the ID and on the B column I have multiple rows of sorted dates for that ID. Example of Table (also adding a picture for reference):

Column A	Column B
ID 1	Date 1
	Date 2
ID 2	Date 3
	Date 4
	Date 5
ID 3	Date 6
	Date 7

Example: For ID 1 (in Column A) - to get the result that I need - I would do Date 2 - Date 1 Example: for ID 2 (in Column A) - to get the result that I need - I would have to do Date 5 - Date 3

Is there an easy way to do it in Excel? Or would I have to result to a programming language - putting IDs into Objects and storing all the dates into arrays and then manipulating it in arrays?

I've tried finding suitable functions in Excel and reading more about Pivot tables, but with no luck. Maybe someone can put in me on the right path.

Answer 1

"=(MAX(FILTER(B:B;A:A=A1)))-MIN(FILTER(B:B;A:A=A1))"
This solution works with Excel 365.
Filter function acts as a VLOOKUP function, but it can actually store multiple values and you can find Minimum and Maximum values for each ID and subtract them .

Answer 2

If you are using Excel 365 you can use the following formulas.

If not, you have to replace the formula in D4 with a unique list of your IDs. And you will have to drag down the formulas for column E and F.

Using MINIFS/MAXIFS then retrieves the min/max dates of a single ID