Google Sheets跨天时间计算实现人员可用性统计求助

优化后公式 #

直接替换你当前在Availability工作表中使用的公式即可：

=IF(LET(
    start, VLOOKUP($A2, Master!$A:$C, 2, 0),
    end, VLOOKUP($A2, Master!$A:$C, 3, 0),
    IF(start < end, ISBETWEEN(B$1, start, end), OR(B$1 >= start, B$1 <= end))
), "Y", "N")

如果你的Google Sheets版本不支持LET函数，可以使用下面的兼容版本：

=IF(
    IF(VLOOKUP($A2, Master!$A:$C, 2, 0) < VLOOKUP($A2, Master!$A:$C, 3, 0),
    ISBETWEEN(B$1, VLOOKUP($A2, Master!$A:$C, 2, 0), VLOOKUP($A2, Master!$A:$C, 3, 0)),
    OR(B$1 >= VLOOKUP($A2, Master!$A:$C, 2, 0), B$1 <= VLOOKUP($A2, Master!$A:$C, 3, 0))
), "Y", "N"
)

逻辑说明 #

• 普通当日班次（上班时间早于下班时间）：沿用原有ISBETWEEN逻辑，判断当前时间刻度落在上下班时间区间内即标记为可用
• 跨天班次（上班时间晚于下班时间）：只要当前时间刻度大于等于上班时间（如16:00-23:59区间），或者小于等于下班时间（如00:00-02:00区间），就标记为可用，完美覆盖跨天场景

注意事项 #

• Master表的上下班时间列、Availability表的表头时间刻度都需要设置为时间格式，不要存储为文本，否则时间比对逻辑会失效
• 该公式同时兼容正常班次和跨天班次，不需要额外区分班次类型，上下班时间按周更新后也无需修改公式

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