Difference between two dates for one unique value thats repeats itself

Hello everyone, my goal is a formula if S2 (and following values in S) matches any value in column B, then give me the difference in years and months from T2 and matching cell from column L belong to the matching value in B

The B column values are all from the same unique ID that have procedures in different dates. The idea is to get years and months since they've joined (column T) in all the different procedures. So say in the example I've done 1111 which joined on the 09/04/2018 is 5y and 8 months on the procedure dated 18/12/2023. It would be 5y and 6 months for the previous procedure on the 18/10/2023

So each value on S only has one unique date of joining (column T) but have different or equal procedures dates. Do let me know if I didn't explained correctly.

Do let me know if I haven't explained correctly or if you need any more information

Thanks!

Test11.xlsx

Fast answers need visual help. Please read the yellow banner at the top of this page on how to attach a file and a mocked up solution.

Try this in R2 copied down:

=LET(d,INDEX($L$2:$L$16,MATCH(S2,$B$2:$B$16,0)),IF(ISNA(d),"",DATEDIF(T2,d,"y")&" years, "&DATEDIF(T2,d,"ym")&" months"))

Not clear which date you want from column L if there are multiples ...

If the max:

=LET(d,MAXIFS($L$2:$L$16,$B$2:$B$16,S2),IF(ISNA(d),"",DATEDIF(T2,d,"y")&" years, "&DATEDIF(T2,d,"ym")&" months"))

If the min:

=LET(d,MINIFS($L$2:$L$16,$B$2:$B$16,S2),IF(ISNA(d),"",DATEDIF(T2,d,"y")&" years, "&DATEDIF(T2,d,"ym")&" months"))

Is this what you want?

AliGW on MS365 Beta Channel (Windows 11) 64 bit

	V	W	X	Y
1	1111	2222	3333	4444
2	5 years, 6 months	8 years, 7 months	6 years, 8 months	9 years, 11 months
3	5 years, 8 months	8 years, 9 months	6 years, 11 months	9 years, 11 months
4		9 years, 3 months	7 years, 1 months
5		10 years, 4 months	10 years, 3 months

Sheet: Sheet1

If not, please mock up in FULL what you want to see in the sample workbook.

Or, in C2 copied down:

=LET(d,INDEX($T$2:$T$5,MATCH(B2,$S$2:$S$5,0)),IF(ISNA(d),"",DATEDIF(d,L2,"y")&" years, "&DATEDIF(d,L2,"ym")&" months"))

AliGW on MS365 Beta Channel (Windows 11) 64 bit

	B	C
2	1111	5 years, 8 months
3	1111	5 years, 6 months
4	1111	5 years, 8 months
5	1111	5 years, 6 months
6	2222	10 years, 4 months
7	2222	9 years, 3 months
8	2222	8 years, 9 months
9	2222	8 years, 7 months
10	3333	7 years, 1 months
11	3333	6 years, 11 months
12	3333	6 years, 8 months
13	3333	10 years, 3 months
14	4444	9 years, 11 months
15	4444	9 years, 11 months
16	5555

Sheet: Sheet1

Look at post #8 - it does the same is DJ's in a slightly more efficient way.

Glad to have helped.

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, remember that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.

You're welcome.

Yes, next time mock up WHAT you waht to see WHERE you want to see it.

Will do AliGW, and still in this topic, could you please still help me at another formula, which would give me the max date for every ID? As in:

Max date for 1111 - 5y 8 months
Max date for 2222 - 10 years 4 months
Max date for 3333 - 10 years 3 months

You've already got that in my workbook.

AliGW on MS365 Beta Channel (Windows 11) 64 bit

	Q
1	Max
2	5 years, 8 months
3	10 years, 4 months
4	10 years, 3 months
5	9 years, 11 months

Sheet: Sheet1