求排除周末、非工作日的工作时段时长计算公式

核心逻辑 #

总工作时长由三部分组成：完整工作日的时长、起始日的有效工作时长、结束日的有效工作时长，公式如下：
总时长(小时) = 完整工作日数 × 10 + 起始日有效时长 + 结束日有效时长

1. 计算完整工作日数 #

使用工作日计数函数（如Excel的NETWORKDAYS，支持排除自定义节假日）：

• 无节假日时：完整工作日数 = NETWORKDAYS(起始日期+1, 结束日期-1)
• 需排除节假日时：完整工作日数 = NETWORKDAYS(起始日期+1, 结束日期-1, 节假日列表)

注：起始日期+1和结束日期-1是为了只统计两个日期之间的完整工作日，排除起始日和结束日本身。

2. 计算起始日有效时长 #

起始日只有在工作时段内的部分才算有效时长，公式：
起始日有效时长(小时) = MAX(0, MIN(起始时间, 18:00) - MAX(起始时间, 08:00))

时间需转换为小时数（如12:40 = 12 + 40/60 ≈ 12.6667小时），若起始时间早于08:00，从08:00开始算；晚于18:00，有效时长为0。

3. 计算结束日有效时长 #

同理，结束日的有效时长公式：
结束日有效时长(小时) = MAX(0, MIN(结束时间, 18:00) - MAX(结束时间, 08:00))

若结束时间早于08:00，有效时长为0；晚于18:00，算到18:00为止。

示例计算（起始：4/8/23 12:40，结束：7/8/23 10:07） #

1. 完整工作日数：
   起始日+1为8/5/23（周五，工作日），结束日-1为8/6/23（周六，非工作日），所以NETWORKDAYS(8/5/23, 8/6/23) = 1

2. 起始日有效时长：
   MIN(12.6667, 18) - MAX(12.6667, 8) = 18 - 12.6667 = 5.3333小时（5小时20分钟）

3. 结束日有效时长：
   MIN(10.1167, 18) - MAX(10.1167, 8) = 10.1167 - 8 = 2.1167小时（2小时7分钟）

4. 总时长：
   1×10 + 5.3333 + 2.1167 = 17.45小时（17小时27分钟）

工具实现示例 #

Excel完整公式（A1=起始时间，B1=结束时间，C:C=节假日列表） #

=NETWORKDAYS(INT(A1)+1, INT(B1)-1, C:C)*10 + 
MAX(0, MIN(MOD(A1,1), 18/24) - MAX(MOD(A1,1), 8/24))*24 + 
MAX(0, MIN(MOD(B1,1), 18/24) - MAX(MOD(B1,1), 8/24))*24

说明：INT(A1)提取日期部分，MOD(A1,1)提取时间（以天为单位，乘以24转为小时）

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