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