Hi all,
This is my first post to this forum. Hope i get some valuable replies here.
I am trying to create a simple BiWeekly roster (extendable to Monthly) for my workflow support team using either VBA or worksheet functions though i am not able to proceed further than the design.
There are 3 Shifts (Morn, Noon, Night) & 1 reserved Shift (General) for the TL.
These are the following Criteria:
1] Girls cannot work Night Shifts except Morn & Noon Shifts. Boys can work all 3 shifts. Exception is TL=Team Lead.
2] Also the two girls with CRE cannot work together. i.e. If one works a Morn shift, then other will do an Afternoon Shift.
3] Shift is for 6 days and Sunday being a weekly off.
4] Saturday shift will only be a Morn or Noon Shift for both boys and girls.
5] If a boy works Night shift, he cannot work on a Morn Shift the
next day. e.g. Cannot work 10p-7a Night Shift and then continue with
Morn Shift 7a-4p.
6] It Should be a Rotating Shift Roster so that each person gets equal
share of shifts.
7] The Biweekly Dates are displayed in columns and the Employee names
in Rows in Column A.
<table x:str="" style="border-collapse: collapse; width: 538px; height: 241px;" border="0" cellpadding="0" cellspacing="0"><col style="width: 80pt;" width="106"> <col style="width: 91pt;" width="121"> <col style="width: 58pt;" span="5" width="77"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; width: 80pt;" x:num="39661" height="17" width="106">1-Aug-08</td> <td class="xl22" style="width: 91pt;" x:num="" width="121">6</td> <td class="xl24" style="width: 58pt;" x:num="39661" x:fmla="=A1" width="77">1-Aug-08</td> <td class="xl24" style="width: 58pt;" x:num="39662" x:fmla="=+C1+1" width="77">2-Aug-08</td> <td class="xl24" style="width: 58pt;" x:num="39663" x:fmla="=+D1+1" width="77">3-Aug-08</td> <td class="xl24" style="width: 58pt;" x:num="39664" x:fmla="=+E1+1" width="77">4-Aug-08</td> <td class="xl24" style="width: 58pt;" x:num="39665" x:fmla="=+F1+1" width="77">5-Aug-08</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" height="17">
</td> <td class="xl22">
</td> <td class="xl23" x:fmla="=TEXT(C1,"DDD")">Fri</td> <td class="xl23" x:fmla="=TEXT(D1,"DDD")">Sat</td> <td class="xl23" x:fmla="=TEXT(E1,"DDD")">Sun</td> <td class="xl23" x:fmla="=TEXT(F1,"DDD")">Mon</td> <td class="xl23" x:fmla="=TEXT(G1,"DDD")">Tue</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" height="17">Alinda D'souza</td> <td class="xl22">FEMALE</td> <td class="xl22">M</td> <td class="xl22">A</td> <td class="xl22">OFF</td> <td class="xl22">A</td> <td class="xl22">M</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" height="17">Harrison Methari</td> <td class="xl22">MALE</td> <td class="xl22">M</td> <td class="xl22">M</td> <td class="xl22">OFF</td> <td class="xl22">A</td> <td class="xl22">A</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" height="17">Inas Fernandes</td> <td class="xl22">MALE</td> <td class="xl22">N</td> <td class="xl22">A</td> <td class="xl22">OFF</td> <td class="xl22">A</td> <td class="xl22">M</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" height="17">Preston Martins</td> <td class="xl22">MALE</td> <td class="xl22">N</td> <td class="xl22">M</td> <td class="xl22">OFF</td> <td class="xl22">M</td> <td class="xl22">M</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" height="17">Savio Borges</td> <td class="xl22">TL</td> <td class="xl22">GEN</td> <td class="xl22">GEN</td> <td class="xl22">OFF</td> <td class="xl22">GEN</td> <td class="xl22">GEN</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" height="17">Jubiana Barboza</td> <td class="xl22">CRE</td> <td class="xl22">A</td> <td class="xl22">M</td> <td class="xl22">OFF</td> <td class="xl22">M</td> <td class="xl22">A</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" height="17">Diana Fernandes</td> <td class="xl22">CRE</td> <td class="xl22">M</td> <td class="xl22">A</td> <td class="xl22">OFF</td> <td class="xl22">A</td> <td class="xl22">M</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" x:num="" height="17">0</td> <td class="xl22">FEMALE</td> <td class="xl22">A</td> <td class="xl22">M</td> <td class="xl22">OFF</td> <td class="xl22">M</td> <td class="xl22">A</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" x:num="" height="17">0</td> <td class="xl22">CRE</td> <td class="xl22">M</td> <td class="xl22">A</td> <td class="xl22">OFF</td> <td class="xl22">A</td> <td class="xl22">M</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" x:num="" height="17">0</td> <td class="xl22">FEMALE</td> <td class="xl22">A</td> <td class="xl22">M</td> <td class="xl22">OFF</td> <td class="xl22">M</td> <td class="xl22">A</td> </tr> </tbody></table>
Also, another question:
Regarding the two CRE girls, how can i make sure that both r not working the same shift on a day? If someone knows a formula for this, which uses Rand(), Row(), Column(), Mod() etc to alternate their shifts based on their current row and column, would really appreciate this help.
Will appreciate some favourable response.
regards,
This is my first post to this forum. Hope i get some valuable replies here.
I am trying to create a simple BiWeekly roster (extendable to Monthly) for my workflow support team using either VBA or worksheet functions though i am not able to proceed further than the design.
There are 3 Shifts (Morn, Noon, Night) & 1 reserved Shift (General) for the TL.
These are the following Criteria:
1] Girls cannot work Night Shifts except Morn & Noon Shifts. Boys can work all 3 shifts. Exception is TL=Team Lead.
2] Also the two girls with CRE cannot work together. i.e. If one works a Morn shift, then other will do an Afternoon Shift.
3] Shift is for 6 days and Sunday being a weekly off.
4] Saturday shift will only be a Morn or Noon Shift for both boys and girls.
5] If a boy works Night shift, he cannot work on a Morn Shift the
next day. e.g. Cannot work 10p-7a Night Shift and then continue with
Morn Shift 7a-4p.
6] It Should be a Rotating Shift Roster so that each person gets equal
share of shifts.
7] The Biweekly Dates are displayed in columns and the Employee names
in Rows in Column A.
<table x:str="" style="border-collapse: collapse; width: 538px; height: 241px;" border="0" cellpadding="0" cellspacing="0"><col style="width: 80pt;" width="106"> <col style="width: 91pt;" width="121"> <col style="width: 58pt;" span="5" width="77"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; width: 80pt;" x:num="39661" height="17" width="106">1-Aug-08</td> <td class="xl22" style="width: 91pt;" x:num="" width="121">6</td> <td class="xl24" style="width: 58pt;" x:num="39661" x:fmla="=A1" width="77">1-Aug-08</td> <td class="xl24" style="width: 58pt;" x:num="39662" x:fmla="=+C1+1" width="77">2-Aug-08</td> <td class="xl24" style="width: 58pt;" x:num="39663" x:fmla="=+D1+1" width="77">3-Aug-08</td> <td class="xl24" style="width: 58pt;" x:num="39664" x:fmla="=+E1+1" width="77">4-Aug-08</td> <td class="xl24" style="width: 58pt;" x:num="39665" x:fmla="=+F1+1" width="77">5-Aug-08</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" height="17">
</td> <td class="xl22">
</td> <td class="xl23" x:fmla="=TEXT(C1,"DDD")">Fri</td> <td class="xl23" x:fmla="=TEXT(D1,"DDD")">Sat</td> <td class="xl23" x:fmla="=TEXT(E1,"DDD")">Sun</td> <td class="xl23" x:fmla="=TEXT(F1,"DDD")">Mon</td> <td class="xl23" x:fmla="=TEXT(G1,"DDD")">Tue</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" height="17">Alinda D'souza</td> <td class="xl22">FEMALE</td> <td class="xl22">M</td> <td class="xl22">A</td> <td class="xl22">OFF</td> <td class="xl22">A</td> <td class="xl22">M</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" height="17">Harrison Methari</td> <td class="xl22">MALE</td> <td class="xl22">M</td> <td class="xl22">M</td> <td class="xl22">OFF</td> <td class="xl22">A</td> <td class="xl22">A</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" height="17">Inas Fernandes</td> <td class="xl22">MALE</td> <td class="xl22">N</td> <td class="xl22">A</td> <td class="xl22">OFF</td> <td class="xl22">A</td> <td class="xl22">M</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" height="17">Preston Martins</td> <td class="xl22">MALE</td> <td class="xl22">N</td> <td class="xl22">M</td> <td class="xl22">OFF</td> <td class="xl22">M</td> <td class="xl22">M</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" height="17">Savio Borges</td> <td class="xl22">TL</td> <td class="xl22">GEN</td> <td class="xl22">GEN</td> <td class="xl22">OFF</td> <td class="xl22">GEN</td> <td class="xl22">GEN</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" height="17">Jubiana Barboza</td> <td class="xl22">CRE</td> <td class="xl22">A</td> <td class="xl22">M</td> <td class="xl22">OFF</td> <td class="xl22">M</td> <td class="xl22">A</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" height="17">Diana Fernandes</td> <td class="xl22">CRE</td> <td class="xl22">M</td> <td class="xl22">A</td> <td class="xl22">OFF</td> <td class="xl22">A</td> <td class="xl22">M</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" x:num="" height="17">0</td> <td class="xl22">FEMALE</td> <td class="xl22">A</td> <td class="xl22">M</td> <td class="xl22">OFF</td> <td class="xl22">M</td> <td class="xl22">A</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" x:num="" height="17">0</td> <td class="xl22">CRE</td> <td class="xl22">M</td> <td class="xl22">A</td> <td class="xl22">OFF</td> <td class="xl22">A</td> <td class="xl22">M</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" x:num="" height="17">0</td> <td class="xl22">FEMALE</td> <td class="xl22">A</td> <td class="xl22">M</td> <td class="xl22">OFF</td> <td class="xl22">M</td> <td class="xl22">A</td> </tr> </tbody></table>
Also, another question:
Regarding the two CRE girls, how can i make sure that both r not working the same shift on a day? If someone knows a formula for this, which uses Rand(), Row(), Column(), Mod() etc to alternate their shifts based on their current row and column, would really appreciate this help.
Will appreciate some favourable response.
regards,
