How to find longest daily streak inside csv datetime column-CodePudding

File:

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

CodePudding user response：

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)))

CodePudding user response：

One way is to use frequency():

=arrayformula( 
  lambda( 
    timestamps, 
    lambda( 
      dates, nextDates, 
      1   max( 
        frequency( 
          if(dates - 1 = nextDates, row(timestamps)), 
          if(dates - 1 <> nextDates, row(timestamps)) 
        ) 
      ) 
    )( 
      iferror(datevalue(left(timestamps, 10))), 
      iferror(datevalue(left({ offset(timestamps, 1, 0); "" }, 10))) 
    ) 
  )( 
    C2:C 
  ) 
)