Employee Scheduling in Excel

[Mayrie]

My Problem:
I currently create my employee schedule in excel and I am trying to find a way to decrease the time I spend entering date. Currently worksheet A lists the employee name in the first column and the days of the week as column headers, where I input the employee shifts for the week. This view is good for the employees because it shows them their total hours for the week. But I need to make sure we have the right staffing in the different departments, we have 6 job codes. Right now I manually enter the data onto worksheet B, the set up of B is not ideal. I have the days fo the week listed as column headers and the job codes in the first column. I basically type in the shift and employee name this table to see staffing by department. This ends up taking a ridiculous amount of time.

What I Want to Do:
Worksheet A: row headers-employee name, column headers-day of the week, start and end times desplayed in table
Worksheet B: row headers-job code, column headers-day of the week, employee name and shift displayed in table
I want to be able to enter only the shift times into either table and have the other table pull the information. The problem is that I want to be able to sort my list of employees (depending on job code, shift, a to z etc) and not have it mess up the other worksheet. I thought a pivot table would work best for this, but I cannot figure out the best way to set up my worksheet to allow for all the data to be on the pivot table.

Any suggestions would be greatly appreciated!

 

[asad]

Is it that one job code includes many time shifts? Do you have a set number of shifts for each job code? Do they change or are they going to be same all the time?
We can just include time shift along with the job code in the actual roster where you will be displaying job code, time shift and then start time , finish time and finally paid hours.

Is that how you want it to work?

 

[Mayrie]

Yes, one job code will have many shifts in one day. There is no set number of shifts for each job code, although there is a minimum number, which changes based on the day. The shifts vary depending on business, so some days I need only 2 shifts of job code 2 but on others I need 4 shifts of the same job code. For the shift times, I have some constant shifts like job code 1 opening shift is 11:15 to 6, and job code 1 closing is 6 to 1215. but I may add more shifts depending on needs.

I was considering adding each possible shift in the roster, but it would get pretty long and I don't know if it would be beneficial. For example here are some shifts I may use in one day (let alone a week). I have listed the possibilities for 2 job codes, but I have 7 jobs codes that I schedule daily. Based on a sampling of the shifts that I might use, what do you think would be best?

JC=Job Code

JC1 open: 1115-6
JC1 long open:11:15-8
JC1 close: 4-1215
JC1 close2: 6-1215

JC2 open: 1115-6
JC2 open2: 1130-6
JC2 long open: 11:15-8
JC2 mid: 12-6
JC2 mid2: 12-8
JC2 mid3: 2-6
JC2 mid4: 2-8
JC2 mid5: 2-10
JC2 mid6: 2-11
JC2 close: 4-10
JC2 close2: 4-11
JC2 close3: 6-10
JC2 close4: 6-11

 

[josephll]

There are many options to do what you want. It will take a lot of time for some one to excel to make it work. I know, I went through the same thing.
This link might be more of what you want...
<TABLE style="WIDTH: 450pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=600 border=0 x:str><COLGROUP><COL style="WIDTH: 450pt; mso-width-source: userset; mso-width-alt: 21942" width=600><TBODY><TR style="HEIGHT: 18.75pt" height=25><TD class=xl27 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 450pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 18.75pt; BACKGROUND-COLOR: silver" width=600 height=25 x:str="'http://www.shiftschedules.com">http://www.shiftschedules.com</TD></TR></TBODY></TABLE>
Take a look at what he has to offer. He will customize anything you want. The draw back is he likes to be paid for his work.
Your other choice is to keep working with it on your own. Makingit all work right is very satisfying. The folks on this forum are adept at making excel dance to any tune you want. Unfortunately I am not one of them. But, I hope this helps a little bit.

 

[asad]

Yes, one job code will have many shifts in one day. There is no set number of shifts for each job code, although there is a minimum number, which changes based on the day. The shifts vary depending on business, so some days I need only 2 shifts of job code 2 but on others I need 4 shifts of the same job code. For the shift times, I have some constant shifts like job code 1 opening shift is 11:15 to 6, and job code 1 closing is 6 to 1215. but I may add more shifts depending on needs.

I was considering adding each possible shift in the roster, but it would get pretty long and I don't know if it would be beneficial. For example here are some shifts I may use in one day (let alone a week). I have listed the possibilities for 2 job codes, but I have 7 jobs codes that I schedule daily. Based on a sampling of the shifts that I might use, what do you think would be best?

JC=Job Code

JC1 open: 1115-6
JC1 long open:11:15-8
JC1 close: 4-1215
JC1 close2: 6-1215

JC2 open: 1115-6
JC2 open2: 1130-6
JC2 long open: 11:15-8
JC2 mid: 12-6
JC2 mid2: 12-8
JC2 mid3: 2-6
JC2 mid4: 2-8
JC2 mid5: 2-10
JC2 mid6: 2-11
JC2 close: 4-10
JC2 close2: 4-11
JC2 close3: 6-10
JC2 close4: 6-11

Can we make them as JC1A, JC1B, JC1C and so on? We can allocate the different times for each of these codes and then employees will be given a code on the roster that will bring up the Start and Finish times for the employee.
e.g.
JC1 open: 1115-6
JC1 long open:11:15-8
JC1 close: 4-1215
JC1 close2: 6-1215

Can be written as

JC1A: 1115-6
JC1B:11:15-8
JC1C: 4-1215
JC1D: 6-1215

 

[Mayrie]

There are many options to do what you want. It will take a lot of time for some one to excel to make it work. I know, I went through the same thing.
This link might be more of what you want...
<TABLE style="WIDTH: 450pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=600 border=0 x:str><COLGROUP><COL style="WIDTH: 450pt; mso-width-source: userset; mso-width-alt: 21942" width=600><TBODY><TR style="HEIGHT: 18.75pt" height=25><TD class=xl27 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 450pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 18.75pt; BACKGROUND-COLOR: silver" width=600 height=25 x:str="'http://www.shiftschedules.com">http://www.shiftschedules.com</TD></TR></TBODY></TABLE>
Take a look at what he has to offer. He will customize anything you want. The draw back is he likes to be paid for his work.
Your other choice is to keep working with it on your own. Makingit all work right is very satisfying. The folks on this forum are adept at making excel dance to any tune you want. Unfortunately I am not one of them. But, I hope this helps a little bit.

Thanks, I did check out the site when I first started considering a new sheet. I saw that it does cost, and I am hoping to get it done on my own. But I will save the address. Thanks for the info!

 

[Mayrie]

Can we make them as JC1A, JC1B, JC1C and so on? We can allocate the different times for each of these codes and then employees will be given a code on the roster that will bring up the Start and Finish times for the employee.
e.g.
JC1 open: 1115-6
JC1 long open:11:15-8
JC1 close: 4-1215
JC1 close2: 6-1215

Can be written as

JC1A: 1115-6
JC1B:11:15-8
JC1C: 4-1215
JC1D: 6-1215

The job code shifts can be named anything, I just made up the names for the example. So I would still need to create a list of all the possible shifts and use a reference formula? I started working on a set up that is a little different:

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 79px"><COL style="WIDTH: 77px"><COL style="WIDTH: 82px"><COL style="WIDTH: 77px"><COL style="WIDTH: 77px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="FONT-WEIGHT: bold">Name</TD><TD style="FONT-WEIGHT: bold">Fri Area</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Fri Start</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Fri End</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Fri Hrs</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="FONT-WEIGHT: bold">Aaron</TD><TD>JC1</TD><TD style="TEXT-ALIGN: center">11:15 AM</TD><TD style="TEXT-ALIGN: center">6:00 PM</TD><TD style="TEXT-ALIGN: center">6:45</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="FONT-WEIGHT: bold">Anna</TD><TD>JC2</TD><TD style="TEXT-ALIGN: center">4:00 PM</TD><TD style="TEXT-ALIGN: center">12:15 AM</TD><TD style="TEXT-ALIGN: center">8:15</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD style="FONT-WEIGHT: bold">Ben</TD><TD>Request Off</TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: center">0:00</TD></TR></TBODY></TABLE>


<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>E6</TD><TD>=IF(OR(B6="JC1", B6="JC2", B6="JC3"), (D6-C6), "0:00")</TD></TR><TR><TD>E7</TD><TD>=IF(OR(B7="JC1", B7="JC2", B7="JC3"), (D7-C7), "0:00")</TD></TR><TR><TD>E8</TD><TD>=IF(OR(B8="JC1", B8="JC2", B8="JC3"), (D8-C8), "0:00")</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>


Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

Column A is constant and lists the employees
Column B uses a drop down menu with all 7 job codes listed
Column C & D are manual input cells, so I can put whatever start and end times I need
Column E is a formula that returns the total hours for the day
Columns B-D are repeated for each day of the week.

This set up seems to be working almost perfect. I can sort each day (seperately only) based upon the area so that I know I'm scheduling correctly. I can sort by name and hide the unnecessary columns (job code, daily hours) using a macro to put the schedule in display view for distribution.

The only problem is that I need a printable schedule that lists the employees and shifts by day AND and area. Like this:

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 166px"><COL style="WIDTH: 96px"><COL style="WIDTH: 96px"><COL style="WIDTH: 96px"><COL style="WIDTH: 96px"><COL style="WIDTH: 96px"><COL style="WIDTH: 96px"><COL style="WIDTH: 96px"><COL style="WIDTH: 96px"><COL style="WIDTH: 96px"><COL style="WIDTH: 96px"><COL style="WIDTH: 96px"><COL style="WIDTH: 96px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD><TD>J</TD><TD>K</TD><TD>L</TD><TD>M</TD><TD>N</TD></TR><TR style="HEIGHT: 24px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="FONT-SIZE: 16pt; FONT-WEIGHT: bold"></TD><TD style="FONT-SIZE: 16pt; FONT-WEIGHT: bold">Friday</TD><TD style="FONT-SIZE: 16pt; FONT-WEIGHT: bold"></TD><TD style="FONT-SIZE: 16pt; FONT-WEIGHT: bold"></TD><TD style="FONT-SIZE: 16pt; FONT-WEIGHT: bold"></TD><TD style="FONT-SIZE: 16pt; FONT-WEIGHT: bold">Saturday</TD><TD style="FONT-SIZE: 16pt; FONT-WEIGHT: bold"></TD><TD style="FONT-SIZE: 16pt; FONT-WEIGHT: bold"></TD><TD style="FONT-SIZE: 16pt; FONT-WEIGHT: bold"></TD><TD style="FONT-SIZE: 16pt; FONT-WEIGHT: bold">Sunday</TD><TD style="FONT-SIZE: 16pt; FONT-WEIGHT: bold"></TD><TD style="FONT-SIZE: 16pt; FONT-WEIGHT: bold"></TD><TD style="FONT-SIZE: 16pt; FONT-WEIGHT: bold"></TD></TR><TR style="HEIGHT: 24px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="BACKGROUND-COLOR: #00ff00; FONT-SIZE: 16pt; FONT-WEIGHT: bold">JC1</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #00ff00; FONT-SIZE: 16pt">11:15 AM</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #00ff00; FONT-SIZE: 16pt">6:00 PM</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #00ff00; FONT-SIZE: 16pt">7:30:00</TD><TD style="BACKGROUND-COLOR: #00ff00; FONT-SIZE: 16pt">Aaron</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #00ff00; FONT-SIZE: 16pt">10:30 AM</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #00ff00; FONT-SIZE: 16pt">6:00 PM</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #00ff00; FONT-SIZE: 16pt">7:30:00</TD><TD style="BACKGROUND-COLOR: #00ff00; FONT-SIZE: 16pt">Jocelyn</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #00ff00; FONT-SIZE: 16pt">10:30 AM</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #00ff00; FONT-SIZE: 16pt">10:15 PM</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #00ff00; FONT-SIZE: 16pt">11:45:00</TD><TD style="BACKGROUND-COLOR: #00ff00; FONT-SIZE: 16pt">Joan</TD></TR><TR style="HEIGHT: 24px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="BACKGROUND-COLOR: #00ff00; FONT-SIZE: 16pt; FONT-WEIGHT: bold"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #00ff00; FONT-SIZE: 16pt">4:00 PM</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #00ff00; FONT-SIZE: 16pt">12:15 AM</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #00ff00; FONT-SIZE: 16pt">8:15:00</TD><TD style="BACKGROUND-COLOR: #00ff00; FONT-SIZE: 16pt">Susan</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #00ff00; FONT-SIZE: 16pt">4:00 PM</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #00ff00; FONT-SIZE: 16pt">12:15 AM</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #00ff00; FONT-SIZE: 16pt">8:15:00</TD><TD style="BACKGROUND-COLOR: #00ff00; FONT-SIZE: 16pt">Manny</TD><TD style="BACKGROUND-COLOR: #00ff00; FONT-SIZE: 16pt"></TD><TD style="BACKGROUND-COLOR: #00ff00; FONT-SIZE: 16pt"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #00ff00; FONT-SIZE: 16pt">0:00:00</TD><TD style="BACKGROUND-COLOR: #00ff00; FONT-SIZE: 16pt"></TD></TR><TR style="HEIGHT: 24px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="BACKGROUND-COLOR: #ff99cc; FONT-SIZE: 16pt; FONT-WEIGHT: bold">JC2</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ff99cc; FONT-SIZE: 16pt">4:00 pm</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ff99cc; FONT-SIZE: 16pt">12:00 AM</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ff99cc; FONT-SIZE: 16pt">6:45:00</TD><TD style="BACKGROUND-COLOR: #ff99cc; FONT-SIZE: 16pt">Anna</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ff99cc; FONT-SIZE: 16pt">11:15 AM</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ff99cc; FONT-SIZE: 16pt">6:00 PM</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ff99cc; FONT-SIZE: 16pt">6:45:00</TD><TD style="BACKGROUND-COLOR: #ff99cc; FONT-SIZE: 16pt">Susan</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ff99cc; FONT-SIZE: 16pt">11:15 AM</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ff99cc; FONT-SIZE: 16pt">4:00 PM</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ff99cc; FONT-SIZE: 16pt">4:45:00</TD><TD style="BACKGROUND-COLOR: #ff99cc; FONT-SIZE: 16pt">Sarah</TD></TR><TR style="HEIGHT: 24px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="BACKGROUND-COLOR: #ff99cc; FONT-SIZE: 16pt; FONT-WEIGHT: bold"></TD><TD style="BACKGROUND-COLOR: #ff99cc; FONT-SIZE: 16pt"></TD><TD style="BACKGROUND-COLOR: #ff99cc; FONT-SIZE: 16pt"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ff99cc; FONT-SIZE: 16pt">0:00:00</TD><TD style="BACKGROUND-COLOR: #ff99cc; FONT-SIZE: 16pt"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ff99cc; FONT-SIZE: 16pt">11:30 AM</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ff99cc; FONT-SIZE: 16pt">6:00 PM</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ff99cc; FONT-SIZE: 16pt">6:30:00</TD><TD style="BACKGROUND-COLOR: #ff99cc; FONT-SIZE: 16pt">Lisa</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ff99cc; FONT-SIZE: 16pt">2:00 PM</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ff99cc; FONT-SIZE: 16pt">8:00 PM</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ff99cc; FONT-SIZE: 16pt">6:00:00</TD><TD style="BACKGROUND-COLOR: #ff99cc; FONT-SIZE: 16pt">Ben</TD></TR><TR style="HEIGHT: 24px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="BACKGROUND-COLOR: #00ccff; FONT-SIZE: 16pt; FONT-WEIGHT: bold">JC3</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #00ccff; FONT-SIZE: 16pt">1:45 PM</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #00ccff; FONT-SIZE: 16pt">6:00 PM</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #00ccff; FONT-SIZE: 16pt">4:15:00</TD><TD style="BACKGROUND-COLOR: #00ccff; FONT-SIZE: 16pt">Tom</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #00ccff; FONT-SIZE: 16pt">1:45 PM</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #00ccff; FONT-SIZE: 16pt">10:00 PM</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #00ccff; FONT-SIZE: 16pt">8:15:00</TD><TD style="BACKGROUND-COLOR: #00ccff; FONT-SIZE: 16pt">Anna</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #00ccff; FONT-SIZE: 16pt">1:45 PM</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #00ccff; FONT-SIZE: 16pt">10:00 PM</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #00ccff; FONT-SIZE: 16pt">8:15:00</TD><TD style="BACKGROUND-COLOR: #00ccff; FONT-SIZE: 16pt">James</TD></TR><TR style="HEIGHT: 24px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="BACKGROUND-COLOR: #00ccff; FONT-SIZE: 16pt; FONT-WEIGHT: bold"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #00ccff; FONT-SIZE: 16pt">6:00 PM</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #00ccff; FONT-SIZE: 16pt">10:00 PM</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #00ccff; FONT-SIZE: 16pt">4:00:00</TD><TD style="BACKGROUND-COLOR: #00ccff; FONT-SIZE: 16pt">Tina</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #00ccff; FONT-SIZE: 16pt">1:45 PM</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #00ccff; FONT-SIZE: 16pt">10:00 PM</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #00ccff; FONT-SIZE: 16pt">8:15:00</TD><TD style="BACKGROUND-COLOR: #00ccff; FONT-SIZE: 16pt">Ken</TD><TD style="BACKGROUND-COLOR: #00ccff; FONT-SIZE: 16pt"></TD><TD style="BACKGROUND-COLOR: #00ccff; FONT-SIZE: 16pt"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #00ccff; FONT-SIZE: 16pt">0:00:00</TD><TD style="BACKGROUND-COLOR: #00ccff; FONT-SIZE: 16pt"></TD></TR><TR style="HEIGHT: 24px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD style="BACKGROUND-COLOR: #ffff00; FONT-SIZE: 16pt; FONT-WEIGHT: bold">JC4</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00; FONT-SIZE: 16pt">6:00 PM</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00; FONT-SIZE: 16pt">12:15 AM</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00; FONT-SIZE: 16pt">6:15:00</TD><TD style="BACKGROUND-COLOR: #ffff00; FONT-SIZE: 16pt">John</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00; FONT-SIZE: 16pt">6:00 PM</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00; FONT-SIZE: 16pt">12:15 AM</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00; FONT-SIZE: 16pt">6:15:00</TD><TD style="BACKGROUND-COLOR: #ffff00; FONT-SIZE: 16pt">Joe</TD><TD style="BACKGROUND-COLOR: #ffff00; FONT-SIZE: 16pt"></TD><TD style="BACKGROUND-COLOR: #ffff00; FONT-SIZE: 16pt"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00; FONT-SIZE: 16pt">0:00:00</TD><TD style="BACKGROUND-COLOR: #ffff00; FONT-SIZE: 16pt"></TD></TR><TR style="HEIGHT: 24px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD style="FONT-SIZE: 16pt; FONT-WEIGHT: bold">Total Hours</TD><TD style="FONT-SIZE: 16pt; FONT-WEIGHT: bold"></TD><TD style="FONT-SIZE: 16pt; FONT-WEIGHT: bold"></TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 16pt; FONT-WEIGHT: bold">37:00:00</TD><TD style="FONT-SIZE: 16pt; FONT-WEIGHT: bold"></TD><TD style="FONT-SIZE: 16pt; FONT-WEIGHT: bold"></TD><TD style="FONT-SIZE: 16pt; FONT-WEIGHT: bold"></TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 16pt; FONT-WEIGHT: bold">51:45:00</TD><TD style="FONT-SIZE: 16pt; FONT-WEIGHT: bold"></TD><TD style="FONT-SIZE: 16pt; FONT-WEIGHT: bold"></TD><TD style="FONT-SIZE: 16pt; FONT-WEIGHT: bold"></TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 16pt; FONT-WEIGHT: bold">30:45:00</TD><TD style="FONT-SIZE: 16pt; FONT-WEIGHT: bold"></TD></TR></TBODY></TABLE>



Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

I am trying to create a pivot table, but since I have a new list of jobs for each day, the pivot table is too complicated. Do you think the set up I have might work? Is there another way to transfer the data in the first table to a new table set up like above? I tried to data consolidation, but that didn't work. I want to keep the first table, the second table is just an example of something I envision, and doesn't need to be color coded. Or do you think using the VLOOKUP function would be better? Thanks so much for sticking with me on this...I want to get it right!

-Athena

 

[josephll]

Have you thought about using a button with associated code to select, copy, and paste the inforamtion you want into the order you want. Once this has been to select the print area and print the sheet.
This would do a couple of things for you. All you would need to do is click a button and all would be done. You would not need to change the sheet you are working from now.
I put together a smple of what I saying earlier but it did not post. I'll put something together later and post it.

 

[Mayrie]

Like a macro button? Right now I have the the schedule set up as a table so if I sort Friday by area, then it will automatically sort the other days,to keep the row values. Since each employee doesn't always work the same area, that won't work. I suppose I could convert the table into a range and custom sort by area. The only problem is that the employee name list is the first column and row labels. So, if I customer sort for each day, the shifts will not match up with the employee correctly. Do you know of a way around that problem? Other than that issue it makes perfect since. I do something similar for my inventory sheets, I don't know why I didn't think of it.

 

[Mayrie]

I just reread your post, and I misinterpretted it the first time I replied. This may work, I will get back to you. Thanks!

 

[Mayrie]

I used a macro to copy and paste the schedule on a new worksheet in the desired layout. The only problem is that each week the number of rows will vary depending on how many employees I schedule, so the macro isn't really going to save me time. I set it up to where I move all employees over to the new layout and then delete the ones that are not working, because they don't need to be on this new sheet. But then I have to reformat after that, so it's not really saving time.
Do you think another method might work?
If I could find a way on my first worksheet to only have one column for job codes (instead of one column for job codes for each day, 7 job code columns) I think I will be able to use a pivot table. But since each employee is assigned more than one job code, I can't figure out how to do this. Any suggestions?