Excel 计算多个日期区间的交集中的工作日数

Excel表格有多对起止时间形成了区间组,如B3:C312组时间区间


A

B

C

D

E

F

1







2


Ramadan
Starts

Ramadan
Ends




3


23-Apr-20

22-May-20


Date1

1-Apr-24

4


12-Apr-21

11-May-21


Date2

12-Apr-24

5


2-Apr-22

1-May-22


Expected

6

6


22-Mar-23

20-Apr-23


Caculated


7


10-Mar-24

8-Apr-24




8


28-Feb-25

29-Mar-25




9


17-Feb-26

18-Mar-26




10


7-Feb-27

8-Mar-27




11


27-Jan-28

25-Feb-28




12


15-Jan-29

13-Feb-29




13


5-Jan-30

3-Feb-30




现在要以F3F4为时间区间参数,先找到参数区间内的工作日,再与12组时间区间计算交集,计算两者交集中的天数。

结果应该是6

使用 SPL XLL,输入公式:

=spl("=w=workdays(E(?2),E(?3)),?1.sum((workdays(E(~(1)),E(~(2)))^w).len())",B3:C13,F3,F4)

Picture1png

workdays 求区间的工作日序列,~ 表示当前区间组,^ 用于求交集。E 将 Excel 的日期型字符串转为可计算日期。

https://stackoverflow.com/questions/78432696/excel-formula-to-count-days-intersecting-between-different-ranges-of-date