Calculate No. of Working Days based on Shift Pattern

[Sandeep Warrier]

Hey everyone!

I've been asked to come up with a method to calculate number of working days between 2 dates for people who apply for sick leaves in order to calculate sick pay. The catch is, this needs to be done according to a shift pattern.

A person may work 3 on 4 off shift, or they may work 7 on 4 off shift, or 5 on 3 off shift, and so on. There are about 8-10 different patterns. In addition to the shift pattern, if the total pattern is 7 days (on + off), a person may also work on specific days. For example, if a person works 3 on 4 off shift, those three working days may be a Mon, Tue, Wed or a Tue, Thu, Fri, or any other 3 day combination.

In the first example below, the person works a 4 on 3 off shift, and works on Monday, Wednesday, Friday and Saturday. If the person applies for leave between 21st November and 11th December, they will get a total of 12 working days worth of sick days and pay. In the second example, the person works on a 5 on 4 off shift. For this they get 13 working days worth of sick days and pay.

Here, for the 2nd example, I have assumed that the start date is the start of the shift pattern day, but this may not always be the case. To handle this, if any other info is needed, I can add that as a requirement.

I know I can get the full cycles by dividing the cycle length with the number of days, but calculating the remaining seems to be very tricky, and I'm not able to come up with a logic for this. Any help will be appreciated.

Book1
	A	B	C	D	E	F	G
1	Start Date	End Date	Shift	Days Worked	Total Working Days
2	21-11-2024	11-12-2024	4 on 3 off	Mon,Wed,Fri,Sat	12
3	21-11-2024	11-12-2024	5 on 4 off		13
4
5
6	Sun	Mon	Tue	Wed	Thu	Fri	Sat
7	17-Nov	18-Nov	19-Nov	20-Nov	21-Nov	22-Nov	23-Nov
8	24-Nov	25-Nov	26-Nov	27-Nov	28-Nov	29-Nov	30-Nov
9	01-Dec	02-Dec	03-Dec	04-Dec	05-Dec	06-Dec	07-Dec
10	08-Dec	09-Dec	10-Dec	11-Dec	12-Dec	13-Dec	14-Dec
11
12
13	Sun	Mon	Tue	Wed	Thu	Fri	Sat
14	17-Nov	18-Nov	19-Nov	20-Nov	21-Nov	22-Nov	23-Nov
15	24-Nov	25-Nov	26-Nov	27-Nov	28-Nov	29-Nov	30-Nov
16	01-Dec	02-Dec	03-Dec	04-Dec	05-Dec	06-Dec	07-Dec
17	08-Dec	09-Dec	10-Dec	11-Dec	12-Dec	13-Dec	14-Dec
Sheet1

 

[JamesCanale]

Works for 365:

MrExcelPlayground23.xlsx
	A	B	C	D	E	F	G	H	I
1	Start Date	End Date	Shift On	Shift Off	Days Worked (Sunday = 1)	Sick Start	Sick Finish	Total Working Days	Sickdays needed
2	11/21/2024	12/11/2024	4	3	2,4,6,7	11/28/2024	12/4/2024	12	4
3	11/21/2024	12/11/2024	5	4		11/28/2024	12/4/2024	13	5
Sheet24

Cell Formulas
Range	Formula
H2:I3	H2	=IF(E2<>"",LET(shift,VALUE(TEXTSPLIT(E2,,",")),start,A2,finish,B2,shifton,C2,shiftoff,D2,sickstart,F2,sickfinish,G2,a,SEQUENCE(finish-start+1,1,start,1),b,WEEKDAY(a,1),c,ISNUMBER(XMATCH(b,shift)),wd,SUM(--c),sd,FILTER(c,(a>=sickstart)*(a<=sickfinish)),sickdays,SUM(--sd),HSTACK(wd,sickdays)),LET(start,A2,finish,B2,shifton,C2,shiftoff,D2,sickstart,F2,sickfinish,G2,a,SEQUENCE(finish-start+1,1,start,1),b,MOD(a-start,shifton+shiftoff)+1,c,b<=shifton,wd,SUM(--c),sd,FILTER(c,(a>=sickstart)*(a<=sickfinish)),sickdays,SUM(--sd),HSTACK(wd,sickdays)))
Dynamic array formulas.

 

[Tetra201]

The solution for 7-day patterns (OP Example 1 and similar) seems to be trivial -- NETWORKDAYS.INTL

Here is my take on a solution for x-on/y-off patterns (OP Example 2 and similar). Data layout is as follows:
A3: Start Date, B3: End Date, C3: Number of Days On, D3: Number of Days Off, E3: Pattern Start Date (i.e., first workday of the pattern that falls between the Start Date and End Date).

=SUMPRODUCT(--(MOD(ROW(INDEX(A:A,A3):INDEX(A:A,B3))-(MOD(E3-1,C3+D3)+1),C3+D3)<C3))