如何用Google Sheets内置函数计算单单元格多段考勤时长总和

问题背景 #

• 原使用自定义函数计算员工单日打卡总工时，例如单元格内容为11 AM - 12PM, 1 PM-2 PM时输出2，但该函数存在复制表格时代码不同步、随机停止运行的问题，涉及1500+兼职员工、20+表格，人工修复耗时耗力。
• 单段时长可通过公式minus(index(split($A2,"-"),1,2),index(split($A2,"-"),1,1))*24计算出结果（如11 AM - 12 PM会得到1），但尝试用arrayformula(sum(minus(index(split(split($A5,","),"-"),1,2),index(split(split($A5,","),"-"),1,1))*24))实现多段求和时，仅能计算第一段时长，结果为1，且因表格结构限制，无法将时长拆分到多个单元格做中间步骤。

解决方案公式 #

使用以下公式即可在单个单元格内完成多段时长的自动求和：

=SUM(ARRAYFORMULA(IFERROR((INDEX(SPLIT(TRIM(SPLIT(A2, ",")), " - "),,2) - INDEX(SPLIT(TRIM(SPLIT(A2, ",")), " - "),,1))*24)))

公式说明 #

1. SPLIT(A2, ",")：将单元格内的多段时长拆分为独立的数组元素（如把11 AM - 12PM, 1 PM-2 PM拆分为11 AM - 12PM和1 PM-2 PM）
2. TRIM(...)：去除每段时长前后的多余空格，避免格式干扰
3. SPLIT(..., " - ")：将每段时长拆分为开始时间和结束时间两个部分
4. INDEX(...,2) - INDEX(...,1)：计算每段时长的时间差
5. *24：将时间差转换为小时数
6. IFERROR：处理可能存在的格式异常，避免公式报错
7. SUM：将所有段的时长结果求和，得到总工时

验证示例 #

当单元格A2内容为11 AM - 12PM, 1 PM-2 PM时，代入公式将返回正确结果2。

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