Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Jul 2008
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Work Rota (2000)

    Edited by HansV to correct link and display data correctly

    http://www.mrexcel.com/forum/showthread.php?t=328515

    I am no good at logic, someone who is please help. In work there are 4 shifts A-D. Each shift works 4 days, then has 4 days off. You either begin at 7am or 7pm. I have created 4 userforms for each shift containing info. I want a command button that when clicked displays the form of which shift is currently in work. The shift pattern lasts 8 days, and then starts again at the beginnning. I have pasted the 8 day pattern below:
    <pre> Day 1 Day 2 Day 3 Day 4 Day 5 Day 6 Day 7 Day 8
    Shift A 7 7 19 19 RD RD RD RD
    Shift B 19 19 RD RD RD RD 7 7
    Shift C RD RD 7 7 19 19 RD RD
    Shift D RD RD RD RD 7 7 19 19
    </pre>

    I need the pattern to start from a particular date and then keep repeating the 8 days through the months. If anyone could do this I would be very grateful.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Work Rota (2000)

    Welcome to Woody's Lounge!

    Try this code:
    <code>
    Sub ShowForm()
    ' Use US date format m/d/yyyy
    Const dtmStart = #1/1/2008#
    Dim arrShifts
    Dim intDiff As Integer
    arrShifts = Array("A", "B", "A", "B", "C", "A", _
    "C", "A", "D", "C", "D", "C", "B", "D", "B", "D")
    intDiff = Int(2 * (Now - dtmStart - 7 / 24)) Mod 16
    Select Case arrShifts(intDiff)
    Case "A"
    UserFormA.Show
    Case "B"
    UserFormB.Show
    Case "C"
    UserFormC.Show
    Case "D"
    UserFormD.Show
    End Select
    End Sub
    </code>
    Change the constant dtmStart as needed, using US date format m/d/yyy (even if your local settings are different).

  3. #3
    New Lounger
    Join Date
    Jul 2008
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Work Rota (2000)

    Thank you very much for that. The date format doesnt really matter as the user wont see this. Ive set dtmStart to todays date and form A displays which it should do. The only line i'm struggling to understand is 'intDiff = Int(2 * (Now - dtmStart - 7 / 24)) Mod 16' . How accurate are we talking here? As I have set it to todays date, at 7pm this evening(my time), that should display form B, and then return to form A at 7am tomorrow morning. Is this whats going to happen? In other words, if the button is clicked at 6.59pm and at 7.01pm, which form would display.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Work Rota (2000)

    Excel and VBA store dates as a number of days and times as fractions of 1 day.
    The part - 7 / 24 subtracts 7 hours from the date/time so that 7:00 becomes midnight and 19:00 becomes noon.
    Now - dtmStart - 7 / 24 calculates the time difference between the start date and the current time, and subtracts 7 hours.
    This is multiplied by 2 to account for the 2 shifts per day, and rounded down to a whole number.
    The Mod 16 calculates the remainder after division by 16, i.e. the result is in the range 0, 1, ..., 15. This is used as index into the array.
    Because we subtract 7 hours and multiply by 2, the result changes at 7:00 and at 19:00.

  5. #5
    New Lounger
    Join Date
    Jul 2008
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Work Rota (2000)

    well it seems to workin which is brill. One last question. Once it reaches the final part of the array, which is D shift. Will it return to the beginning and start the process again with A shift, and continue to do this for ever more?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Work Rota (2000)

    Yes, that's what the Mod 16 part is for - it makes the series start over when you reach 16 (the number of elements in the array).

  7. #7
    New Lounger
    Join Date
    Jul 2008
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Work Rota (2000)

    thats excellent. cant thank you enough!

    thanks again

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •