logic problem: help needed

[dcleave]

Hi,
I am using Excel to program a staff rota. This is posing me a few
problems, and I would appreciate any help.

Imagine a block of cells 50 rows high and 28 columns across. Each row
represents the workpattern of a member of staff, and each column is one
day.

A cell can be empty (Day off) or have a 'D' (day-shift)

Each person has to work 13 shifts in the 28 days

I want a macro to enter the 'D's into the sheet, but there are some
rules that must be followed.

1) Each row has 13 'D's.

2) Each column should have roughly the same number of 'D's (ie the
same number of staff) - a difference of 1 is OK.

3) D's should be clustered into 2's & 3's in each row

4) Each row will start out with 5 'D's in place that have been entered
by hand: these cannot be moved.

I am finding this extremely tricky. The problem I have is following one
rule tends to cause another to be broken. ie I can get the 'D's to
cluster or to have the same number in each column, but not both at the
same time.

I will be very grateful for any help.

David

 

[Guest]

Hi

Under point 4 you state that each row starts out with 5 D's in place. Do
you mean that the first 5 days, everybody works? Or do you mean that these
five days are spread through the 28 days? If the latter, how is this done?

 

[dcleave]

Hi.
Thanks for your interest in my problem.

To clarify, all staff are entitled to request up to 5 shifts, which
will be honoured as long not too many request the same day.

In practical terms, this translates as each row having 5 fixed 'D's in
place, before the macro is run, which cannot be moved. These are spread
randomly across the 28 days.

Any help would be gratefully received. I am not looking for people to
write the complete macro for me (although that would be nice!), but to
give suggestions as to how I can get a macro to cluster the 'D's in
each row whilst still maintaining the same number of 'D's in each
column.

David

 

[Guest]

Hi

While I fully understand what you are trying to achieve here, I do not agree
with your modus operandi.

Having worked shifts, and having planned shifts all my life, I have found
that a shift system rotating on a set pattern works best for all concerned.
Too often, when you try and accomodate the wishes of everybody, you end up
annoying everybody, and people start complaining about favouritism. That's
the one end of the stick.

The other side of the story is that computers are not human, and work to set
logic. For a macro to accomodate peoples wishes, which are not according to
a set pattern, is not easy.

I would therefore recommend a different approach, and that is to use a
rotaline system. You do however have a couple of problems, and I do not know
whether you are flexible about this or not.

If you could, it would be better for you to reduce your staff by 2, and
increase the shifts per person to 14 in 28 days.

Again, I do not know whether this is possible or not, nor whether you are
prepared to go this route. Why do I say this? At present you have 50
bodies, and you want each to work 13 shifts in 28 days. Therefore 650 shift
in 28 days. This means that on each of the 28 days you require 23.21428571
bodies on duty. That is really a tough call.

If you have 48 staff member, each working 14 shifts, you will have 24 on
duty every day, and that is an easy roster to work out (2 days on, 2 days
off). That in turn means that each worker gets the chance to work on each
day of the week, and you cannot get fairer than that!

I know I am not solving your initial question, but think about it!

Regards

(e-mail address removed)_2nd_at. Randburg, Gauteng, South Africa

 

[dcleave]

Hi,
Thanks for your reply:

This is all getting off the point, but allow me to clarify things. Th
problem that I set is actually a gross simplification of the actua
situation. The workplace I run is a busy intensive care unit in the UK
There are 70 staff working a mixture of day & night shifts (12h
shifts), with occasional management & study days thrown in. Most wor
13 shifts in 28 days, but some work less. The are many different grade
of staff, which all has to be taken into account. The trouble with
fixed rota pattern is that it although it is easy to write, it tends t
discriminate against women with children, or people who have partner
who work irregular hours. We like to offer a degree of flexible workin
so that our staff with childcare comittments (the majority) can have a
element of choice/flexibility. This does mean that the rota is
nightmare to write. It used to be done by hand on a huge sheet o
paper, and take about 4 days to complete. errors were common. I move
it to excel, and it now takes me one full day. All the adding up o
staff numbers etc are automated, but I still feel that Excel has mor
to offer.

To get back to the point:

The solution to the following problem would be a huge help to me:

How do I get the 'D's in a row to cluster in the way I want (2's
3's), whilst still maintaining the same number in each column?

I would hope to extrapolate the solution to this problem to othe
problems.

Davi

 

[Guest]

Hi David

Thanks for the feedback! I appreciate your problem, don't get me wrong.
Allow me some time to play around with what you have given me, and I'll come
back. I am however not very hopefull that I will be able to solve it with a
macro. What I have used, is a simple formula to add the number of D's per
column and per row, which of course makes it a lot easier to work with. I
used =COUNTIF(B1:B51,"D") for the columns and =COUNTIF(A2:AC2,"D") for the
rows. The problem with a macro is that while it will insert D's in 2's and
3's, and even consider the 5 fixed "D"'s, it will not consider the overall
pattern you try to maintain. Bu as I said, let me play around with it, and
see what I can come up with.