Consulting

Results 1 to 7 of 7

Thread: "A" shift vs "B" shift work days into calendar

  1. #1

    "A" shift vs "B" shift work days into calendar

    Attached is a calendar that two shifts work "A" and "B" shift. There is a pattern to the work schedule when A is working B is off and vise versa. The pattern goes first week AA BB AAA; Second week BB AA BBB and then it starts over; Third Week AA BB AAA; forth week BB AA BBB. Is their a simple code that can insert the shift scheduled to work without having to manually do it.

    Thanks in Advance.
    Note: I started it manually to give an idea of the pattern.
    Attached Files Attached Files

  2. #2
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    With any standard calendar format, it would be a piece o' cake, but that one. JSMH.

    Is there an insurmountable reason you have to use a split month format?

    Never mind, If I can see a glimpse of an idea, many here are smarter than me and they will know how. Just consider this post a bump.

    BTW, your attachment is protected and can't be used by anyone trying to help you. They will have to rebuild the Charts themselves.

    BTW2: Why Charts?
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  3. #3
    It is protected but without a password so you should be able to unprotect. I'll re-attach a unprotected worksheet. Also it has conditional formatting A = Red background; B = Blue background.

    thanks
    Attached Files Attached Files

  4. #4
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    In your sheet, at cell AK1 enter this date:
    21/12/2014
    making sure that Excel understands this to be a date 21st December 2014. This is the base date for the whole shooting match.
    In cell B3 enter this formula:
    =IFERROR(MID(REPT("AABBAAABBAABBB",105),DATEVALUE(B$2 & "-" & $A3 & "-" & $P$1)-$AK$1,1),"")

    Copy across and down to P14.
    Copy B3 to T3
    Copy across and down to AI14, don't worry about formulae where there are no real dates at the end of the month.

    For 2016:
    Copy B3 formula to B19 adjusting $P$1 to $P$17, then copy across and down.
    You get the drift.

    The first difference between your sheet and the formulae results is on 19 Feb 2017 where you have broken the pattern.

    I think there might be a simpler formula and will come back if I find one.

    Also attached.


    Edit post posting:
    The attached has the formulae in the left most calendars, a copy of your original calendars to the right, and a grid of TRUE/FALSE tothe right of that to compare the two and highlight differences.
    Attached Files Attached Files
    Last edited by p45cal; 10-11-2015 at 09:24 AM.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  5. #5
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Not a shorter formula but one which only needs a number (from 0 to 13) in AK1 instead of a date there to alter the base of the pattern:
    In B3:
    =IFERROR(MID("AABBAAABBAABBB",MOD(DATEVALUE(B$2 & "-" & $A3 & "-" & $P$1)+$AK$1,14)+1,1),"")
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  6. #6
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    @ ronNCmale, Told ya there were some smart people here.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  7. #7
    You were so right SamT, There are some mensa members up in here. Thanks so much p45cal, it works great and I would have never figured out how to make it work. This saved me a lot of time and a big chance of making an error when I inputted the A's or B's. 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
  •