Workflow Roster

Status
Not open for further replies.

sifar786

New Member
Joined
Aug 9, 2008
Messages
15
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,

 
Status
Not open for further replies.

Forum statistics

Threads
1,081,556
Messages
5,359,549
Members
400,533
Latest member
fpenning

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top