Google Sheets中非工作时段起始时SLA返回0的计算方法

核心逻辑落地规则 #

• 先判断开始时间是否落在非工作时段：早于07:00、晚于等于17:00均判定为非工作时段，直接返回0时长
• 开始时间在工作时段内时，自动计算开始、结束时间区间内落在07:00-17:00范围的有效时长作为SLA值

具体函数方案 #

假设你的Start Time数据在A列，End Time数据在B列，SLA计算结果放在C列，首行数据从第2行开始，直接在C2单元格输入对应函数后下拉填充即可。

单天无跨天场景（匹配示例表格场景） #

适用场景：所有开始、结束时间都在同一天，无跨天情况

=IF(OR(MOD(A2,1)<TIME(7,0,0), MOD(A2,1)>=TIME(17,0,0)), 0, MIN(MOD(B2,1), TIME(17,0,0)) - MAX(MOD(A2,1), TIME(7,0,0)))

函数各部分说明：

• MOD(时间单元格,1)：自动提取时间值的时分秒部分，剥离日期信息，避免日期差干扰区间判断
• OR(...)判断段：覆盖开始时间早于7点、晚于等于17点两种非工作场景，满足任意条件直接返回0
• 有效时长计算段：取结束时间与下班时间17:00的较小值，减去开始时间与上班时间7:00的较大值，得到区间内的有效工作时长
  输入函数后将C列单元格格式设置为「时长」，即可显示为00:00:00的标准格式。

跨天/多日场景 #

适用场景：存在开始、结束时间跨天，需要统计多日工作时段总时长的情况

=IF(OR(MOD(A2,1)<TIME(7,0,0), MOD(A2,1)>=TIME(17,0,0)), 0, NETWORKDAYS.INTL(A2,B2,"0000000")*10/24 - MAX(TIME(7,0,0), MOD(A2,1)) - (1 - MIN(TIME(17,0,0), MOD(B2,1))))

函数说明：

• NETWORKDAYS.INTL第三个参数"0000000"代表一周7天均计为工作日，如果需要排除周末，可将该参数改为"0000011"，自定义休息日可按需调整该参数的7位字符串
• 每日有效工作时长为10小时（7:00-17:00共10小时），因此用总工作日数乘10/24得到基准总时长，再扣除开始当日上班前的无效时长、结束当日下班后的无效时长，即为最终SLA值

注意事项 #

• 提前确认Start Time、End Time列的单元格格式为Google Sheets可识别的日期时间格式，不要存为纯文本，否则会导致函数计算报错
• 如果需要非工作时段直接返回文本格式的00:00:00，可将函数中第一个返回值0替换为"00:00:00"，但建议保留数值类0，方便后续对SLA列做求和、平均值等统计计算

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