Rotating Schedule advice

Hello,

Please see attached spreadsheet. SCHEDULE FOR THE WEEK OF 05 JAN - JAN 11.xlsx I created a 22 week schedule for staff Sunday to Saturday. Each line rotation does 5 shifts a week. After each week they go down one line rotation

"Bill" in rotation line # 1 goes to rotation line # 2...
"Joe" in rotation line # 2 goes to rotation line # 3...
At the end of line 22 rotation "Mark" in rotation line # 22 goes back up to rotation line # 1 "bill" and starts all over again.

I would like the names to auto populate and rotate each employee down one line rotation when I open a new sheet (Insert worksheet) or if someone has a better idea for me

and for the date to auto populate to the next week when I open a new worksheet.

All the shifts are 7.5 hours but they need to stay late quite often.. So if I edit their shift times I need the hours to auto update to how long they actually worked example Tuesday line rotation #1 they worked from 1500-2300 hours (8 hours)

There is 2 different departments Sup and TVM and they have different billing rates.. So I need all the TVM hours to sum up in Column K according to their department to balance with a company work software.

Rotations line 1 through rotation line # 5 are all TVM department shifts so its easy but line 6 they start to mix between TVM and Sup Departments. Would it be best to just =sum all the tvm and sup shifts individually or is their a better way?

Any input would be much appreciated.

Thanks in advance.

Wow! Thank you very much Pierre Leclerc for such a quick reply! This is really fantastic.

One thing I forgot to mention is that often the workers will do shift swaps in case they need a certain day off....So by the end of the week we have several swaps but if I create a new schedule the names are swapping from the current week to the next week which means I will need to manually go back and change the names again every week.

Its not a big deal and I can manage because this is 100x better better than what I had before but out of curiosity is there some way I can make their names always reset without the swaps when I create next weeks schedule?

I am going to attempt to also add some conditional formatting to be able to track the employees shift swaps that will turn their names red when they do a shift swap.

Ideally I want to do something like

example:

write brackets (1) next to the two employees names that did a shift swap and for it to start making a list at the bottom of the schedule

I made an example in cell B98 with bill and joe doing a shift swap.

I also added a total calculator on the side. Let me know what you think

Thanks again for you help Pierre. It is much appreciatedNew Schedule.xlsm.

Hi Pierre,

Thanks for the quick reply again.

Im not sure what happened with the dates but I managed to get it return it back to how you fixed it for me plus the totals on the side. New Schedule with totals.xlsm

How would I go about moving the macro into column B? Sorry for my incompetance. Thank you for your time.

Wow This is really great Thanks so much Pierre!!

I noticed that cell D12 seems to be permanently red highlighted for some reason.

Also, excel keeps asking me to enable/disable macros regarding safety can/should I disable that security message?

What do you think about the totals on the right side? Do you think it look good/professional?

I cant think of anything else at the moment. This is really really great. Thank you soo much.

Worked like a charm! Thanks again Pierre! 5 STAR +++REP [SOLVED]

Awesome Pierre. If this Sheet helped you please don't forget to * Add Reputation to p24leclerc

Hi,

Is it possible to create a similar macro base on skills, so if I create a sheet with operator skills & what roles they can complete to then run a macro that assigns them to said task taking into account what they have worked on in previous weeks helping fairly share the jobs over said period i.e weekly?

Thanks

Thank you so much

Lets see how I can explain this without complicating the matter.

I have a similar schedule but all the names are on the left column and on the right where the dates and hours are are pre-defined and don't change.
The only thing I want to have rotate are the names bi-weekly.

So there are 20 employees and they need to rotate every two weeks so in essence their names should move down every two weeks to cover the previous person shift.
sun mon tue wed thur fri sat sun mon tue wed thur fri sat
name 1 HOURS
name 2
name 3
name 4
name 5
etc until they rotate to:
name 5
name 1
name 2
name 3
name 4

The only thing I want is the names to rotate and the rest stays the same.
Any help would be greatly appreciated