Calculating Time

[erric]

I have a spreadsheet detailing employee time swipes through a card reader. Unfortunately, all of the time data is in a single column. What is the easiest way to calculate hours worked per day? I'm looking at all data for all employees from January 2011 through February 2011, so it's a large set to have to parse manually.

 

[Trouttrap2]

Can you give us a sample of your Data?

 

[VoG]

Please post an example.

 

[Rasm]

just do straight subtraction of the two values - it will return the diff

 

[erric]

Below is a sample. There are three columns: Card ID, Time, and a column detailing whether it was a swipe into the building or out of the building.

<table width="275" border="0" cellpadding="0" cellspacing="0"><colgroup><col style="mso-width-source:userset;mso-width-alt:2986;width:53pt" width="70"> <col style="mso-width-source:userset;mso-width-alt:3669;width:65pt" width="86"> <col style="mso-width-source:userset;mso-width-alt:5077;width:89pt" width="119"> </colgroup><tbody><tr style="height:11.25pt" height="15"> <td style="height:11.25pt;width:53pt" width="70" height="15">Card Number</td> <td style="width:65pt" width="86">Gen Time</td> <td style="vertical-align: top;">
</td><td style="width:89pt" width="119">Reader</td> </tr> <tr style="height:11.25pt" height="15"> <td style="height:11.25pt" align="right" height="15">20611</td> <td class="xl65" align="right">1/3/2011 5:49</td> <td style="vertical-align: top;">
</td><td>Office Access IN #1</td> </tr> <tr style="height:11.25pt" height="15"> <td style="height:11.25pt" align="right" height="15">20611</td> <td class="xl65" align="right">1/3/2011 15:23</td> <td style="vertical-align: top;">
</td><td>Office Access OUT #2</td> </tr> <tr style="height:11.25pt" height="15"> <td style="height:11.25pt" align="right" height="15">20611</td> <td class="xl65" align="right">1/4/2011 5:55</td> <td style="vertical-align: top;">
</td><td>Office Access IN #1</td> </tr> <tr style="height:11.25pt" height="15"> <td style="height:11.25pt" align="right" height="15">20611</td> <td class="xl65" align="right">1/4/2011 10:37</td> <td style="vertical-align: top;">
</td><td>Office Access OUT #2</td> </tr> <tr style="height:11.25pt" height="15"> <td style="height:11.25pt" align="right" height="15">20611</td> <td class="xl65" align="right">1/4/2011 11:57</td> <td style="vertical-align: top;">
</td><td>Office Access IN #1</td> </tr> <tr style="height:11.25pt" height="15"> <td style="height:11.25pt" align="right" height="15">20611</td> <td class="xl65" align="right">1/4/2011 15:23</td> <td style="vertical-align: top;">
</td><td>Office Access OUT #2</td> </tr> <tr style="height:11.25pt" height="15"> <td style="height:11.25pt" align="right" height="15">20611</td> <td class="xl65" align="right">1/5/2011 5:50</td> <td style="vertical-align: top;">
</td><td>Office Access IN #1</td> </tr> <tr style="height:11.25pt" height="15"> <td style="height:11.25pt" align="right" height="15">20611</td> <td class="xl65" align="right">1/5/2011 15:22</td> <td style="vertical-align: top;">
</td><td>Office Access OUT #2</td> </tr> <tr style="height:11.25pt" height="15"> <td style="height:11.25pt" align="right" height="15">20611</td> <td class="xl65" align="right">1/6/2011 5:53</td> <td style="vertical-align: top;">
</td><td>Office Access IN #1</td> </tr> <tr style="height:11.25pt" height="15"> <td style="height:11.25pt" align="right" height="15">20611</td> <td class="xl65" align="right">1/6/2011 15:22</td> <td style="vertical-align: top;">
</td><td>Office Access OUT #2</td> </tr> <tr style="height:11.25pt" height="15"> <td style="height:11.25pt" align="right" height="15">20611</td> <td class="xl65" align="right">1/7/2011 5:52</td> <td style="vertical-align: top;">
</td><td>Office Access IN #1</td> </tr> <tr style="height:11.25pt" height="15"> <td style="height:11.25pt" align="right" height="15">20611</td> <td class="xl65" align="right">1/7/2011 11:54</td> <td style="vertical-align: top;">
</td><td>Office Access OUT #2</td> </tr> <tr style="height:11.25pt" height="15"> <td style="height:11.25pt" align="right" height="15">20611</td> <td class="xl65" align="right">1/10/2011 5:53</td> <td style="vertical-align: top;">
</td><td>Office Access IN #1</td> </tr> <tr style="height:11.25pt" height="15"> <td style="height:11.25pt" align="right" height="15">20611</td> <td class="xl65" align="right">1/10/2011 6:08</td> <td style="vertical-align: top;">
</td><td>Office Access OUT #2</td> </tr> <tr style="height:11.25pt" height="15"> <td style="height:11.25pt" align="right" height="15">20611</td> <td class="xl65" align="right">1/10/2011 6:10</td> <td style="vertical-align: top;">
</td><td>Office Access IN #1</td> </tr> <tr style="height:11.25pt" height="15"> <td style="height:11.25pt" align="right" height="15">20611</td> <td class="xl65" align="right">1/10/2011 15:23</td> <td style="vertical-align: top;">
</td><td>Office Access OUT #2</td> </tr> <tr style="height:11.25pt" height="15"> <td style="height:11.25pt" align="right" height="15">20611</td> <td class="xl65" align="right">1/11/2011 5:51</td> <td style="vertical-align: top;">
</td><td>Office Access IN #1</td> </tr> <tr style="height:11.25pt" height="15"> <td style="height:11.25pt" align="right" height="15">20611</td> <td class="xl65" align="right">1/11/2011 15:22</td> <td style="vertical-align: top;">
</td><td>Office Access OUT #2</td> </tr> <tr style="height:11.25pt" height="15"> <td style="height:11.25pt" align="right" height="15">20611</td> <td class="xl65" align="right">1/12/2011 5:53</td> <td style="vertical-align: top;">
</td><td>Office Access IN #1</td> </tr> <tr style="height:11.25pt" height="15"> <td style="height:11.25pt" align="right" height="15">20611</td> <td class="xl65" align="right">1/12/2011 11:49</td> <td style="vertical-align: top;">
</td><td>Office Access OUT #2</td> </tr> <tr style="height:11.25pt" height="15"> <td style="height:11.25pt" align="right" height="15">20611</td> <td class="xl65" align="right">1/12/2011 11:50</td> <td style="vertical-align: top;">
</td><td>Office Access IN #1</td> </tr> <tr style="height:11.25pt" height="15"> <td style="height:11.25pt" align="right" height="15">20611</td> <td class="xl65" align="right">1/12/2011 15:24</td> <td style="vertical-align: top;">
</td><td>Office Access OUT #2</td> </tr> <tr style="height:11.25pt" height="15"> <td style="height:11.25pt" align="right" height="15">20611</td> <td class="xl65" align="right">1/13/2011 5:55</td> <td style="vertical-align: top;">
</td><td>Office Access IN #1</td> </tr> <tr style="height:11.25pt" height="15"> <td style="height:11.25pt" align="right" height="15">20611</td> <td class="xl65" align="right">1/13/2011 15:38</td> <td style="vertical-align: top;">
</td><td>Office Access OUT #2</td> </tr> <tr style="height:11.25pt" height="15"> <td style="height:11.25pt" align="right" height="15">20611</td> <td class="xl65" align="right">1/14/2011 5:52</td> <td style="vertical-align: top;">
</td><td>Office Access IN #1</td> </tr> <tr style="height:11.25pt" height="15"> <td style="height:11.25pt" align="right" height="15">20611</td> <td class="xl65" align="right">1/14/2011 11:53</td> <td style="vertical-align: top;">
</td><td>Office Access OUT #2</td> </tr> </tbody></table>

 

[erric]

Still looking for help if anyone has a solution. Thanks!