Prev: Import selected worksheet from another workbook
Next: Error: Microsoft ODBC Driver Manager Data Source name not found
From: beancurd on 4 Mar 2010 11:35
Thanks you very much, Joel.
But after run the marco, I find that missing generate job duties (A,
A/
M, A/R, A/E, N and N/R)* on schedule and the Employee 10 is missing
shift duty on Public
Holiday......Also, find out the schedule if on public holiday but not
in bill date, it generate 4 staff on duty, but it should be only 2 AM
staff and 1 PM staff only.....Pls give me a big hand~~

* A only for Day shift
A/M only for Day shift but need to handling some Specify job duty1
A/R only for Day shift but need to handling some Specify job duty2
A/E only for Day shift but need to handling some Specify job duty3
N only for Night shift
N/R only for Night shift but need to handling some Specify job duty4


From: joel on 4 Mar 2010 13:09

I've been working on the code a little bit each day. I expect to be
finish tonight. I will check to make sure the problems you listed gets
fixed.

I'm generating the schedule in the following order

1) Holiday
2) Weekends - This is the most complicated sections. I'm scheduling
Saturday and Sunday together. I schedule on person on the AM shift for
both Saturday and Sunday and one person one the PM shift for both
Saturday and Sunday. Then I schedule the remaining people trying to not
schedule the same person on more than one shift. I also try not toe
schedule a person when they work a holiday from Wednesday to Tuesday
(with the weekend in the middle). And I try not to schedule a person
who works Friday Night on a holiday on Saturday morning then try not to
schedule a person who works on a Monday AM holiday on sunday night.

Meeting all these conditions and meeting the number of people you
required to work is impossible. So there will be a few times when all
these conditions are not met. Usually the some of people who are off
for a holiday will also have to work the weekend.

There is a trade off between the reuirement for people to have both
Saturday and Sunday Off and having people working both Saturday and
Sunday. I don't schedule people to have off both Sturday and Sunday
since with holidays and billing cyles this is impossible. Instead I
rotate the pople who have off both days so everybody eventually gets
both days off and equal number of times. I do the same thing with
working only Saturday and working only Sunday. So if you look at the
schedule on a monthly basis you may not see everybody getting off both
Saturday and Sunday together. But you will see the schedule rotating as
you go through the year.

3) I then schedule nights making sure pople don't work morre than 5
days in a row. Try not to schedule people Friday night after I
scheduled them Saturday morning. And I also try not to schedule people
a night before a holiday when they are work the next morning.


4) Finally I will schedule the day shift. I will need to use one of
the shift workers on Friday to meet the demand for 5 people.


One question. Is there a minimum number of hours each shift person
need to work? My plan is to only schedule people to meet the scedule
requirements and not to schedule any more people than the requirements.
I'm not sure if shift workers will get too many days off.


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=183222

[url="http://www.thecodecage.com"]Microsoft Office Help[/url]

From: beancurd on 5 Mar 2010 12:21
Oh joel, you are so helpful and "Thanks you" x 100 million.
Day shift 9:00am - 18:30pm
Night shift 12:00pm - 21:30pm
From: joel on 5 Mar 2010 12:40

I got everything working last night but on the 78th day of the year the
macro couldn't fill a shift person because anybody available would of
worked for more than 5 days in a row. What I'm going to do is highlight
the cell in the worksheet Red and let the code continue. Then see if
there is a way to easily fix the problem. I think it occured on a
Friday day shift where I already schedule the person to work the
weekend. I have a few algoritms I thinking of using to stop this from
happening. Right now my queing system is comparing looking for the
person who worked the fewest number of days. Instead if I calculate the
number of days a person is working an individual week (including the
weekends and holidays I already scheduled) the problem may not occur.

I'm also getting a number of cases where a person is working a night
shift and is also scheduled to work the next day shift. I want to see
the number of times these issue occurs and see if I can easily fix the
problem.


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=183222

[url="http://www.thecodecage.com/forumz/"]Excel Live Chat[/url]

From: beancurd on 5 Mar 2010 13:01
Hi joel,

did you set the marco, these are 4 staff (include the supervisor) is
working on Day shift only (office hour 9:00-18:30) and day off on Sat,
Sun and Public holiday?
First  |  Prev  |  Next  |  Last
Pages: 1 2 3 4
Prev: Import selected worksheet from another workbook
Next: Error: Microsoft ODBC Driver Manager Data Source name not found