Dropping the date from cell

[bethieb]

I have a spreadsheet sent from another company. The cell has the date first and then the time. My formula figures the time only so I need to drop the date. I tried to change the cell but it won't change. Any suggestions. The spreadsheet used to have the time only and I would convert it to am/pm. My formula doesn't work with the new setup. Thanks.

 

[DonkeyOte]

no, not possible to change the cell itself... as we said you'll need to adjust your formulae I'm afraid... ... the only other alternative is to use VBA to adjust the date strings but I think adjusting your other formulae will be best.

If you need any pointers on how to do that just post up what you have and given we know your date layout we can adjust them for you...

 

[bethieb]

<TABLE style="WIDTH: 681pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=907 border=0 x:str><COLGROUP><COL style="WIDTH: 192pt; mso-width-source: userset; mso-width-alt: 9362" width=256><COL style="WIDTH: 18pt; mso-width-source: userset; mso-width-alt: 877" width=24><COL style="WIDTH: 36pt; mso-width-source: userset; mso-width-alt: 1755" width=48><COL style="WIDTH: 74pt; mso-width-source: userset; mso-width-alt: 3584" width=98><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2742" width=75><COL style="WIDTH: 17pt; mso-width-source: userset; mso-width-alt: 804" width=22><COL style="WIDTH: 37pt; mso-width-source: userset; mso-width-alt: 1792" width=49><COL style="WIDTH: 23pt; mso-width-source: userset; mso-width-alt: 1133" width=31><COL style="WIDTH: 28pt; mso-width-source: userset; mso-width-alt: 1353" width=37><COL style="WIDTH: 84pt; mso-width-source: userset; mso-width-alt: 4096" span=2 width=112><COL style="WIDTH: 32pt; mso-width-source: userset; mso-width-alt: 1572" width=43><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl23 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 1pt solid; WIDTH: 192pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right width=256 height=17 x:num="39631">7/2/2008</TD><TD class=xl22 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black; WIDTH: 18pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" width=24>A</TD><TD class=xl22 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black; WIDTH: 36pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=48 x:num>70732</TD><TD class=xl22 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black; WIDTH: 74pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" width=98>MYERS</TD><TD class=xl22 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black; WIDTH: 56pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" width=75>TROY</TD><TD class=xl22 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black; WIDTH: 17pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" width=22>D</TD><TD class=xl22 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black; WIDTH: 37pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=49 x:num>6240</TD><TD class=xl22 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black; WIDTH: 23pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" width=31> </TD><TD class=xl22 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black; WIDTH: 28pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" width=37> </TD><TD class=xl22 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black; WIDTH: 84pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" width=112>01JUL08:16:34:16</TD><TD class=xl22 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black; WIDTH: 84pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" width=112>02JUL08:05:33:33</TD><TD class=xl22 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black; WIDTH: 32pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=43 x:num>12.75</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl23 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black 1pt solid; WIDTH: 192pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right width=256 height=17 x:num="39631">7/2/2008</TD><TD class=xl22 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 18pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" width=24>A</TD><TD class=xl22 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 36pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=48 x:num>70734</TD><TD class=xl22 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 74pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" width=98>WALTON</TD><TD class=xl22 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 56pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" width=75>DONTE</TD><TD class=xl22 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 17pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" width=22> </TD><TD class=xl22 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 37pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=49 x:num>2107</TD><TD class=xl22 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 23pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" width=31> </TD><TD class=xl22 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 28pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" width=37> </TD><TD class=xl22 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 84pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" width=112>02JUL08:04:52:32</TD><TD class=xl22 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 84pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" width=112>02JUL08:17:33:55</TD><TD class=xl22 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 32pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=43 x:num>12.5</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl23 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black 1pt solid; WIDTH: 192pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right width=256 height=17 x:num="39631">7/2/2008</TD><TD class=xl22 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 18pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" width=24>A</TD><TD class=xl22 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 36pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=48 x:num>70743</TD><TD class=xl22 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 74pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" width=98>PARKER</TD><TD class=xl22 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 56pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" width=75>RICKY</TD><TD class=xl22 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 17pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" width=22>J</TD><TD class=xl22 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 37pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=49 x:num>3629</TD><TD class=xl22 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 23pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" width=31> </TD><TD class=xl22 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 28pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" width=37> </TD><TD class=xl22 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 84pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" width=112>01JUL08:16:37:53</TD><TD class=xl22 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 84pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" width=112>02JUL08:05:38:43</TD><TD class=xl22 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 32pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=43 x:num>13</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl23 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black 1pt solid; WIDTH: 192pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right width=256 height=17 x:num="39631">7/2/2008</TD><TD class=xl22 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 18pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" width=24>A</TD><TD class=xl22 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 36pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=48 x:num>70756</TD><TD class=xl22 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 74pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" width=98>OVERHOLSER</TD><TD class=xl22 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 56pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" width=75>CHARLES</TD><TD class=xl22 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 17pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" width=22>R</TD><TD class=xl22 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 37pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=49 x:num>6213</TD><TD class=xl22 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 23pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" width=31> </TD><TD class=xl22 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 28pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" width=37> </TD><TD class=xl22 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 84pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" width=112>01JUL08:16:15:58</TD><TD class=xl22 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 84pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" width=112>02JUL08:05:33:56</TD><TD class=xl22 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 32pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=43 x:num>13.25</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl23 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black 1pt solid; WIDTH: 192pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right width=256 height=17 x:num="39631">7/2/2008</TD><TD class=xl22 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 18pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" width=24>A</TD><TD class=xl22 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 36pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=48 x:num>70766</TD><TD class=xl22 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 74pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" width=98>STEVENS</TD><TD class=xl22 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 56pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" width=75>DANIEL</TD><TD class=xl22 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 17pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" width=22> </TD><TD class=xl22 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 37pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=49 x:num>6751</TD><TD class=xl22 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 23pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" width=31> </TD><TD class=xl22 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 28pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" width=37> </TD><TD class=xl22 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 84pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" width=112>02JUL08:04:43:59</TD><TD class=xl22 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 84pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" width=112>02JUL08:17:25:12</TD><TD class=xl22 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 32pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=43 x:num>12.5</TD></TR></TBODY></TABLE>

This is how it comes to me. I need to be able to calculate the hours to the minute. The current hours are figured in their system and are not correct.

 

[Norie]

I really think you need to include row and column headers.

The formula I posted worked fine when I entered one of your examples in A1, put the formula in A2 and then format as Time.

You can't have a value and a formula in the same cell.

 

[bethieb]

<TABLE style="WIDTH: 681pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=907 summary="Page Layout" border=0 x:str><COLGROUP><COL style="WIDTH: 192pt; mso-width-source: userset; mso-width-alt: 9362" width=256><COL style="WIDTH: 18pt; mso-width-source: userset; mso-width-alt: 877" width=24><COL style="WIDTH: 36pt; mso-width-source: userset; mso-width-alt: 1755" width=48><COL style="WIDTH: 74pt; mso-width-source: userset; mso-width-alt: 3584" width=98><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2742" width=75><COL style="WIDTH: 17pt; mso-width-source: userset; mso-width-alt: 804" width=22><COL style="WIDTH: 37pt; mso-width-source: userset; mso-width-alt: 1792" width=49><COL style="WIDTH: 23pt; mso-width-source: userset; mso-width-alt: 1133" width=31><COL style="WIDTH: 28pt; mso-width-source: userset; mso-width-alt: 1353" width=37><COL style="WIDTH: 84pt; mso-width-source: userset; mso-width-alt: 4096" span=2 width=112><COL style="WIDTH: 32pt; mso-width-source: userset; mso-width-alt: 1572" width=43><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 1pt solid; BORDER-LEFT: black 1pt solid; WIDTH: 192pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=256 height=17></COLGROUP>Date</TD><TD class=xl26 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 1pt solid; BORDER-LEFT: black; WIDTH: 18pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" width=24>A/I</TD><TD class=xl26 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 1pt solid; BORDER-LEFT: black; WIDTH: 36pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" width=48>Badge</TD><TD class=xl26 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 1pt solid; BORDER-LEFT: black; WIDTH: 74pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" width=98>Last Name</TD><TD class=xl26 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 1pt solid; BORDER-LEFT: black; WIDTH: 56pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" width=75>First Name</TD><TD class=xl26 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 1pt solid; BORDER-LEFT: black; WIDTH: 17pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" width=22>MI</TD><TD class=xl26 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 1pt solid; BORDER-LEFT: black; WIDTH: 37pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" width=49>SSN(4)</TD><TD class=xl26 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 1pt solid; BORDER-LEFT: black; WIDTH: 23pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" width=31>IAM</TD><TD class=xl26 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 1pt solid; BORDER-LEFT: black; WIDTH: 28pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" width=37>Craft</TD><TD class=xl26 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 1pt solid; BORDER-LEFT: black; WIDTH: 84pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" width=112>Punch In</TD><TD class=xl26 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 1pt solid; BORDER-LEFT: black; WIDTH: 84pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" width=112>Punch Out</TD><TD class=xl26 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 1pt solid; BORDER-LEFT: black; WIDTH: 32pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" width=43>Hours</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl27 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black 1pt solid; WIDTH: 192pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right width=256 height=17 x:num="39631">7/2/2008</TD><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 18pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" width=24>A</TD><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 36pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=48 x:num>70613</TD><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 74pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" width=98>WURSTER</TD><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 56pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" width=75>THOMAS</TD><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 17pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" width=22>R</TD><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 37pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=49 x:num>8910</TD><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 23pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" width=31> </TD><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 28pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" width=37> </TD><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 84pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" width=112>01JUL08:22:44:22</TD><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 84pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" width=112>02JUL08:10:12:32</TD><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 32pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=43 x:num>11.25</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl27 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black 1pt solid; WIDTH: 192pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right width=256 height=17 x:num="39631">7/2/2008</TD><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 18pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" width=24>A</TD><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 36pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=48 x:num>70615</TD><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 74pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" width=98>TOTH, III</TD><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 56pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" width=75>STEVE</TD><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 17pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" width=22>J</TD><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 37pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=49 x:num>3241</TD><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 23pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" width=31> </TD><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 28pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" width=37> </TD><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 84pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" width=112>01JUL08:17:40:27</TD><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 84pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" width=112>02JUL08:06:24:00</TD><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 32pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=43 x:num>12.5</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl27 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black 1pt solid; WIDTH: 192pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right width=256 height=17 x:num="39631">7/2/2008</TD><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 18pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" width=24>A</TD><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 36pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=48 x:num>70624</TD><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 74pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" width=98>TOLLEY</TD><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 56pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" width=75>DALE</TD><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 17pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" width=22>A</TD><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 37pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=49 x:num>1893</TD><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 23pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" width=31> </TD><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 28pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" width=37> </TD><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 84pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" width=112>01JUL08:16:15:48</TD><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 84pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" width=112>02JUL08:05:33:50</TD><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 32pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=43 x:num>13.25</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl27 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black 1pt solid; WIDTH: 192pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right width=256 height=17 x:num="39631">7/2/2008</TD><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 18pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" width=24>A</TD><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 36pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=48 x:num>70627</TD><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 74pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" width=98>CORNETT</TD><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 56pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" width=75>JOHN</TD><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 17pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" width=22> </TD><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 37pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=49 x:num>4720</TD><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 23pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" width=31> </TD><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 28pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" width=37> </TD><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 84pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" width=112>01JUL08:16:32:00</TD><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 84pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" width=112>02JUL08:05:12:00</TD><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 32pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=43 x:num>12.5</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl27 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black 1pt solid; WIDTH: 192pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right width=256 height=17 x:num="39631">7/2/2008</TD><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 18pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" width=24>A</TD><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 36pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=48 x:num>70630</TD><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 74pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" width=98>BAUER</TD><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 56pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" width=75>CHRIS</TD><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 17pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" width=22> </TD><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 37pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=49 x:num>5711</TD><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 23pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" width=31> </TD><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 28pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" width=37> </TD><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 84pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" width=112>01JUL08:16:27:54</TD><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 84pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" width=112>02JUL08:04:54:06</TD><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 32pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=43 x:num>12.25</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl27 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black 1pt solid; WIDTH: 192pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right width=256 height=17 x:num="39631">7/2/2008</TD><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 18pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" width=24>A</TD><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 36pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=48 x:num>70633</TD><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 74pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" width=98>PENCE</TD><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 56pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" width=75>MATT</TD><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 17pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" width=22> </TD><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 37pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=49 x:num>6471</TD><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 23pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" width=31> </TD><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 28pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" width=37> </TD><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 84pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" width=112>01JUL08:16:29:59</TD><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 84pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" width=112>02JUL08:05:17:55</TD><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 32pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=43 x:num>12.75</TD></TR></TBODY></TABLE>


I know I am seriously coming off as an idiot. Trust me this has been a very bad day! Forgive me. Thank you so much for your help.

 

[DonkeyOte]

for reference, what was the old formula you used that worked when you had just time ?

 

[Norie]

No problem.

I actually meant the Excel row and column headers.

Anyways, try this in column M and copy down

=TIMEVALUE(RIGHT(J2,8))

And for the punch out in column N.

=TIMEVALUE(RIGHT(K2,8))

 

[bethieb]

=text(if(e2<e1,1-e1+e2,e2-e1), "jj.mm")

 

[Norie]

Nobody has posted an IF formula apart from yourself.

Did you try the formula(s) posted on their own?

Or are you somehow trying to incorporate them into an existing formula.

If it's the latter then I think we need to know what that formula is meant to do.

 

[bethieb]

It won't let me post it?? Wacky

 

[bethieb]

Lawsw10 asked what formula I used when it was just in time format. That is the if formula.

Norie - your formula works for changing the time but I have add two columns. Lawsw10 said he would help with just changing my formula to calculate time instead.

Thanks!