Four on Four off Shift Pattern

[jamelmessaoud]

Hi guys,

I have a requirement to generate a calendar for employees who work 4 on and 4 off shift patterns.  What I am thinking is creating a master calendar and have a row number which counts from 1 to 8 and then resets back to 1 again after the 8th date.  What I am thinking is then exclude columns 5 to 8 further down the script.

I've been having a play with the RowNo function but I can't seem to get it to do what I need.  Is there a way to create a row number of 1 to 8 that resets back to 1 again? Something else to throw into the mix I need to start the count based upon the employees start date!

Hoping someone can help 🙂

Thanks

[marcus_sommer]

You could create such sequences within the calendar with something like:

mod(rowno(), 8 )+ 1 as 8Days,
mod(rowno(), 4) + 1 as 4Days

[jamelmessaoud]

I have tried that but for some stupid reason the number doesn't start at  1?! Is there any way to force the row number to start at number one?  If I could do that it would  work perfect I think!

[jamelmessaoud]

Here's another idea.

I could build a master calendar and only populate the days where they are working? 

https://excelribbon.tips.net/T010759_Generating_a_4_On_4_Off_Work_Schedule.html

For example,  build a calendar which only shows the following dates 06/09, 07/09, 08/09, 09/09,  14/09, 15/09, 16/09, 17/09.  So effectively the 4 off dates are not shown...

I have no idea how to do this though... 😄

Thanks

[marcus_sommer]

I would tend to keep everything within a single calendar and only creating separate specialized tables if there are further requirements which couldn't be practically included in the calendar.

That your calendar isn't starting with the first day of the 4/4 shifts should be solvable with an appropriate offset- value, maybe with something like this:

if(rowno() < OffsetDays, -1, mod(rowno() - OffsetDays, 8 )+ 1) as 8Days

[jamelmessaoud]

I'll give it a try.  Thanks Marcus 🙂