Google Sheets count consecutive dates

Question

I have a Google Sheet with a list of dates & times like this:

10/24/2019 4:25:00
10/29/2019 4:33:22
10/30/2019 4:33:11
10/31/2019 4:24:11
11/1/2019 4:36:11
11/2/2019 5:21:37
11/4/2019 5:30:20
11/6/2019 5:21:28
11/7/2019 5:27:07
11/8/2019 5:22:34
11/10/2019 8:51:13
11/11/2019 5:26:27
11/11/2019 11:41:27
11/12/2019 5:27:49
11/13/2019 5:30:34

I am looking for a way to figure out the longest "streak" of consecutive dates where there was at least one entry. The data can be unsorted or sorted.

Answer 1

Solution without google app scripts

1) sort your unique dates

=arrayformula(query(unique(datevalue(A:A)),"select * where Col1 is not null"))

2) mark the days that are streak days with a 1

=arrayformula(if(F:F="","",{0;if(F2:F-F1:F=1,1,0)}))

3) count the streaks

=max(arrayformula(if(G:G="","",frequency(if(G:G,row(G:G)),if(not(G:G),row(G:G))))))

Note: in my example column A is the original dates, column F is my sorted dates, column G is my streak marker. You can also combine step 2 and 3 into one bigger formula =arrayformula(frequency(if(if(F:F="","",{0;if(F2:F-F1:F=1,1,0)}),sequence(rows(if(F:F="","",{0;if(F2:F-F1:F=1,1,0)})))),if(not(if(F:F="","",{0;if(F2:F-F1:F=1,1,0)})),sequence(rows(if(F:F="","",{0;if(F2:F-F1:F=1,1,0)}))))))

Answer 2

You could create a google app script function like this:

function BIGGEST_CONSECUTIVE_DATE_STREAK() {
  const activeSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const range = activeSheet.getActiveRange();
  var currentDate;
  var newDate;
  var biggestStreak = 0;
  var currentStreak = 0; 

  // Loop through all values in the selected range
  for (i = 0; i < range.getValues().length - 2; i++) {
    currentDate = new Date(range.getValues()[i])
    newDate = new Date(range.getValues()[i+1])

    if (currentDate.getMonth == newDate.getMonth) {  
      if (newDate.getDate() - currentDate.getDate() == 1) {
        // If dates are consecutive in terms of days, 
        // the consecutive date streak increases.
        currentStreak++;
      } else {
        // Otherwise the streak ends.
        currentStreak = 0;
      }
    }
    // Update the biggest number of consecutive days.
    if (currentStreak > biggestStreak) biggestStreak = currentStreak;  
  } 
  // Alert the biggest consecutive date streak. 
  // Change this line depening on your use case.
  Browser.msgBox(biggestStreak, Browser.Buttons.OK_CANCEL);
}

I presumed you meant by 'consecutive dates' consecutive as in days.

Usage: select the dates in google sheets and execute this script.

Go here for the documentation on how to create a custom function and link it to your google sheet if you don't know how to execute the script.