Workflow Roster

[sifar786]

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,

Workflow Roster - Test.xls
	A	B	C	D	E	F	G	H	I	J
1	1-Aug-08	6	1-Aug-08	2-Aug-08	3-Aug-08	4-Aug-08	5-Aug-08	6-Aug-08	7-Aug-08	8-Aug-08
2			Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri
3	AlindaD'souza	FEMALE	7	8	OFF	10	11	12	7	8
4	HarrisonMethari	MALE	2	3	OFF	5	6	1	2	3
5	InasFernandes	MALE	3	4	OFF	6	1	2	3	4
6	PrestonMartins	MALE	4	5	OFF	1	2	3	4	5
7	SavioBorges	TL	GEN	GEN	OFF	GEN	GEN	GEN	GEN	GEN
8	JubianaBarboza	CRE	12	7	OFF	9	10	11	12	7
9	DianaFernandes	CRE	7	8	OFF	10	11	12	7	8
10		CRE	8	9	OFF	11	12	7	8	9
11		CRE	9	10	OFF	12	7	8	9	10
12		CRE	10	11	OFF	7	8	9	10	11
13
14	Roster
15	1-Aug-08	6	1-Aug-08	2-Aug-08	3-Aug-08	4-Aug-08	5-Aug-08	6-Aug-08	7-Aug-08	8-Aug-08
16			Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri
17	AlindaD'souza	FEMALE	M	A	OFF	A	M	A	M	A
18	HarrisonMethari	MALE	M	M	OFF	A	A	M	M	N
19	InasFernandes	MALE	N	A	OFF	A	M	M	N	N
20	PrestonMartins	MALE	N	M	OFF	M	M	N	N	A
21	SavioBorges	TL	GEN	GEN	OFF	GEN	GEN	GEN	GEN	GEN
22	JubianaBarboza	CRE	A	M	OFF	M	A	M	A	M
23	DianaFernandes	CRE	M	A	OFF	A	M	A	M	A
24	0	FEMALE	A	M	OFF	M	A	M	A	M
25	0	CRE	M	A	OFF	A	M	A	M	A
26	0	FEMALE	A	M	OFF	M	A	M	A	M
Sheet4

 

[Gerald Higgins]

This is my first post to this forum.

No it isn't.
Here's your first one.
http://www.mrexcel.com/forum/showthread.php?t=335346

Your request is very long and complicated, and it's completely unclear what exactly you need help with.
Are you looking for someone to create the whole roster for you ?
If so, say so.

Good luck !

 

[sifar786]

Hi Gerald,

Its indeed my first post.......i just edited my post and added the spreadsheet y'day when i found out this cool Addin called Colo HTMLMaker.

No, i am not asking anyone to build the roster for me. i just need help to incorporate the following requirements in my formulae.

The above table is a sort of Initial Calculation table. It contains a formula in individual cells: e.g. cell D3

=IF(AND(OR($B3="CRE",$B3="FEMALE"),D$2<>"Sun"),IF(MOD(ROW(D3)+COLUMN(D$2),$B$1)<7,MOD(ROW(D3)+COLUMN(D$2),$B$1)+7,MOD(ROW(D3)+COLUMN(D$2),$B$1)),IF(AND($B3="TL",D$2<>"Sun"),"GEN",IF(AND(MOD(ROW(D3)+COLUMN(D$2),$B$1)<6,D$2<>"Sun"),MOD(ROW(D3)+COLUMN(D$2),$B$1)+1,"OFF")))



====================
Now, if you see, the below table is the Roster Table and i have added individual formula in each cell, which is:

=IF(D3="GEN","GEN",IF(D3="OFF","OFF",IF(D3>6,CHOOSE(D3,misc!$H$2,misc!$H$3,misc!$H$4,misc!$H$5,misc!$H$6,misc!$H$7,misc!$H$8,misc!$H$9,misc!$H$10,misc!$H$11,misc!$ H$12,misc!$H$13,misc!$H$14,misc!$H$15),IF(D$16="Sat",CHOOSE(D3,misc!$F$2,misc!$F$3,misc!$F$4,misc!$F$5,misc!$F$6,misc!$F$7),CHOOSE(D3,misc!$G$2,misc!$G$3,misc!$G$4,misc!$G$ 5,misc!$G$6,misc!$G$7)))))

As you can see from table, i am not able to sort out Criteria for Females and CRE Shift and Saturday. Females cannot work Night Shift & CRE can only work Alternate Shifts. The good part is that both CRE are Females and they also cannot work Night shift.

Trying to figure out a some random formula which will give alternate shifts to each CRE. Say, for day1, if one CRE has a Morn Shift, then other CRE will only get an Noon Shift. No Night Shifts for Females and CRE.

I am attaching the Misc Sheet for you to see.


Gerald, if you want me to mail you this spreadsheet so that you can have a look, please let me know. Any help would be greatly appreciated.

Workflow Roster - Test.xls
	A	B	C	D	E	F	G
1	SHIFTS	NAMES	GENDER	NOS	TL/FEMALES	MALES	CRE/FEMALES
2	7am-4pm	AlindaD'souza	Female	1	M	M	M
3	9am-6pm	HarrisonMethari	Male	2	A	A	A
4	1pm-10pm	InasFernandes	Male	3	M	M	M
5	10pm-7am	PrestonMartins	Male	4	A	N	A
6		SavioBorges	TL	5	M	N	M
7		JubianaBarboza	CRE	6	A	A	A
8		DianaFernandes	CRE				M
9							A
10							M
11							A
12							M
13							A
MISC

 

[Stormseed]

hi dude (sifar)

I was watching your post for sometime now. Candidly, I would say, no member on the Board would be interested in reading your *whole* chapter. The request you made in your thread is very long and complicated. It is indeed very much necessary to explain your problem with details and always ensure that you post *one* question at a time. I do not deny that many a times users get lucky with their threads because someone on the board repsonds to these types of questions but this happens once in a blue moon !

We do not want to discourage you as it is your first or second post. All of us on the board are here so that we can help each other to come out of problems arising in our daily routine work - but - in a proper and effective manner. You may have experienced that the discussion boards for any type of software across the internet would entertain only one question at a time. This is because, there exists a number of other users across the globe who are searching for that particular issue that you are experiencing. There are other discussion forums for Excel too. The main objective of such forums is to *help* people on these forums to share and gain knowledge thereby resolving their issues on a particular subject. Also, it is very important to understand that the users on the Board are *not* paid for their work ! So we cannot afford to be selfish or self centered regarding our posts. No person is going to complete or take up and finish the *whole* project for you. The users across the internet, would type in the keywords to find a response pertaining to the issue that they are experiencing. Obviously, they are not going to search for *workflow roster* !!

Hence it is very necessary to maintain the rhythm of *one* question at a time so that other users with similar problem are addressed effectively. You have put in all your requests in one go which is *not correct* and *not feasible* for the members on the board to *read* your question.

Try to post a part of the problem that you are expereincing with a valid and meaningful subject line. Post the formula or the line of code or the part of your spreadsheet with which you are experiencing an issue. Mention where are you stuck, alongwith the details. I bet, you will have the answers within minutes on this board ! Make your thread small, simple and precise. A lot of people on the Board discard your thread because of the only reason that your question is long or complicated or they are *multiple* questions.

You would never be able to imagine the *density* of the knowledge, people share on this Board

Good luck.

 

[sifar786]

Hi StormSeed,

Thanks for your inputs.

Since you got me wrong, I just needed ideas and not someone to do my work. Never made a Roster before, therefore you see the confusion in my request.

I Agree with your suggestions. Thnx.

BTW, I already solved the problem.


Regards,

 

[PaddyD]

"BTW, I already solved the problem."

Perhaps you could share how you did it, in case someone has a similar problem in the future and comes across this thread via a search.