Is this possible?

[kmart37]

I use the following sheet to make employee schedules with time ranges. My boss has tasked me to track Employee hours versus customer arrival times. Is there a way I can link my schedule tool to my labor tracker and have it pre populate? Here is the sheet I use for schedule:

HPD_OT_1.xls
	B	C	D	E
9		IN	OUT	HOURS
10	Marorie	8:00AM	6:00PM	10.00
MASTER

Here is the sheet I use to track the employees schedule over the day. What I would like is that it populates based upon the schedule I create on the above sheet so that is Marjorie is scheduled from 8am-6pm that it adds a 1 in each hour she works or a .25/.50/.75 if it is not a full hour.

Copy of HPD_OT_1.xls
	B	C	D	E	F	G	H	I	J	K	L	M
22	Name	5:00am	6:00am	7:00am	8:00am	9:00am	10:00am	11:00am	12:00pm	1:00pm	2:00pm	3:00pm
23
24	Marjorie				1	1	1	1	1	1	1	1
Monday

Is this possible and if so how do I do it?

Keith

 

[theozz]

Hi ! Here Suggest to you

<table style="border-collapse: separate;" colspan="2" border="7" cellspacing="0"><tbody bgcolor="#ffffff"><tr height="36"><td colspan="2"> Nice Question Thanks.
DataSheet= Sheet1</td></tr><tr><td align="right" width="30"><table rowspan="34" cellspacing="1" width="30"><tbody bgcolor="#ffffff"><tr height="18"><td>
</td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">1 </td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">2 </td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">3 </td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">4 </td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">5 </td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">6 </td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">7 </td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">8 </td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">9 </td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">10 </td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">11 </td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">12 </td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">13 </td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">14 </td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">15 </td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">16 </td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">17 </td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">18 </td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">19 </td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">20 </td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">21 </td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">22 </td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">23 </td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">24 </td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">25 </td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">26 </td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">27 </td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">28 </td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">29 </td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">30 </td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">31 </td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">32 </td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">33 </td></tr></tbody></table></td><td width="450"><table colspan="6" rowspan="16" bgcolor="#939393" border="0" cellspacing="1" width="450"><tbody bgcolor="#ffffff"><tr height="18"><td style="font-family: verdana;" align="center" bgcolor="#dcdcdc" width="91">A</td><td style="font-family: verdana;" align="center" bgcolor="#dcdcdc" width="61">B</td><td style="font-family: verdana;" align="center" bgcolor="#dcdcdc" width="61">C</td><td style="font-family: verdana;" align="center" bgcolor="#dcdcdc" width="74">D</td><td style="font-family: verdana;" align="center" bgcolor="#dcdcdc" width="81">E</td><td style="font-family: verdana;" align="center" bgcolor="#dcdcdc" width="80">F</td></tr><tr height="18"><td colspan="1" align="left" bgcolor="#cc99ff" width="91">NAME</td><td colspan="1" align="left" bgcolor="#cc99ff" width="61">IN</td><td colspan="1" align="left" bgcolor="#cc99ff" width="61">OUT</td><td colspan="1" align="left" bgcolor="#cc99ff" width="74">HOURS</td><td colspan="1" align="left" bgcolor="#ffffff" width="81"> </td><td colspan="1" align="left" bgcolor="#ffffff" width="80"> </td></tr><tr height="18"><td colspan="1" align="left" bgcolor="#ffff99" width="91">TOM</td><td colspan="1" align="right" bgcolor="#ffff99" width="61">8:00</td><td colspan="1" align="right" bgcolor="#ffff99" width="61">18:00</td><td colspan="1" align="right" bgcolor="#ffff99" width="74">10:00</td><td colspan="1" align="left" bgcolor="#ffffff" width="81"> </td><td colspan="1" align="left" bgcolor="#ffffff" width="80"> </td></tr><tr height="18"><td colspan="1" align="left" bgcolor="#ffff00" width="91">JOHN</td><td colspan="1" align="right" bgcolor="#ffff00" width="61">8:20</td><td colspan="1" align="right" bgcolor="#ffff00" width="61">19:00</td><td colspan="1" align="right" bgcolor="#ffff00" width="74">10:40</td><td colspan="1" align="left" bgcolor="#ffffff" width="81"> </td><td colspan="1" align="left" bgcolor="#ffffff" width="80"> </td></tr><tr height="18"><td colspan="1" align="left" bgcolor="#ffcc99" width="91">SAM</td><td colspan="1" align="right" bgcolor="#ffcc99" width="61">8:50</td><td colspan="1" align="right" bgcolor="#ffcc99" width="61">20:00</td><td colspan="1" align="right" bgcolor="#ffcc99" width="74">11:10</td><td colspan="1" align="left" bgcolor="#ffffff" width="81"> </td><td colspan="1" align="left" bgcolor="#ffffff" width="80"> </td></tr><tr height="18"><td colspan="1" align="left" bgcolor="#ffcc00" width="91">KOREAN</td><td colspan="1" align="right" bgcolor="#ffcc00" width="61">8:00</td><td colspan="1" align="right" bgcolor="#ffcc00" width="61">20:20</td><td colspan="1" align="right" bgcolor="#ffcc00" width="74">12:20</td><td colspan="1" align="left" bgcolor="#ffffff" width="81"> </td><td colspan="1" align="left" bgcolor="#ffffff" width="80"> </td></tr><tr height="18"><td colspan="1" align="left" bgcolor="#ff9900" width="91">WOMAN</td><td colspan="1" align="right" bgcolor="#ff9900" width="61">8:00</td><td colspan="1" align="right" bgcolor="#ff9900" width="61">20:50</td><td colspan="1" align="right" bgcolor="#ff9900" width="74">12:50</td><td colspan="1" align="left" bgcolor="#ffffff" width="81"> </td><td colspan="1" align="left" bgcolor="#ffffff" width="80"> </td></tr><tr height="18"><td colspan="1" align="left" bgcolor="#ffffff" width="91"> </td><td colspan="1" align="left" bgcolor="#ffffff" width="61"> </td><td colspan="1" align="left" bgcolor="#ffffff" width="61"> </td><td colspan="1" align="left" bgcolor="#ffffff" width="74"> </td><td colspan="1" align="left" bgcolor="#ffffff" width="81"> </td><td colspan="1" align="left" bgcolor="#ffffff" width="80"> </td></tr><tr height="18"><td colspan="1" align="left" bgcolor="#cc99ff" width="91">NAME</td><td colspan="1" align="left" bgcolor="#ffff99" width="61">TOM</td><td colspan="1" align="left" bgcolor="#ffff00" width="61">JOHN</td><td colspan="1" align="left" bgcolor="#ffcc99" width="74">SAM</td><td colspan="1" align="left" bgcolor="#ffcc00" width="81">KOREAN</td><td colspan="1" align="left" bgcolor="#ff9900" width="80">WOMAN</td></tr><tr height="18"><td colspan="1" align="left" bgcolor="#cc99ff" width="91">IN</td><td colspan="1" align="right" bgcolor="#ffff99" width="61">8:00</td><td colspan="1" align="right" bgcolor="#ffff00" width="61">8:20</td><td colspan="1" align="right" bgcolor="#ffcc99" width="74">8:50</td><td colspan="1" align="right" bgcolor="#ffcc00" width="81">8:00</td><td colspan="1" align="right" bgcolor="#ff9900" width="80">8:00</td></tr><tr height="18"><td colspan="1" align="left" bgcolor="#cc99ff" width="91">OUT</td><td colspan="1" align="right" bgcolor="#ffff99" width="61">18:00</td><td colspan="1" align="right" bgcolor="#ffff00" width="61">19:00</td><td colspan="1" align="right" bgcolor="#ffcc99" width="74">20:00</td><td colspan="1" align="right" bgcolor="#ffcc00" width="81">20:20</td><td colspan="1" align="right" bgcolor="#ff9900" width="80">20:50</td></tr><tr height="18"><td colspan="1" align="left" bgcolor="#cc99ff" width="91"> </td><td colspan="1" align="left" bgcolor="#ffff99" width="61"> </td><td colspan="1" align="left" bgcolor="#ffff00" width="61"> </td><td colspan="1" align="left" bgcolor="#ffcc99" width="74"> </td><td colspan="1" align="left" bgcolor="#ffcc00" width="81"> </td><td colspan="1" align="left" bgcolor="#ff9900" width="80"> </td></tr><tr height="18"><td colspan="1" align="right" bgcolor="#cc99ff" width="91">5:00</td><td colspan="1" align="left" bgcolor="#ffff99" width="61">--</td><td colspan="1" align="left" bgcolor="#ffff00" width="61">--</td><td colspan="1" align="left" bgcolor="#ffcc99" width="74">--</td><td colspan="1" align="left" bgcolor="#ffcc00" width="81">--</td><td colspan="1" align="left" bgcolor="#ff9900" width="80">--</td></tr><tr height="18"><td colspan="1" align="right" bgcolor="#cc99ff" width="91">6:00</td><td colspan="1" align="left" bgcolor="#ffff99" width="61">--</td><td colspan="1" align="left" bgcolor="#ffff00" width="61">--</td><td colspan="1" align="left" bgcolor="#ffcc99" width="74">--</td><td colspan="1" align="left" bgcolor="#ffcc00" width="81">--</td><td colspan="1" align="left" bgcolor="#ff9900" width="80">--</td></tr><tr height="18"><td colspan="1" align="right" bgcolor="#cc99ff" width="91">7:00</td><td colspan="1" align="left" bgcolor="#ffff99" width="61">--</td><td colspan="1" align="left" bgcolor="#ffff00" width="61">--</td><td colspan="1" align="left" bgcolor="#ffcc99" width="74">--</td><td colspan="1" align="left" bgcolor="#ffcc00" width="81">--</td><td colspan="1" align="left" bgcolor="#ff9900" width="80">--</td></tr><tr height="18"><td colspan="1" align="right" bgcolor="#cc99ff" width="91">8:00</td><td colspan="1" align="right" bgcolor="#ffff99" width="61">1:00</td><td colspan="1" align="right" bgcolor="#ffff00" width="61">0:40</td><td colspan="1" align="right" bgcolor="#ffcc99" width="74">0:10</td><td colspan="1" align="right" bgcolor="#ffcc00" width="81">1:00</td><td colspan="1" align="right" bgcolor="#ff9900" width="80">1:00</td></tr><tr height="18"><td colspan="1" align="right" bgcolor="#cc99ff" width="91">9:00</td><td colspan="1" align="right" bgcolor="#ffff99" width="61">1:00</td><td colspan="1" align="right" bgcolor="#ffff00" width="61">1:00</td><td colspan="1" align="right" bgcolor="#ffcc99" width="74">1:00</td><td colspan="1" align="right" bgcolor="#ffcc00" width="81">1:00</td><td colspan="1" align="right" bgcolor="#ff9900" width="80">1:00</td></tr></tbody></table><table colspan="6" rowspan="17" bgcolor="#939393" border="0" cellspacing="1" width="450"><tbody bgcolor="#ffffff"><tr height="18"><td colspan="1" align="right" bgcolor="#cc99ff" width="91">10:00</td><td colspan="1" align="right" bgcolor="#ffff99" width="61">1:00</td><td colspan="1" align="right" bgcolor="#ffff00" width="61">1:00</td><td colspan="1" align="right" bgcolor="#ffcc99" width="74">1:00</td><td colspan="1" align="right" bgcolor="#ffcc00" width="81">1:00</td><td colspan="1" align="right" bgcolor="#ff9900" width="80">1:00</td></tr><tr height="18"><td colspan="1" align="right" bgcolor="#cc99ff" width="91">11:00</td><td colspan="1" align="right" bgcolor="#ffff99" width="61">1:00</td><td colspan="1" align="right" bgcolor="#ffff00" width="61">1:00</td><td colspan="1" align="right" bgcolor="#ffcc99" width="74">1:00</td><td colspan="1" align="right" bgcolor="#ffcc00" width="81">1:00</td><td colspan="1" align="right" bgcolor="#ff9900" width="80">1:00</td></tr><tr height="18"><td colspan="1" align="right" bgcolor="#cc99ff" width="91">12:00</td><td colspan="1" align="right" bgcolor="#ffff99" width="61">1:00</td><td colspan="1" align="right" bgcolor="#ffff00" width="61">1:00</td><td colspan="1" align="right" bgcolor="#ffcc99" width="74">1:00</td><td colspan="1" align="right" bgcolor="#ffcc00" width="81">1:00</td><td colspan="1" align="right" bgcolor="#ff9900" width="80">1:00</td></tr><tr height="18"><td colspan="1" align="right" bgcolor="#cc99ff" width="91">13:00</td><td colspan="1" align="right" bgcolor="#ffff99" width="61">1:00</td><td colspan="1" align="right" bgcolor="#ffff00" width="61">1:00</td><td colspan="1" align="right" bgcolor="#ffcc99" width="74">1:00</td><td colspan="1" align="right" bgcolor="#ffcc00" width="81">1:00</td><td colspan="1" align="right" bgcolor="#ff9900" width="80">1:00</td></tr><tr height="18"><td colspan="1" align="right" bgcolor="#cc99ff" width="91">14:00</td><td colspan="1" align="right" bgcolor="#ffff99" width="61">1:00</td><td colspan="1" align="right" bgcolor="#ffff00" width="61">1:00</td><td colspan="1" align="right" bgcolor="#ffcc99" width="74">1:00</td><td colspan="1" align="right" bgcolor="#ffcc00" width="81">1:00</td><td colspan="1" align="right" bgcolor="#ff9900" width="80">1:00</td></tr><tr height="18"><td colspan="1" align="right" bgcolor="#cc99ff" width="91">15:00</td><td colspan="1" align="right" bgcolor="#ffff99" width="61">1:00</td><td colspan="1" align="right" bgcolor="#ffff00" width="61">1:00</td><td colspan="1" align="right" bgcolor="#ffcc99" width="74">1:00</td><td colspan="1" align="right" bgcolor="#ffcc00" width="81">1:00</td><td colspan="1" align="right" bgcolor="#ff9900" width="80">1:00</td></tr><tr height="18"><td colspan="1" align="right" bgcolor="#cc99ff" width="91">16:00</td><td colspan="1" align="right" bgcolor="#ffff99" width="61">1:00</td><td colspan="1" align="right" bgcolor="#ffff00" width="61">1:00</td><td colspan="1" align="right" bgcolor="#ffcc99" width="74">1:00</td><td colspan="1" align="right" bgcolor="#ffcc00" width="81">1:00</td><td colspan="1" align="right" bgcolor="#ff9900" width="80">1:00</td></tr><tr height="18"><td colspan="1" align="right" bgcolor="#cc99ff" width="91">17:00</td><td colspan="1" align="right" bgcolor="#ffff99" width="61">1:00</td><td colspan="1" align="right" bgcolor="#ffff00" width="61">1:00</td><td colspan="1" align="right" bgcolor="#ffcc99" width="74">1:00</td><td colspan="1" align="right" bgcolor="#ffcc00" width="81">1:00</td><td colspan="1" align="right" bgcolor="#ff9900" width="80">1:00</td></tr><tr height="18"><td colspan="1" align="right" bgcolor="#cc99ff" width="91">18:00</td><td colspan="1" align="right" bgcolor="#ffff99" width="61">0:00</td><td colspan="1" align="right" bgcolor="#ffff00" width="61">1:00</td><td colspan="1" align="right" bgcolor="#ffcc99" width="74">1:00</td><td colspan="1" align="right" bgcolor="#ffcc00" width="81">1:00</td><td colspan="1" align="right" bgcolor="#ff9900" width="80">1:00</td></tr><tr height="18"><td colspan="1" align="right" bgcolor="#cc99ff" width="91">19:00</td><td colspan="1" align="left" bgcolor="#ffff99" width="61">--</td><td colspan="1" align="right" bgcolor="#ffff00" width="61">0:00</td><td colspan="1" align="right" bgcolor="#ffcc99" width="74">1:00</td><td colspan="1" align="right" bgcolor="#ffcc00" width="81">1:00</td><td colspan="1" align="right" bgcolor="#ff9900" width="80">1:00</td></tr><tr height="18"><td colspan="1" align="right" bgcolor="#cc99ff" width="91">20:00</td><td colspan="1" align="left" bgcolor="#ffff99" width="61">--</td><td colspan="1" align="left" bgcolor="#ffff00" width="61">--</td><td colspan="1" align="right" bgcolor="#ffcc99" width="74">0:00</td><td colspan="1" align="right" bgcolor="#ffcc00" width="81">0:20</td><td colspan="1" align="right" bgcolor="#ff9900" width="80">0:50</td></tr><tr height="18"><td colspan="1" align="right" bgcolor="#cc99ff" width="91">21:00</td><td colspan="1" align="left" bgcolor="#ffff99" width="61">--</td><td colspan="1" align="left" bgcolor="#ffff00" width="61">--</td><td colspan="1" align="left" bgcolor="#ffcc99" width="74">--</td><td colspan="1" align="left" bgcolor="#ffcc00" width="81">--</td><td colspan="1" align="left" bgcolor="#ff9900" width="80">--</td></tr><tr height="18"><td colspan="1" align="right" bgcolor="#cc99ff" width="91">22:00</td><td colspan="1" align="left" bgcolor="#ffff99" width="61">--</td><td colspan="1" align="left" bgcolor="#ffff00" width="61">--</td><td colspan="1" align="left" bgcolor="#ffcc99" width="74">--</td><td colspan="1" align="left" bgcolor="#ffcc00" width="81">--</td><td colspan="1" align="left" bgcolor="#ff9900" width="80">--</td></tr><tr height="18"><td colspan="1" align="right" bgcolor="#cc99ff" width="91">23:00</td><td colspan="1" align="left" bgcolor="#ffff99" width="61">--</td><td colspan="1" align="left" bgcolor="#ffff00" width="61">--</td><td colspan="1" align="left" bgcolor="#ffcc99" width="74">--</td><td colspan="1" align="left" bgcolor="#ffcc00" width="81">--</td><td colspan="1" align="left" bgcolor="#ff9900" width="80">--</td></tr><tr height="18"><td colspan="1" align="right" bgcolor="#cc99ff" width="91">24:00</td><td colspan="1" align="left" bgcolor="#ffff99" width="61"> </td><td colspan="1" align="left" bgcolor="#ffff00" width="61"> </td><td colspan="1" align="left" bgcolor="#ffcc99" width="74"> </td><td colspan="1" align="left" bgcolor="#ffcc00" width="81"> </td><td colspan="1" align="left" bgcolor="#ff9900" width="80"> </td></tr><tr height="18"><td colspan="1" align="left" bgcolor="#cc99ff" width="91"> </td><td colspan="1" align="right" bgcolor="#ffff99" width="61">10:00</td><td colspan="1" align="right" bgcolor="#ffff00" width="61">10:40</td><td colspan="1" align="right" bgcolor="#ffcc99" width="74">11:10</td><td colspan="1" align="right" bgcolor="#ffcc00" width="81">12:20</td><td colspan="1" align="right" bgcolor="#ff9900" width="80">12:50</td></tr><tr height="18"><td colspan="6" align="center" bgcolor="#ccffcc" width="450">A12:F31 His Format= [h]:mm</td></tr></tbody></table></td></tr></tbody></table>
<colspan=5 width="800" rowspan="7" height="126"></colspan=5><table style="border-collapse: separate;" border="6" cellspacing="0"><tbody><tr height="24"><td colspan="5" align="center">Used Formula ...(With Running MicrosoftExcel Ver 2003)</td></tr><tr height="24"><td align="center" bgcolor="#d3d3d3" width="4%">No</td><td align="center" bgcolor="#d3d3d3" width="9%">Addr'</td><td align="center" bgcolor="#d3d3d3" width="65%"> If use below Formula, You'll Get Result as Right</td><td align="center" bgcolor="#d3d3d3" width="15%">Result</td><td align="center" bgcolor="#d3d3d3">Formula's</td></tr><tr height="20"><td align="center">1</td><td align="center" bgcolor="#ffff99">D2</td><td bgcolor="#ffff99">=C2-B2</td><td align="right">10:00</td><td align="right"><form name="PrNames1"><input onclick='window.clipboardData.setData("Text","=C2-B2");' value="Do Copy" name="MyNames1" type="button"></form></td></tr><tr height="20"><td align="center">2</td><td align="center" bgcolor=""> </td><td>D2 His Formula Used This Cell -> D2:D6</td><td align="left"> </td><td> </td></tr><tr height="20"><td align="center">3</td><td align="center" bgcolor="#ffff99">B12</td><td bgcolor="#ffff99">=IF(AND(B$9< =$A12,$A12< =B$10),ROUND(MIN(B$10,$A13)-MAX(B$9,$A12),10),IF(AND(0< $A13-B$9,$A13-B$9< TIME(1,0,0)),$A13-B$9,"--"))</td><td align="left">--</td><td align="right"><form name="PrNames3"><input onclick='window.clipboardData.setData("Text","=IF(AND(B$9<=$A12,$A12<=B$10),ROUND(MIN(B$10,$A13)-MAX(B$9,$A12),10),IF(AND(0<$A13-B$9,$A13-B$9<TIME(1,0,0)),$A13-B$9,\"--\"))");' value="Do Copy" name="MyNames3" type="button"></form></td></tr><tr height="20"><td align="center">4</td><td align="center" bgcolor=""> </td><td>B12 His Formula Used This Cell -> B12:F30</td><td align="left"> </td><td> </td></tr><tr height="20"><td align="center">5</td><td align="center" bgcolor="#ffff99">B32</td><td bgcolor="#ffff99">=SUM(B12:B30)</td><td align="right">10:00</td><td align="right"><form name="PrNames5"><input onclick='window.clipboardData.setData("Text","=SUM(B12:B30)");' value="Do Copy" name="MyNames5" type="button"></form></td></tr><tr height="20"><td align="center">6</td><td align="center" bgcolor=""> </td><td>B32 His Formula Used This Cell -> B32:F32</td><td align="left"> </td><td> </td></tr><tr height="24"><td colspan="5">
if Formula occurs Error! or Different Result...Check or Remove the Space in Formula.

How about this suggest?
</td></tr><tr height="24"><td colspan="5">
formula based only one day (same day)
</td></tr></tbody></table>