Count maximum no. of consecutive NULL OR BLANK cells between two date fields

[Millixcel]

I need to:

1. return the maximum number of consecutive blank or null cells between the start and end date
2. return the max date that corresponds to the end of the above sequence

Things to be aware of: There will sometimes be multiple occurrences of blank/null sequences.

I got close using Max, Frequency and Column but it went to pieces when trying to calculate between the two dates only.

Please see below where I have tried to use the MrExcel add in to provide example data

Excel forum example.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH
2	Return the max count of consecutive NULL OR BLANK values between the start and end dates
3	Return the max date which corresponds to the above sequence
4
5	START_DATE	END_DATE	CONSEC	END OF SEQUENCE	01/01/2018	01/02/2018	01/03/2018	01/04/2018	01/05/2018	01/06/2018	01/07/2018	01/08/2018	01/09/2018	01/10/2018	01/11/2018	01/12/2018	01/01/2019	01/02/2019	01/03/2019	01/04/2019	01/05/2019	01/06/2019	01/07/2019	01/08/2019	01/09/2019	01/10/2019	01/11/2019	01/12/2019	01/01/2020	01/02/2020	01/03/2020	01/04/2020	01/05/2020
6	03/11/2006	01/06/2020	3	01/06/2018	8	8	8				8	8	8	8	8	8	8	8	8	8	8	8	8	8	8	8	8	8	8	8	8	8	8
7	13/02/2007	22/05/2020	5	01/12/2018	10	10	10	10	10	10	10	0	0	0	0	0	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10
8	01/03/2007	01/06/2020	0		115	115	115	115	115	115	115	115	115	115	115	115	115	115	115	115	115	115	115	115	115	115	115	115	115	115	115	115	115
9	09/05/2007	01/06/2020	0		121.8	121.8	121.8	121.8	121.8	121.8	121.8	121.8	121.8	121.8	121.8	121.8	121.8	121.8	121.8	121.8	121.8	121.8	121.8	121.8	121.8	121.8	121.8	121.8	121.8	121.8	121.8	121.8	121.8
10	12/03/2007	01/06/2020	2	01/05/2020	51	51	51	51	51	51	51	51	51	51	51	51	51	51	51	51	51	51	51	51	51	51	51	51	51	51	51	0	0
example

Any assistance would be greatly appreciated before I lose any more of my already receding hairline.

 

[Peter_SSs]

Welcome to the MrExcel board!

Just wondering what result you would expect in the green cell for this example, and why?

Also, are the 'empty' cells in columns F, G, H, ... actually empty or do they contain formulas returning ""?

20 06 24.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N	O
5	START_DATE	END_DATE	CONSEC	END OF SEQUENCE	1/01/2018	1/02/2018	1/03/2018	1/04/2018	1/05/2018	1/06/2018	1/07/2018	1/08/2018	1/09/2018	1/10/2018
11	1/01/2018	1/10/2018	2		6			6			6			6
Sheet2 (2)

 

[Millixcel]

Hi, very good question! Should that instance occur then I would look to return the most recent time it happened.

 

[Millixcel]

for F,G,H they are empty, they are query results from access but will still be treated as a standard blank.

 

[Peter_SSs]

I would consider using some user-defined functions as below. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below (you can use the icon at the top right of the code pane below) into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formulas as shown in the screen shot below and copy down.
6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Function MaxConsec(rDateHeaders As Range, rValues As Range, rStartDate As Date, rEndDate As Date) As Long
  Dim c As Long, Consec As Long
  
  For c = 1 To rDateHeaders.Columns.Count
    If rDateHeaders(c).Value >= rStartDate And rDateHeaders(c).Value <= rEndDate Then
      If rValues(c).Value = 0 Then
        Consec = Consec + 1
        If Consec > MaxConsec Then MaxConsec = Consec
      Else
        Consec = 0
      End If
    End If
  Next c
End Function

Function LastDate(rDateHeaders As Range, rValues As Range, rStartDate As Date, rEndDate As Date) As Variant
  Dim c As Long, Consec As Long, MaxConsec As Long
  
  LastDate = "N/A"
  For c = 1 To rDateHeaders.Columns.Count
    If rDateHeaders(c).Value >= rStartDate And rDateHeaders(c).Value <= rEndDate Then
      If rValues(c).Value = 0 Then
        Consec = Consec + 1
        If Consec >= MaxConsec Then
          MaxConsec = Consec
          LastDate = rDateHeaders(c).Value
        End If
      Else
        Consec = 0
      End If
    End If
  Next c
End Function

Millixcel 2020-06-25 1.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH
5	START_DATE	END_DATE	CONSEC	END OF SEQUENCE	1/01/2018	1/02/2018	1/03/2018	1/04/2018	1/05/2018	1/06/2018	1/07/2018	1/08/2018	1/09/2018	1/10/2018	1/11/2018	1/12/2018	1/01/2019	1/02/2019	1/03/2019	1/04/2019	1/05/2019	1/06/2019	1/07/2019	1/08/2019	1/09/2019	1/10/2019	1/11/2019	1/12/2019	1/01/2020	1/02/2020	1/03/2020	1/04/2020	1/05/2020
6	3/11/2006	1/06/2020	3	1/06/2018	8	8	8				8	8	8	8	8	8	8	8	8	8	8	8	8	8	8	8	8	8	8	8	8	8	8
7	13/02/2007	22/05/2020	5	1/12/2018	10	10	10	10	10	10	10	0	0	0	0	0	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10
8	1/03/2007	1/06/2020	0	N/A	115	115	115	115	115	115	115	115	115	115	115	115	115	115	115	115	115	115	115	115	115	115	115	115	115	115	115	115	115
9	9/05/2007	1/06/2020	0	N/A	121.8	121.8	121.8	121.8	121.8	121.8	121.8	121.8	121.8	121.8	121.8	121.8	121.8	121.8	121.8	121.8	121.8	121.8	121.8	121.8	121.8	121.8	121.8	121.8	121.8	121.8	121.8	121.8	121.8
10	12/03/2007	1/06/2020	2	1/05/2020	51	51	51	51	51	51	51	51	51	51	51	51	51	51	51	51	51	51	51	51	51	51	51	51	51	51	51	0	0
11	1/03/2018	1/10/2018	2	1/09/2018	6			6			6			6
Sheet2 (2)

Cell Formulas
Range	Formula
D6:D11	D6	=MaxConsec(F$5:AH$5,F6:AH6,B6,C6)
E6:E11	E6	=LastDate(F$5:AH$5,F6:AH6,B6,C6)

 

[Millixcel]

I have one word. Hero!

I've tested this thoroughly and can't find a single issue!

I would have never gotten there in a million years without your help so thank you very much!

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.