Shift Roster on Excel, pls help

[beancurd]

Who can help me to auto generate roster on excel, it is very
complicate...pls help...

1) bill date (5, 7, 11, 14, 17, 21, 25 and end of month) need to 6 day
3 night, but if Saturday, Sunday or Public Holiday need to 2 day 2
night
2) non bill date only need to 6 day 2 night staff
3) non bill date if Saturday, Sunday or Public Holiday need to 2 day
and 1 night
4) if on Friday, need to 5 day 3 night
5) on shift staff, each staff need to have 1 double off on roster and
one Saturday off, two Sun off another 2 off on weekend, they cannot
continuous more then 5 days work and cannot last day on NIGHT shift,
next day on DAY shift
6) if Staff A on night duty, and that night only 2 staff or 1 staff,
it may be additional add 1 more staff on night shift too

10 Staff in a Team, 4 staff is not on shift, that mean only on duty at
day and day off on Sat, Sun and Public Holiday; another 6 staff need
to on shift duty, how can I to be fair to automatically generate a
roster? A roster basic on 4 week, start on Monday.

More information, all staff must be on duty job (except one of
department head, he is not on shift duty, only duty at day and day off
on Sat, Sun and Public Holiday)

A = day
N = night
A/R = duty 1
A/E = duty 2
A/M = duty 3 (except on Sat, Sun and Public Holiday)

 

[beancurd]

Hi Joel,

sorry for mis-information and very thanks you your big help.

On Shift staff (6 staff), total day off is 7 days, it must be include
1 time 2 continuous day off, 1 day off on Saturday, one day off on
Sunday, another 3 day off on weekday; Also they cannot continuous more
then 5 days work and cannot last day on NIGHT shift, next day on DAY
shift

10 employees include the supervisor, 4 staff (include the supervisor)
is office hour work 9:00-18:30; another 6 staff is Shift staff, that
mean they need to report duty on DAY shift (9:00-18:30) or NIGHT shift
(12:00-21:30)

How about the job duties schedule, how can I to be fair......

all staff must be on duty job (exclude one of supervisor)

A = day
N = night
N/R = night job duty
A/R = day job duty 1
A/E = day job duty 2
A/M = day job duty 3 (NOT include on Sat, Sun and Public Holiday)

 

[beancurd]

Hi Joel,

Can you send me your file to me, because I got error message on VBA.
Thanks!

 

[beancurd]

And one Big problem, some staff have special request on roster, (e.g.
Staff A want to DAY shift on 13/5/2010, Staff B want to NIGHT shift on
14/05/2010, Staff C want to OFF on 15/5/2010, sometimes have special
request on annual leave too). If Public holiday, will be get
additional one day compensate off , (e.g. on Apr have one day public
holiday, so that the total day off should be 7 days + 1 day compensate
off)….

This is too hard and very complicate roster…I’m very headache to
handle this roster on fair…

 

[beancurd]

Hi Joel,

Shown error message when I run the VBA, "Compile error: Cannot define
a Public user-defined type within an object module"

Pls Help~~

 

[beancurd]

Thanks you very much, Joel.
But after run the marco, I find that missing generate job duties on
schedule and the Employee 10 is missing shift duty on Public
Holiday......Pls give me a big hand~~

 

[beancurd]

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

 

[beancurd]

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

 

[beancurd]

Oh joel, you are so helpful and "Thanks you" x 100 million.
Day shift 9:00am - 18:30pm
Night shift 12:00pm - 21:30pm

 

[beancurd]

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?

 

[beancurd]

humm....is it no way to solve this problem?
if the shift staff can work 6 days, can help on this situation? but
need to better fewest time for the shift staff working on 6 days and
must to be fair.

 

[beancurd]

Oh no, anyone can help me?? Joel~~where are you??