• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

3-2-2 Shift Scheduling

cparks

cparks

Member
Hello Forum,

I'm slowly trying to upgrade my level of experise in Excel...as in array formulas. The more I work with Excel the more I come across and realize how much array formulas are used. My next step is VBA...but to me, you might as well be speaking a different language...onto the issue.

Ive already asked for help in this forum (I might add has been awesome!) with a calendar that Im using as an event calendar for the family/kids for us and other family members.

What I have now come across is: My wife works a 2-2-3 shift pattern (also called Panama Schedule) (Work 2, Off 2, Work 3, Off 2, Work 2, Off 3,...).

The attached file has what I have so far (I've cleaned it up for sample purposes), but has all the formulas and some formatting already in it.

The last sheet is an example of the 2-2-3 shift pattern and parts of a possible formula that Ive tried but not successful. Just as for everyone, my expertise is growing in Excel very well, but there are still many things I dont understand and Im trying to figure out.

Can someone give me some guidance on starting this...or what sort of layout I might need to come up with so that I may tie it into what I have thus far?

I mainly need help with the 2-2-3 schedule...everything else I think I can figure out....hopefully.

Much thanks.
 

Attachments

Last edited:
I would approach the layout differently
I would start with a large calendar that had all dates down the left side from Jan 1 to as long as you want, maybe even 4 or 5 years, with people across the top

The applying a formula to each person is a lot simpler in this layout, that trying to apply it to the Calendar layout you are using.

You can still report the Schedule in a Calendar Layout, but retrieve the data from the previous mentioned worksheet
 
I guess what I could do is lock some cells inside the date box (7 cells to choose from) and assign them to specific events/recurring dates and try the way you mentioned...is that what you mean?
 
No
I would setup a base calendar as a new worksheet
I would start with a large calendar that had all dates down the left side from Jan 1 to as long as you want, maybe even 4 or 5 years, with people across the top
upload_2015-2-22_14-22-16.png

Then applying a formula to each cell which is based on the date and the roster type for the person will be a lot easier than trying to apply it to the Calendar layout you are using.

You can still report the Schedule in a Calendar Layout, but retrieve the data from the previous mentioned worksheet
 
You can then look up the dates and activities from here to populate the calendar which is displayed as a Normal Calendar
 
You must log in or register to reply here.
Back
Top