+ Reply to Thread
Results 1 to 8 of 8

Dynamic rota pattern

  1. #1
    Registered User
    Join Date
    11-15-2010
    Location
    Leicester
    MS-Off Ver
    Office 2010 / 2013
    Posts
    41

    Dynamic rota pattern

    Hi all

    I am trying to create a dynamic 8 week rota pattern whereby I will have a set pattern and when I enter in any week commencing date it will return what week that is.

    I have put a small sample trying to explain better.

    Hope this explains it ok.
    Attached Files Attached Files

  2. #2
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Dynamic rota pattern

    just use a vlookup as if you use week number there will be some week 53 and no week 1
    Attached Files Attached Files
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  3. #3
    Registered User
    Join Date
    11-15-2010
    Location
    Leicester
    MS-Off Ver
    Office 2010 / 2013
    Posts
    41

    Re: Dynamic rota pattern

    Hi Martin

    Thanks for the reply. Yeah, that would work but I wanted to create something more dynamic and as I have a pattern that just 8 weeks repeating I wanted a formula that would work with that.

    I have created a formula that seems to work, by using MOD and OFFSET.

    The formula is
    =OFFSET($D$3,MOD(INT(($C19-$A$2)/7),8),WEEKDAY(D18)-1)

    I have attached the file to show the working formula shoud anyone else have a use.

    The WEEKDAY(D18)-1 is just to make the formula completely dynamic so that I can use the same formula for each day. I could have made 7 different formulas like.

    =OFFSET($D$3,MOD(INT(($C19-$A$2)/7),8),0)
    =OFFSET($D$3,MOD(INT(($C19-$A$2)/7),8),1)

    and so on....

    If anyone has any improvements on this or just a better way of doing it then please post.

    Thanks
    Attached Files Attached Files

  4. #4
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Dynamic rota pattern

    vlookup is simpler and better lol

  5. #5
    Registered User
    Join Date
    11-15-2010
    Location
    Leicester
    MS-Off Ver
    Office 2010 / 2013
    Posts
    41

    Re: Dynamic rota pattern

    Always different ways to get to the end result, but like I said, I was just after a completely dynamic formula.... not that vlookup is any worse.

    Also,

    Ooops

    Just noticed a slight problem. The formula should be

    =OFFSET($D$3,MOD(INT(($M3-$A$2)/7),7),WEEKDAY(N$2)-1)

    In the MOD the divisor only needed to be 7 not 8.

  6. #6
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Dynamic rota pattern

    ="week "&MOD(ABS($A$2-C19)/7,8)+1 with start in a2 date in c19

  7. #7
    Registered User
    Join Date
    11-15-2010
    Location
    Leicester
    MS-Off Ver
    Office 2010 / 2013
    Posts
    41

    Re: Dynamic rota pattern

    Hi all

    Its been a while since I originally posted this thread but I have a question that directly links to it so thought I'd add it on here... hope thats ok!

    In the attached example is a sample of my dynamic rota pattern. Which is all working very well. (of course any improvements/suggestions welcome).

    What I'm after now is to be able to count the amount of 'OFF' days between two dates. I can easliy do this by creating the rota for the period of time I want and then count the 'OFF' days (as shown on sheet 2), but again I want to keep it dynamic if possible. One main reason for this is that I have lots of different rota patterns to work with and didn't really want loads of sheets if I can avoid it.

    Example. My 6 week pattern on week 2 has a monday 'OFF'. Between 01/01/2011 and 31/12/2011 there will be 9 mondays 'OFF'. However on week 5 it has Thursday 'OFF'. Between the same two dates there will only be 8 Thursdays 'OFF'.

    I hope this makes sense... Its not the easiest thing to explain...

    Thanks
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    11-15-2010
    Location
    Leicester
    MS-Off Ver
    Office 2010 / 2013
    Posts
    41

    Re: Dynamic rota pattern

    Hi again

    Well, I think I've solved my problem with a UDF and a 'lookup table'.

    It seems to do the job, as a rota is amended, either through changing the days or adding/removing weeks it works out how many days will be worked in the year.

    Its still in the early stages, and I'm sure it can be tidied up.....

    I've attached my solution. Any feedback welcome..

    Thanks

    P.S I just noticed that in the sample I uploaded the formula in A2 was looking at T5. It should be T2 (start of the year).
    Attached Files Attached Files
    Last edited by kt1978; 06-06-2011 at 06:22 PM. Reason: informing of formual error.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1