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

virtuosok

Board Regular
Joined
Sep 2, 2020
Messages
92
Office Version
  1. 2016
Platform
  1. Windows
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:

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,751
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
Joined
Sep 2, 2020
Messages
92
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
Aug 18, 2015
Messages
10,751
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.
 
Solution

virtuosok

Board Regular
Joined
Sep 2, 2020
Messages
92
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Thank you Sir very much indeed...
 

virtuosok

Board Regular
Joined
Sep 2, 2020
Messages
92
Office Version
  1. 2016
Platform
  1. Windows
...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
    Capture7.PNG
    41.6 KB · Views: 3

virtuosok

Board Regular
Joined
Sep 2, 2020
Messages
92
Office Version
  1. 2016
Platform
  1. Windows
No worries, I have added a helper column with =VALUE set of formulas, all set!
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,751
Glad you got it all squared away! Thanks for the feedback. :cool:
 

Watch MrExcel Video

Forum statistics

Threads
1,127,500
Messages
5,625,157
Members
416,075
Latest member
TechJosh

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top