Printable View
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.
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?
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
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.
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),"")
@ ronNCmale, Told ya there were some smart people here. :D
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.