Custom roster calendar

[shane7mcdonald]

Hi all, I have a problem I'm trying to solve. I need to create a custom roster calendar based on the following schedule;

- 8 day shifts

- 6 days off (RR)

- 7 night shifts

- 7 days off (RR)

Then the roster is repeated.

I have 4 crews who are all on a different parts of the cycle.

Below is what the calendar should look like over a few weeks.

Date	Shift A	Shift B	Shift C	Shift D
19/03/2020	D	N	D	RR
20/03/2020	D	N	RR	RR
21/03/2020	D	N	RR	RR
22/03/2020	D	N	RR	RR
23/03/2020	D	N	RR	RR
24/03/2020	D	N	RR	RR
25/03/2020	D	N	RR	RR
26/03/2020	D	RR	N	D
27/03/2020	RR	RR	N	D
28/03/2020	RR	RR	N	D
29/03/2020	RR	RR	N	D
30/03/2020	RR	RR	N	D
31/03/2020	RR	RR	N	D
1/04/2020	RR	RR	N	D
2/04/2020	N	D	RR	D
3/04/2020	N	D	RR	RR
4/04/2020	N	D	RR	RR
5/04/2020	N	D	RR	RR
6/04/2020	N	D	RR	RR
7/04/2020	N	D	RR	RR
8/04/2020	N	D	RR	RR
9/04/2020	RR	D	D	N
10/04/2020	RR	RR	D	N
11/04/2020	RR	RR	D	N
12/04/2020	RR	RR	D	N
13/04/2020	RR	RR	D	N
14/04/2020	RR	RR	D	N
15/04/2020	RR	RR	D	N

Any help would be greatly appreciated!

[lbeneyze · Accepted solution]

Good morning,

I suggest you the following approach:

   1 you create a date column for the period you want

   2 you add a index column to the date table. In your example 3/19/2020 will be 1... 3/18/2021 will have 365

   3 add a custom column to create an increment of 28: if [Index] < 28 then [Index] else if [Index] - (28 * (Number.IntegerDivide([Index], 28))) = 0 then 28 else [Index] - (28 * (Number.IntegerDivide([Index], 28)))

   4 create different shift sequences with 2 columns: colum 1 is index from 1 to 28, 2nd column description of the shift: "D", "RR", N"

   5 Realise a full join on Date and Shift table based on Index column.

Did it answer your question? Please mark my post as solution

Did you like the answer? Please add a kudo.

Good luck and keep up the good job.

Kind regards,

Lohic Beneyzet

[shane7mcdonald]

Thanks @lbeneyze , that was most helpful! 👍