4 Replies Latest reply on Jul 20, 2018 9:29 AM by Don Wise

# Continuous Shift Cycle/Rotation using Modulo Function

We recently implemented the attached workbook example, to support the identification, by date, of when a shift occurred.  3 different shifts with a 6-day rotation (AA,BB,CC) so A-SHIFT is on for two contiguous dates, then off for four contiguous dates, B-SHIFT is on for two, then off for four, etc.  We also used same for a 24-day rotation, 3 different shifts and got great initial results.

We used a combination of calcs and the modulo function to achieve the correct rotation/cycle. It works very well, inclusive of date data where a date is non-contiguous.  In the Superstore Sample attached, there are some dates that are non-contiguous and the cycle/rotation disregards that issue and the rotation is good (for the 1st year).  In the production environment all dates are contiguous.

The issue is that at the end of a complete 1st year, the cycle/rotation breaks. See below screenshot. This is regardless of whether the dates are contiguous or not. So, scaffolding by a date dimension/fact table will not resolve the issue. Any thoughts in keeping the rotation across multiple years using modulo function?

Thanks, Don

• ###### 1. Re: Continuous Shift Cycle/Rotation using Modulo Function

Hi Don see the attached

it will return this

I just created an anchor at your start date and used datediff

then

then

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

• ###### 2. Re: Continuous Shift Cycle/Rotation using Modulo Function

Hi Jim,

Thank you!

That worked perfectly!   Don

• ###### 3. Re: Continuous Shift Cycle/Rotation using Modulo Function

Thanks Don

I always like your posts - well thought out

I just got lucky with this one

Jim

• ###### 4. Re: Continuous Shift Cycle/Rotation using Modulo Function

Thanks Jim,

I've learned a lot from you over the years, so I greatly appreciate the kind words.  As background, this will programmatically resolve the documenting of a Firefighter's shift schedule for 11 agencies, involving two different types of schedule rotations and three different types of 24-hour shift periods.  A bit complex.  I knew the issue would involve that date field but simply hit a wall on creativity.  Thank you once again!   Don