google sheetsgoogle-sheets-dates
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.
It seems that this is possible to do in a Google spreadsheet now.
If I enter the data from my question, highlight it and select insert chart from the menu, Google Spreadsheet will greate a trend graph with the points laid out correctly.
If the row you are using for labels contains dates, the spreadsheet supposes that you need an accurate representation of time. So it arranges the data chronologically and marks the time axis at equal time intervals:
If you want the specific dates to be used as labels, this means they should be used like text labels. Select the top row and apply Format->Number->Plain Text:
Now the columns are labeled with the text from the top row, and they are presented in the order of appearance in the spreadsheet.
Best Answer
Solution without google app scripts
1) sort your unique dates
2) mark the days that are streak days with a 1
3) count the streaks
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)}))))))