?
Solved

Designing an Excel Spreadsheet to display Shift Pattern

Posted on 2011-04-22
19
Medium Priority
?
622 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:tezza73
  • 8
  • 6
  • 4
18 Comments
 
LVL 19

Expert Comment

by:Arno Koster
ID: 35446547
The pattern used seems ok to me, but how can we help you with it ?
0
 
LVL 19

Assisted Solution

by:Arno Koster
Arno Koster earned 1000 total points
ID: 35446595
To get you started visualizing things,

will this be any help to you ?

you can enter night shifts with 'n', morning shifts with 'm' and so on.
colours will automatically be applied and to the right and below you can verify the totals for each shift and day.

one thing that pops up is that shift B indeed is missing a night (marked red in the excel file)

 shift-pattern.xlsx
0
 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 1000 total points
ID: 35446619
I have worked out a spreadsheet which can check the data and can prepare a roster.

Rows 19-22 shows total assignments to a shift. From there it can be seen that shift B is a night short.

Columns I to L below row 27 shows where shifts are missing or doubled.

Columns A to g is the duty roster.

Saqib
Shift-planner.xls
0
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 

Author Comment

by:tezza73
ID: 35446645
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
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 35446665
Take a look at my file. you can extend columns A to g as far as you like
0
 

Author Comment

by:tezza73
ID: 35446686
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
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 35446793
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
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 35446797
Sorry here is the file
Copy-of-Shift-planner.xls
0
 
LVL 19

Expert Comment

by:Arno Koster
ID: 35446865
no, not harder. only more work ;-)

 shift-pattern.xlsx
0
 

Author Comment

by:tezza73
ID: 35447314
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
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 35447735
Works for me
0
 

Author Comment

by:tezza73
ID: 35448770
Saqib

Thanks Saqib, I will wait for akoster. I really appreciate your efforts very much.

Regards

Terence

0
 

Author Comment

by:tezza73
ID: 35449443
Hello can you please reopen this thread as I need to award split points:

250 ssaqibh
250 akoster

Thanks
Regards
tezza73
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 35449469
Click on the "request attention" link below your question on the right side and request for reopen.
0
 
LVL 19

Expert Comment

by:Arno Koster
ID: 35449617
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.
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 35449682
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
0
 

Author Closing Comment

by:tezza73
ID: 35470996
Thanks all for the kind assistance.
Regards
terence
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 35471807
Thanks, Terence

Saqib
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question