...

In Excel, count workdays in the intersection of multiple date intervals

 

In the Excel worksheet below, there are time intervals defined by multiple pairs of start and end time points. Within B3:C13, there are a total of 12 time intervals.


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

Calculated

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


Take F3 and F4 as the time interval parameters, find workdays within the interval defined by the parameters, calculate intersection between them and the 12 time intervals, and find the number of days in the intersection.

The expected result should be 6.

Use SPL XLL to enter the formula below:

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

Picture1png

The workdays()function gets a sequence of workdays in an interval; ~ represents the current interval, and ^ means calculating intersection; E() converts an Excel date string to a computable date value.

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


 
 •  256 View  •