如何计算两日期区间内指定月份的工作日天数？

在Sheet2的C2单元格输入以下公式，下拉填充即可：

=LET(
    name, A2,
    month_text, B2,
    start_date, XLOOKUP(name, Sheet1!A:A, Sheet1!B:B),
    end_date, XLOOKUP(name, Sheet1!A:A, Sheet1!C:C),
    month_start, DATE(2023, MONTH(DATEVALUE(month_text&" 1, 2023")), 1),
    month_end, EOMONTH(month_start, 0),
    overlap_start, MAX(start_date, month_start),
    overlap_end, MIN(end_date, month_end),
    IF(overlap_start > overlap_end, 0, NETWORKDAYS(overlap_start, overlap_end))
)

公式拆解说明 #

• 匹配姓名对应的起止日期：用XLOOKUP根据Sheet2的姓名，从Sheet1中提取对应的开始和结束日期。如果你的Excel版本不支持XLOOKUP，可以替换为VLOOKUP：
  • 取Start Date：VLOOKUP(name, Sheet1!A:C, 2, FALSE)
  • 取End Date：VLOOKUP(name, Sheet1!A:C, 3, FALSE)
• 转换月份文本为日期区间：
  • DATE(2023, MONTH(DATEVALUE(month_text&" 1, 2023")), 1)：将"February"这类月份文本转换成2023年该月的第一天
  • EOMONTH(month_start, 0)：快速获取该月份的最后一天
• 计算有效重叠区间：用MAX取两个起始日期中较晚的作为计算起点，MIN取两个结束日期中较早的作为计算终点，确保只计算两个区间的重叠部分
• 统计工作日：如果重叠区间不存在（起点晚于终点）则返回0，否则用NETWORKDAYS统计该区间内的工作日（默认排除周六周日，需自定义节假日可添加第三个参数）

示例验证 #

• Anne的情况：匹配到的起止日期是2/16/23_{3/10/23，February的区间是2/1/23}2/28/23，重叠区间为2/16/23~2/28/23，工作日共9天，与期望结果一致
• Bob的情况：匹配到的起止日期是4/23/23_{7/4/23，May的区间是5/1/23}5/31/23，重叠区间为5/1/23~5/31/23，工作日共23天，与期望结果一致

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