如何在Google Sheets中计算9:00-17:00时段内的有效工作时长？

要计算任务在9:00-17:00工作时段内的耗时，同时排除周末和公共节假日，可以按以下步骤操作：

1. 准备节假日列表 #

新建一个工作表并命名为「节假日」，在A列输入所有需要排除的公共节假日日期（确保格式为Google Sheets可识别的日期格式，比如2022/10/1）。

2. 使用公式计算有效时长 #

假设开始日期在A列，结束日期在B列，结果列从C2开始，在C2单元格输入以下公式：

=IF(A2=B2, 
  MAX(0, MIN(B2, INT(B2)+TIME(17,0,0)) - MAX(A2, INT(A2)+TIME(9,0,0))),
  (NETWORKDAYS.INTL(A2, B2, 1, 节假日!A:A)-1)*8/24 + 
  MAX(0, INT(A2)+TIME(17,0,0) - MAX(A2, INT(A2)+TIME(9,0,0))) + 
  MAX(0, MIN(B2, INT(B2)+TIME(17,0,0)) - INT(B2)-TIME(9,0,0))
)

公式说明： #

• 同天情况：如果开始和结束在同一天，直接计算当天落在9:00-17:00的时长，用MAX(0, ...)避免出现负数结果。
• 跨天情况：
  • NETWORKDAYS.INTL(A2, B2, 1, 节假日!A:A)：计算开始到结束日期之间的工作日总数（排除周六周日和指定节假日），参数1代表周末为周六周日，若你的周末规则不同可调整该参数。
  • (工作日数-1)*8/24：计算中间完整工作日的总时长（每天8小时，转换为Google Sheets的天单位）。
  • 后两个MAX(0, ...)分别计算开始当天的剩余工作时长，以及结束当天的有效工作时长。

3. 设置单元格格式 #

选中结果列，设置单元格格式为「时长」或自定义格式[h]:mm:ss，这样可以正确显示超过24小时的总耗时。

注意事项 #

• 确保A、B列的日期时间是Google Sheets可识别的格式，若为文本需先转换为日期时间类型。
• 若无需要排除的公共节假日，可删除公式中的节假日!A:A参数，改为NETWORKDAYS.INTL(A2, B2, 1)。

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