如何在CSV的datetime列中计算最长每日连续记录streak？

示例数据 #

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

核心逻辑 #

统计有记录的日期中，最长的连续天数序列（只要当天有至少一条记录就算一天，日期依次相差1天即视为连续）。步骤分为：提取纯日期→去重→排序→计算连续天数。

通用前置操作（Excel/Google Sheets均适用） #

假设导入数据后，Time Published列在C列（从C2开始是第一条记录）：

1. 提取纯日期：新增D列，D2输入公式（下拉填充到所有行）：

   =DATEVALUE(LEFT(C2, 10))
   

   （注：因原日期格式为YYYY-MM-DD，取前10位即可得到纯日期）
2. 去重日期：选中D列 → 数据菜单 → 删除重复值（避免同一天多条记录干扰统计）
3. 排序日期：将去重后的D列按降序排列（从最新日期开始计算连续天数）

Excel 具体实现 #

方法1：辅助列（兼容所有Excel版本） #

1. 新增E列，E2输入1（第一条日期的连续天数初始为1）
2. E3输入公式（下拉填充到所有行）：

   =IF(D2 - D3 = 1, E2 + 1, 1)
   

   解释：如果上一个日期与当前日期相差1天（即连续），则在上一个连续天数基础上加1，否则重置为1
3. 计算最长连续天数：在任意空白单元格输入=MAX(E:E)，示例中结果为5

方法2：动态数组公式（仅Excel 365/2021） #

无需辅助列，直接用数组公式一步输出结果：

=LET(dates,SORT(UNIQUE(DATEVALUE(LEFT(C2:C8,10))),,-1),MAX(SCAN(1,dates,LAMBDA(x,y,IF(INDEX(dates,ROW(x)-1)-y=1,x+1,1)))))

Google Sheets 具体实现 #

方法1：辅助列（直观易懂） #

操作逻辑与Excel辅助列完全一致：

1. 新增E列，E2输入1
2. E3输入公式（下拉填充）：

   =IF(D2 - D3 = 1, E2 + 1, 1)
   

3. 用=MAX(E:E)得到最长连续天数

方法2：数组公式（一步到位） #

无需辅助列，直接输入：

=LET(dates,SORT(UNIQUE(DATEVALUE(LEFT(C2:C8,10))),1,FALSE),MAX(SCAN(1,dates,LAMBDA(x,y,IF(INDEX(dates,ROW(x)-1)-y=1,x+1,1)))))

内容的提问来源于stack exchange，提问作者Martin