Google Sheets含空单元格的日期间隔求和问题

问题背景 #

原公式=LAMBDA(rg,SUMPRODUCT(rg-OFFSET(rg,0,-1)))(INDEX(1:1,2):INDEX(1:1,COUNTA(1:1)))仅适用于连续非空数字的相邻差值求和，处理带空单元格的日期列时，空单元格会被解析为日期数值0（对应1899-12-30），导致计算结果错误。

解决方案公式 #

针对单行（以第2行的B2:G2为例），使用以下公式：

=LAMBDA(dates, IFERROR(SUM(INDEX(dates,2):INDEX(dates,ROWS(dates)) - INDEX(dates,1):INDEX(dates,ROWS(dates)-1)), 0))(FILTER(B2:G2, B2:G2<>""))

公式逻辑拆解 #

• FILTER(B2:G2, B2:G2<>"")：提取当前行中所有非空的日期，生成纯净的日期数组
• LAMBDA(dates, ...)：将提取到的日期数组命名为dates，简化后续引用
• INDEX(dates,2):INDEX(dates,ROWS(dates))：取数组中从第2个到最后一个的日期
• INDEX(dates,1):INDEX(dates,ROWS(dates)-1)：取数组中从第1个到倒数第2个的日期
• 两组日期相减得到相邻日期间隔的天数数组，再用SUM求和
• IFERROR(..., 0)：处理无有效日期或仅单个日期的情况，返回0避免错误

示例验证 #

• 第一行示例（B2:G2）：提取的日期数组为[2022-07-06, 2022-07-07, 2022-07-08, 2022-07-09]，差值求和为1+1+1=3，与期望输出一致
• 第四行示例（B4:G4）：提取的日期数组为[2022-07-06, 2022-07-07]，差值求和为1，与期望输出一致

批量应用 #

将公式输入到结果列的第一个单元格（如A2），下拉填充即可批量处理所有行。

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