Help with setting up a shift schedule/roster!

**target.aquired** · 06-23-2012, 07:51 AM

Hi there all excel geeks!

Im just starting to learn about VBA and excel formulas!

Im trying to create a shift pattern, based on either weeks or days, troughout a year! The actual shift pattern is based on a 2 week work period and 4 weeks off.

Table:
Column A1-A50 Names
Columns B1-A50 Starting date
Top Row Weeks or dates for a whole year.

So when you pick the starting date all the shift patterns will be generated or changed + highlighted.

I know how to generate the series of dates but cant figure out how to highlight them or put them together using a code or formula.

Thanks
TA

**MarvinP** · 06-23-2012, 10:31 AM

Hi TA who lives in the WWW and has your goals in place, welcome to the forum.

I'm an advocate of pivot tables for these kinds of problems. In Pivot Tables you need to have your data in a TABLE. Then you do a simple pivot and results will appear. To show what I mean, I've attached an example with 5 names. Then I did a random date and calculated Week Number and Year so I could Pivot the data. See attached to see if it meets you needs.

**target.aquired** · 07-03-2012, 06:58 AM

Hi,
Thanks for the feedback but I think im on a different page, or Im may belost too :-) See attached link: http://skiftkalender.no/index.php?sk...=1&language=US

This is more or less what im looking for, but based on a starting date, to populate dates with highlights in a worksheet.

Sorry for any inconvenience

**target.aquired** · 08-07-2012, 05:29 AM

Hi again,

Still struggeling here so its time to bump this thread :-)

I have attached the current spreadsheet we are using for employees, a very time consuming rota and I want to have the ability to auto populate work ranges (with highlights) if possible.

This is based on a offshore work rotation, 2 weeks work and 4 weeks off with different traveldays, yes I know they are spoiled

Column A - employee number
Column B - Work location
Column C - Travel day (monday to friday)
Column D --> calendar and shift pattern, weeks!

For instance; Employee 1 (A12) I would like to pick a starting date and this will auto populate the work rota over the whole year! With highlight if possible

I am open to suggestions to change the layout and appreciate all the help I can get. Im kind of a novice user in Excel