Designing an Excel Spreadsheet to display Shift Pattern
Hello Experts
Another holiday approaches, so Happy Easter to all.
I have been playing with a shift pattern to the extent that I am baffled now.
The Shift pattern repeats itself every thirty five days as follows:
Commencing April 1st 2011
A - SHIFT
3 Nights, 2 Rest Days, 2 Mornings, 3 Afternoons, 2 Nights, 2 Rest Days, 3 Mornings, 2 Afternoons, 2 Nights, 10 Rest Days, 2 Mornings, 2 Afternoons.
B-SHIFT
3 Afternoons, 2 Nights, 2 Rest Days, 3 Mornings, 2 Afternoons, 2 Nights, 10 Rest Days, 2 Mornings, 2 Afternoons, 2 Nights, 2 Rest Days, 2 Mornings
C-SHIFT
3 Mornings, 2 Afternoons, 2 Nights, 10 Rest Days, 2 Mornings, 2 Afternoons, 3 Nights, 2 Rest Days, 2 Mornings, 3 Afternoons, 2 Nights, 2 Rest Days
D-SHIFT
10 Rest Days, 2 Mornings, 2 Afternoons, 3 Nights, 2 Rest Days, 2 Mornings, 3 Afternoons, 2 Nights, 2 Rest Days, 3 Mornings, 2 Afternoons, 2 Nights
-E-SHIFT
3 Rest Days, 2 Mornings, 2 Afternoons, 3 Nights, 2 Rest Days, 2 Mornings, 3 Afternoons, 2 Nights, 2 Rest Days, 3 Mornings, 2 Afternoons, 2 Nights, 7 Rest Days
That now completes the 35 day cycle of shift routines.
Now I do know that you experts are very nay extremely proficient and I wondered if you could come up with some of your magic and provide me with a solution. This has drove me potty and my brain is in overload.
Is it simple, too simple that I am missing something or is something more sophisticated required such as VBA. (If thats the case then I am snookered as VBA is beyond me).
Please would it be possible for someone to provide me with a few more brain cells.
As always I am in your debt, please help
Regards
Tezza73
Another holiday approaches, so Happy Easter to all.
I have been playing with a shift pattern to the extent that I am baffled now.
The Shift pattern repeats itself every thirty five days as follows:
Commencing April 1st 2011
A - SHIFT
3 Nights, 2 Rest Days, 2 Mornings, 3 Afternoons, 2 Nights, 2 Rest Days, 3 Mornings, 2 Afternoons, 2 Nights, 10 Rest Days, 2 Mornings, 2 Afternoons.
B-SHIFT
3 Afternoons, 2 Nights, 2 Rest Days, 3 Mornings, 2 Afternoons, 2 Nights, 10 Rest Days, 2 Mornings, 2 Afternoons, 2 Nights, 2 Rest Days, 2 Mornings
C-SHIFT
3 Mornings, 2 Afternoons, 2 Nights, 10 Rest Days, 2 Mornings, 2 Afternoons, 3 Nights, 2 Rest Days, 2 Mornings, 3 Afternoons, 2 Nights, 2 Rest Days
D-SHIFT
10 Rest Days, 2 Mornings, 2 Afternoons, 3 Nights, 2 Rest Days, 2 Mornings, 3 Afternoons, 2 Nights, 2 Rest Days, 3 Mornings, 2 Afternoons, 2 Nights
-E-SHIFT
3 Rest Days, 2 Mornings, 2 Afternoons, 3 Nights, 2 Rest Days, 2 Mornings, 3 Afternoons, 2 Nights, 2 Rest Days, 3 Mornings, 2 Afternoons, 2 Nights, 7 Rest Days
That now completes the 35 day cycle of shift routines.
Now I do know that you experts are very nay extremely proficient and I wondered if you could come up with some of your magic and provide me with a solution. This has drove me potty and my brain is in overload.
Is it simple, too simple that I am missing something or is something more sophisticated required such as VBA. (If thats the case then I am snookered as VBA is beyond me).
Please would it be possible for someone to provide me with a few more brain cells.
As always I am in your debt, please help
Regards
Tezza73
Arno Koster
The pattern used seems ok to me, but how can we help you with it ?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
akoster
Many thanks for answering promptly and yes you are correct I slipped a night on shift B.
What I was after was a full year calendar from 1st April 2011 to 31st March 2012,
I was hoping that I could input a start date, say 1st April 2001 and then the calendar would replicate the 35 day cycle and arrange the calendar in MONTHLY format.
APRIL 1 2 3 4 5 and so on
A
B
C
D
E
MAY 1 2 3 4 5 and so on
A
B
C
D
E
etc.
Does that make the task a lot harder now.
Regards
Tezza73
Many thanks for answering promptly and yes you are correct I slipped a night on shift B.
What I was after was a full year calendar from 1st April 2011 to 31st March 2012,
I was hoping that I could input a start date, say 1st April 2001 and then the calendar would replicate the 35 day cycle and arrange the calendar in MONTHLY format.
APRIL 1 2 3 4 5 and so on
A
B
C
D
E
MAY 1 2 3 4 5 and so on
A
B
C
D
E
etc.
Does that make the task a lot harder now.
Regards
Tezza73
Take a look at my file. you can extend columns A to g as far as you like
ASKER
A - SHIFT
3 Nights, 2 Rest Days, 2 Mornings, 3 Afternoons, 2 Nights, 2 Rest Days, 3 Mornings, 2 Afternoons, 2 Nights, 10 Rest Days, 2 Mornings, 2 Afternoons.
B-SHIFT
3 Afternoons, 2 Nights, 2 Rest Days, 3 Mornings, 2 Afternoons, 2 Nights, 10 Rest Days, 2 Mornings, 2 Afternoons, 3 Nights, 2 Rest Days, 2 Mornings
C-SHIFT
3 Mornings, 2 Afternoons, 2 Nights, 10 Rest Days, 2 Mornings, 2 Afternoons, 3 Nights, 2 Rest Days, 2 Mornings, 3 Afternoons, 2 Nights, 2 Rest Days
D-SHIFT
10 Rest Days, 2 Mornings, 2 Afternoons, 3 Nights, 2 Rest Days, 2 Mornings, 3 Afternoons, 2 Nights, 2 Rest Days, 3 Mornings, 2 Afternoons, 2 Nights
-E-SHIFT
3 Rest Days, 2 Mornings, 2 Afternoons, 3 Nights, 2 Rest Days, 2 Mornings, 3 Afternoons, 2 Nights, 2 Rest Days, 3 Mornings, 2 Afternoons, 2 Nights, 7 Rest Days
3 Nights, 2 Rest Days, 2 Mornings, 3 Afternoons, 2 Nights, 2 Rest Days, 3 Mornings, 2 Afternoons, 2 Nights, 10 Rest Days, 2 Mornings, 2 Afternoons.
B-SHIFT
3 Afternoons, 2 Nights, 2 Rest Days, 3 Mornings, 2 Afternoons, 2 Nights, 10 Rest Days, 2 Mornings, 2 Afternoons, 3 Nights, 2 Rest Days, 2 Mornings
C-SHIFT
3 Mornings, 2 Afternoons, 2 Nights, 10 Rest Days, 2 Mornings, 2 Afternoons, 3 Nights, 2 Rest Days, 2 Mornings, 3 Afternoons, 2 Nights, 2 Rest Days
D-SHIFT
10 Rest Days, 2 Mornings, 2 Afternoons, 3 Nights, 2 Rest Days, 2 Mornings, 3 Afternoons, 2 Nights, 2 Rest Days, 3 Mornings, 2 Afternoons, 2 Nights
-E-SHIFT
3 Rest Days, 2 Mornings, 2 Afternoons, 3 Nights, 2 Rest Days, 2 Mornings, 3 Afternoons, 2 Nights, 2 Rest Days, 3 Mornings, 2 Afternoons, 2 Nights, 7 Rest Days
There was a mistake in my file which I have corrected. I have also formatted the planner as desired.
You can copy the last month further (together with the two helper columns to the left of it and change the month in the third column.
Saqib
You can copy the last month further (together with the two helper columns to the left of it and change the month in the third column.
Saqib
Sorry here is the file
Copy-of-Shift-planner.xls
Copy-of-Shift-planner.xls
ASKER
WOW, what can I say to you two, the results the same with two different solutions. I am very impressed at both of you. Thank you very much. I have tried to decipher the 'code', but my Excel although adequate, it does not come anywhere near your knowledge.
Would you each mind if I awarded the points jointly. I believe that is fair.
Regards
tezza73
Would you each mind if I awarded the points jointly. I believe that is fair.
Regards
tezza73
Works for me
ASKER
Saqib
Thanks Saqib, I will wait for akoster. I really appreciate your efforts very much.
Regards
Terence
Thanks Saqib, I will wait for akoster. I really appreciate your efforts very much.
Regards
Terence
ASKER
Hello can you please reopen this thread as I need to award split points:
250 ssaqibh
250 akoster
Thanks
Regards
tezza73
250 ssaqibh
250 akoster
Thanks
Regards
tezza73
Click on the "request attention" link below your question on the right side and request for reopen.
no problems with splitting the points.
Glad to have been of assistance !
although it might look like rocket science or something close to it, i can tell you it is most definitely not !
if you go through the formulae one step at a time and play around by testing what it does and how you can use it for yourself, you'll be at the same level in no-time.
Glad to have been of assistance !
although it might look like rocket science or something close to it, i can tell you it is most definitely not !
if you go through the formulae one step at a time and play around by testing what it does and how you can use it for yourself, you'll be at the same level in no-time.
You are very right, akoster, but if you look at statistics - of people around you - you will find that this is not the case.
I, myself, have taught a number of people working around me some out-of-routine formulas and they use it as long as they are on the project with me. But as soon as the project is over and we part they are no longer using the formulas or functions. I cannot remember someone who is using formulas (or functions by concieving them). I can recall only one person who inherited a set of simple formulas from me and is using them extensively without understanding them. He either copies it off an existing file or off a piece of paper. He cannot reproduce it from scratch.
The message is that you need to take interest in this and you need to spend time. One good resource is Experts-Exchange. If you continue to look at questions and their progress, very soon you would find you want to answer some of them. Once you start that... there is no end to it.
Saqib
I, myself, have taught a number of people working around me some out-of-routine formulas and they use it as long as they are on the project with me. But as soon as the project is over and we part they are no longer using the formulas or functions. I cannot remember someone who is using formulas (or functions by concieving them). I can recall only one person who inherited a set of simple formulas from me and is using them extensively without understanding them. He either copies it off an existing file or off a piece of paper. He cannot reproduce it from scratch.
The message is that you need to take interest in this and you need to spend time. One good resource is Experts-Exchange. If you continue to look at questions and their progress, very soon you would find you want to answer some of them. Once you start that... there is no end to it.
Saqib
ASKER
Thanks all for the kind assistance.
Regards
terence
Regards
terence
Thanks, Terence
Saqib
Saqib