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

virtuosok

Board Regular
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...

Last edited:

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Eric W

MrExcel MVP
Maybe:

Book1
ABCDEKL
12IDTextMax between successive datesIDTextDate
131a371a1-Jan
141a1-Jan
151a
16b2-Feb
171c
181a1-Feb
192a
203z6-Jun
214
22
234
241a2-Apr
251a25-Feb
261b
27
281a7-Feb
Sheet1
Cell Formulas
RangeFormula
C13C13=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

Board Regular
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

MrExcel MVP
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
ABCDEKL
12IDMax between successive datesIDTextDate
131311a1/1/2020
141a1/1/2020
151a
16without2/2/2020
171xx without
181a2/1/2020
192a
203yy without6/6/2020
214
22
234
241a3/3/2020
251a3/18/2020
261without qq
27
281a4/10/2020
29
Sheet1
Cell Formulas
RangeFormula
C13C13=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

Board Regular

Thank you Sir very much indeed...

virtuosok

Board Regular
...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?

Attachments

• Capture7.PNG
41.6 KB · Views: 3

virtuosok

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

Eric W

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

Replies
8
Views
113
Replies
4
Views
164
Replies
3
Views
30
Replies
3
Views
130
Replies
4
Views
253

1,127,063
Messages
5,622,470
Members
415,897
Latest member
Barry18180

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back