Rotated Shifts

I need to build function that can created rotated shifts for 4 staff, that can be rotated every two days

Rules:
scenario

3 shifts (7-3,3-11,11-7)

1.7-3
2.3-11
3.11-7
                Day1          Day 2       Day 3   Day 4   Day 5  Day 7  Day 8   Day 9
Team 1     1                 1              2          2         3          3       off        Off
Team 2     2                 2             3           3         off    off          1            1
Team 3      3                 3             off        off       1        1           2            2
Team 4      off               off            1         1          2         2           3           3


But we need to configure for each one what is his last shift (1 or 2 o r 3) before calculate because the current can be one of them
We should configure the start date when this should be apply
lets say the day 1 is 2/3/2014  then day 2 will be 3/3/2014
No vacations.
The second we need to save this in database and exported as excel sheet
abdallah1973Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Rgonzo1971Connect With a Mentor Commented:
HI,

the function could be this (the adjustment deĆ¼pends on the start date)

Function FindTurns(ByVal myDate As Date, ByVal TeamNr As Integer) As Integer
' 0 = 7-3, 1 = 3-11, 2 = 11-7, 3 = Off
' TeamNr = 1 to 4
Adjust = 2
FindTurns = ((((((myDate + Adjust) Mod 8) \ 2) + 1) Mod 4) + TeamNr - 1) Mod 4
End Function

Open in new window

Regards
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.