Calculate interval between dates based on input at another columns

[nburaq]

Hi,

I would like to try to find interval between two dates based on the material number. I figured out difference between dates in terms of dates with this formula =DATEDIF(MIN(B2:B17);MAX(B2:B17);"d") but could not manage it to combine with filter.(or any other formula that will help to solve this) here is the simple date:

Mat	date	Interval (days)
123456	16/03/2018​
123456	26/04/2018​
123456	09/05/2018​
123456	14/06/2018​
123456	15/06/2018​
888888	06/08/2018​
123456	27/08/2018​
123456	26/03/2019​
999999	24/03/2020​
123456	24/03/2020​
123456	25/03/2020​
123456	16/11/2020​
123456	07/12/2020​
123456	07/12/2020​
123456	07/12/2020​
123456	07/12/2020​

so basically result of mat 123456 in column interval will be seen as 997 days and if there is no repeating material, then reference date will be 25/02/2024. In this case for mat 888888 will be 2029 days.

Thanks for your help!

 

[nburaq]

Solved myself , by adding d1 25/02/2024 as static value and formula is :
=IF(COUNTIF($A$2:$A$17;$A2)<=1;$D$1-MINIFS($B$2:$B$17;$A$2:$A$17;$A2);MAXIFS($B$2:$B$17;$A$2:$A$17;$A2)-MINIFS($B$2:$B$17;$A$2:$A$17;$A2))

Thanks again

 

[awoohaw]

Here is a short formula, i'm sure there are others:

Do you want big negative numbers in Rows 8 and 11?
Look at formula in column C.

Book1
	A	B	C	D
1	Mat	date	Interval (days)	Your Formula
2
3	123456	2018-03-16	997	997
4	123456	2018-04-26	997	997
5	123456	2018-05-09	997	997
6	123456	2018-06-14	997	997
7	123456	2018-06-15	997	997
8	888888	2018-08-06	0	-43318
9	123456	2018-08-27	997	997
10	123456	2019-03-26	997	997
11	999999	2020-03-24	0	-43914
12	123456	2020-03-24	997	997
13	123456	2020-03-25	997	997
14	123456	2020-11-16	997	997
15	123456	2020-12-07	997	997
16	123456	2020-12-07	997	997
17	123456	2020-12-07	997	997
18	123456	2020-12-07	997	997
Sheet2

Cell Formulas
Range	Formula
C3:C18	C3	=MAXIFS($B$3:$B$18,$A$3:$A$18,A3)-MINIFS($B$3:$B$18,$A$3:$A$18,A3)
D3:D18	D3	=IF( COUNTIF($A$3:$A$18,$A3)<=1, $D$2-MINIFS($B$3:$B$18,$A$3:$A$18,$A3), MAXIFS($B$3:$B$18,$A$3:$A$18,$A3)-MINIFS($B$3:$B$18,$A$3:$A$18,$A3))