Workflow Roster

[noname]

Hi Max,

Its been a long time since i got in touch with you. I have been an
avid fan of yours since the time you helped me out with my queries on
groups.google.com and by sharing your scheduling files on
Savefile.com.

I am trying to create a simple biweekly roster for my workflow support
team using either VBA or worksheet functions though i am not able to
proceed further than the design.

These are the following Criteria:

1] Girls cannot work Night Shifts. Only for Boys.
2] Also the two girls with CRE cannot work together.
3] Shift is for 6 days and Sunday being a weekly off.
4] Saturday shift will only be a Morning Shift or an Afternoon Shift.
Girls can work on Saturdays on this shifts.
5] Savio Borges is a Team Lead and so will only work General Shift
(9a-6p)
6] Its a Biweekly Roster.
7] If a person works a Night shift, he cannot work a Morn Shift the
next day. e.g. Cannot work 10p-7a Night Shift and then continue with
Morn Shift 7a-4p.
8] It Should be a Rotating Shift Roster so that each person gets equal
share of shifts.
9] There are 3 Shifts to be distributed, Morn, A'Noon & Night. General
Shift only reserved for Team Lead i.e Savio Borges.
10] The Biweekly Dates are displayed in columns and the Employee names
in Rows in Column A.


Please help ASAP!

Eijaz Sheikh

 

[Max]

While I'm happy that you found my free archives to be of use,
I regret that I'm unable to help you on this query.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:16,700 Files:356 Subscribers:53
xdemechanik

 

[noname]

Hi Max,

I can write code to create the Design (formatting and Dates generation
and getting the employees, but am unable to understand what type of
logic or algorithm to encompass the various scenarios as outlined
above.


I am sure you have your own reasons for not helping me, though i
believe that you may have some ideas on how to approach this problem.

Any ideas i would really appreciate it.

Warm Regards,

Eijaz

While I'm happy that you found my free archives to be of use,
I regret that I'm unable to help you on this query.
--
Max
Singaporehttp://savefile.com/projects/236895
Downloads:16,700 Files:356 Subscribers:53
xdemechanik

Hi Max,

Its been a long time since i got in touch with you. I have been an
avid fan of yours since the time you helped me out with my queries on
groups.google.com and by sharing your scheduling files on
Savefile.com.

I am trying to create a simple biweekly roster for my workflow support
team using either VBA or worksheet functions though i am not able to
proceed further than the design.

These are the following Criteria:

1] Girls cannot work Night Shifts. Only for Boys.
2] Also the two girls with CRE cannot work together.
3] Shift is for 6 days and Sunday being a weekly off.
4] Saturday shift will only be a Morning Shift or an Afternoon Shift.
Girls can work on Saturdays on this shifts.
5] Savio Borges is a Team Lead and so will only work General Shift
(9a-6p)
6] Its a Biweekly Roster.
7] If a person works a Night shift, he cannot work a Morn Shift the
next day. e.g. Cannot work 10p-7a Night Shift and then continue with
Morn Shift 7a-4p.
8] It Should be a Rotating Shift Roster so that each person gets equal
share of shifts.
9] There are 3 Shifts to be distributed, Morn, A'Noon & Night. General
Shift only reserved for Team Lead i.e Savio Borges.
10] The Biweekly Dates are displayed in columns and the Employee names
in Rows in Column A.

Please help ASAP!

Eijaz Sheikh

 

[Max]

I am sure you have your own reasons for not helping me ..

No reason other than it's out of my depth. I don't know. Pl note that I
asked you to post in the newsgroups so that your query will be visible to
all responders, and you increase your chances of getting a response from
those who may something to offer you.

though i believe that you may have some ideas
on how to approach this problem.

Your criteria/requirements are complex. I don't know. You could try trial n
error by hand, manually experimenting with various scenarios, tweaking each
iteratively until you can hopefully converge on 1 or 2 possible scenarios
which satisfies the majority of the requirements. It's not easy.

--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,000 Files:358 Subscribers:55
xdemechanik
---

 

[AltaEgo]

Eijaz

There was a question to this NG some weeks back that may help you get
started.

http://groups.google.com/group/micr...529c5ae5a8/65ad9067b0662ddf?#65ad9067b0662ddf

You will need to solve complexities involving when females and leaders can
work outside the basic rota. Possibly, one rota for females, one rota for
males and take turns swapping with the leader to ensure equal distribution.

As Max says, your criteria/requirements are complex. I doubt the above will
help you much at all. You may save yourself a lot of time and frustration
if you search for a commercially written solution. I would not even suggest
a commercial solution on the criteria you gave, only suggest that you try
these links:


http://www.google.com.au/search?hl=...ial&hs=qgb&q="excel+roster"&btnG=Search&meta=

http://www.google.com.au/search?hl=...-GB:official&q="excel+rota"&btnG=Search&meta=


http://www.google.com.au/search?q=w...s=org.mozilla:en-GB:official&client=firefox-a

 

[noname]

Hi All,

I am almost done with the roster using worksheet functions. Only stuck
with 1 thing and that is to alternate the CRE girls shifts so that
they donot work same shift on same day.

I want some random formula (using MOD(), ROW(), COLUMN(), RAND() etc)
which i can inject in my own formula using an IF statement. e.g.

IF(,,IF(B9)="CRE", ...then some formula...,some formula,IF(,,))




DATE
1-Aug-08 Gender 1-Aug-08 2-Aug-08 3-Aug-08 4-Aug-08 5-
Aug-08
Fri Sat Sun
Mon Tue
Alinda D'souza FEMALE M A OFF A
M
Harrison Methari MALE M M OFF A
A
Inas Fernandes MALE N A OFF A
M
Preston Martins MALE N M OFF M
M
Savio Borges TL GEN GEN OFF GEN GEN
Jubiana Barboza CRE A M OFF M A
Diana Fernandes CRE M A OFF A M


If you want a copy of the file which i have made, please let me know
so that i can mail it to you.

Any help would be much appreciated.