Google Sheets按月份汇总相同月份对应时长的公式求解

前提假设 #

• Records表3列依次为Entry Date（A列）、Exit Date（B列）、Duration（C列），有效数据从第2行开始
• 表2的月份表头位于第1行，B1为Jan、C1为Feb……M1为Dec，总时长统计行位于第2行，B2对应1月总时长

基础实现公式（匹配示例场景） #

如果所有记录的入场、离场日期都属于同一个月，直接在B2单元格输入以下公式，向右拖拽填充到M2即可得到所有月份的总时长：

=SUMPRODUCT((MONTH(Records!$A$2:$A)=MONTH(B1&1))*Records!$C$2:$C)

公式逻辑说明 #

• MONTH(Records!$A$2:$A)：提取Records表所有入场日期的月份数值
• MONTH(B1&1)：将当前列的月份缩写（如Jan）识别为日期，提取对应月份数值
• 筛选出入场日期属于当前月份的所有记录，对时长列求和得到结果

进阶实现公式（兼容跨月记录） #

如果存在入场、离场跨月份的记录，需要统计实际落在当月的时长，不需要提前计算Duration列也可以直接用以下公式：

=SUMPRODUCT(IF(Records!$A$2:$A="",0,MAX(0,MIN(EOMONTH(B1&1,0),Records!$B$2:$B)-MAX(B1&1,Records!$A$2:$A)+1)))

公式逻辑说明 #

• 逐行计算单条记录与当前月份的日期重叠区间
• 对所有重叠天数求和，自动适配跨月时长拆分场景

常用操作技巧 #

• 若需要固定统计年份，可将公式中的B1&1修改为B1&2021，避免系统自动识别年份出错
• 若Records表数据量较大，可将整列范围替换为固定行数范围（如$A$2:$A$1000），提升计算效率
• 若公式返回#VALUE!错误，先检查Records表的日期列是否为标准日期格式，可通过「格式→数字→日期」调整

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