Search code examples
excelcsvdatetimegoogle-sheetsgoogle-docs

How to find longest daily streak inside csv datetime column

File: https://www.filemail.com/d/zvbqkjggbccvfzy

I have the following below data:

enter image description here

and I am trying to calculate the longest daily stream of 'Time Publishd' values. Such as in the below example, the longest stream is 1-5 days in a row where the date decremented by a value of 1 (08-11, 08-10, 08-09, etc). I am trying to take my data and get the longest daily streak of these datetime values, is it possible to compute this inside a csv program such as excel / google sheets using the following .csv file data:

Title,Visibility,Time Published
Wednesday Night Interlude (Sylvere Remix) - Drake,Public,2022-08-11 16:00:35 UTC
Wigs - Prefab Sprout [1985 Electronic / Rock],Public,2022-08-10 16:00:04 UTC
Righteous Rule (Vocal) - No Smoke [1991 House],Public,2022-08-09 05:53:02 UTC
Baila Que Baila - Salero [1984 Italo-Disco],Public,2022-08-08 16:00:01 UTC
BAD1,Public,2022-08-07 16:00:31 UTC
BAD2,Public,2022-08-03 16:00:26 UTC
BAD3,Public,2022-08-02 16:00:26 UTC
Solution

  • In Google Sheets, here's how you can do this:

    =ArrayFormula(LAMBDA(dates,1+MAX(LEN(SPLIT(JOIN(,--
IFERROR(dates-1=QUERY(dates,"offset 1",0))),0))))
(INDEX(SPLIT(C2:C," "),,1)))