Maximum # of days between 2 successive dates in a range (with a condition)

[virtuosok]

Hi,
Wondering if you can help me with this one...
I have the following set up:
1. Subject ID in cells E13:E1000 (this range contains either a number or is blank)
2. A condition in cells K13:K1000 (this is either text or a blank field)
3. A range of dd-mmm-yyyy dates in M13:M1000 (this range contains either dates or is blank; some of the dates are duplicated and some are missing so this is not a contiguous range)
In other words, each subject (listed in column E) typically has several dozen dates in column M, loosely associated with (meaning "being in the same row as") certain text in column K - or being there without the text in respective cell (K13, K14, K15...)

Looking for the formula which would do the following:
- for a given subject (referring to column E)...
- ...what would be the maximum # of days between any of the 2 successive dates (refer to column M)...
-...only considering the rows which have a specific "text" in column K and ignoring dates which don't have the associated "text" in that column...

 

[Eric W]

Maybe:

Book1
	A	B	C	D	E						K	L
12	ID	Text	Max between successive dates		ID						Text	Date
13	1	a	37		1						a	1-Jan
14					1						a	1-Jan
15					1						a
16											b	2-Feb
17					1						c
18					1						a	1-Feb
19					2						a
20					3						z	6-Jun
21					4
22
23					4
24					1						a	2-Apr
25					1						a	25-Feb
26					1						b
27
28					1						a	7-Feb
Sheet1

Cell Formulas
Range	Formula
C13	C13	=MAX(LARGE(IF((E13:E1000=A13)*(K13:K1000=B13)*(L13:L1000>0),L13:L1000),ROW(INDIRECT("1:"&COUNTIFS(E:E,A13,K:K,B13,L:L,">0")-1)))-LARGE(IF((E13:E1000=A13)*(K13:K1000=B13)*(L13:L1000>0),L13:L1000),ROW(INDIRECT("2:"&COUNTIFS(E:E,A13,K:K,B13,L:L,">0")))))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[virtuosok]

Thanks, this is close, but I'm afraid not quite what I need... looking at the table and formula above, I see that # of days between 18L and 24L (as two consecutive dates for conditions in cells A13 and B13) is 61, not 37
This is probably because I failed to mention that all dates for any given subject are incremental, i.e. if we have "02 Apr" for subject 1 noted anywhere in column L, all subsequent dates which follow below for that subject, cannot be earlier than "02 Apr" (i.e. cannot be "07 Feb" as in example above)... they will be either "02 Apr" or any date after that day.

Also, my actual text condition (B13) is a bit complex so probably easier to arrange the formula a bit differently, i.e. not trying to count all entries matching B13, but excluding entries matching B13 instead... More specifically, I would like to exclude all rows which contain a word "without" anywhere in column K, i.e. <>"*without*"

 

[Eric W]

The same basic formula will still work. If your dates are monotone increasing, you'll get the right answer. The discrepancy you noted was due to my manufactured example. It's worth pointing out that if you use the XL2BB tool (see the link in the reply box) to post examples, it will be easier to understand your request and thereby get a quicker, better answer.

See if this modification of the formula will work for you:

Book1
	A	B	C	D	E						K	L
12	ID		Max between successive dates		ID						Text	Date
13	1		31		1						a	1/1/2020
14					1						a	1/1/2020
15					1						a
16											without	2/2/2020
17					1						xx without
18					1						a	2/1/2020
19					2						a
20					3						yy without	6/6/2020
21					4
22
23					4
24					1						a	3/3/2020
25					1						a	3/18/2020
26					1						without qq
27
28					1						a	4/10/2020
29
Sheet1

Cell Formulas
Range	Formula
C13	C13	=MAX(LARGE(IF((E13:E1000=A13)*ISERROR(SEARCH("without",K13:K1000))*(L13:L1000>0),L13:L1000),ROW(INDIRECT("1:"&COUNTIFS(E:E,A13,K:K,"<>*without*",L:L,">0")-1)))-LARGE(IF((E13:E1000=A13)*ISERROR(SEARCH("without",K13:K1000))*(L13:L1000>0),L13:L1000),ROW(INDIRECT("2:"&COUNTIFS(E:E,A13,K:K,"<>*without*",L:L,">0")))))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[virtuosok]

Thank you Sir very much indeed...

 

[virtuosok]

...apologies, one more thing. Is there a way to make the last formula work if the dates (column L in the above example) are not formatted as such?

 

[virtuosok]

No worries, I have added a helper column with =VALUE set of formulas, all set!

 

[Eric W]

Glad you got it all squared away! Thanks for the feedback.