OT Tracking system for multiple employees

Mikelowrey

Board Regular
Joined
Apr 20, 2014
Messages
51
Office Version
  1. 2016
Platform
  1. Windows
Good morning,

I would like to create a 2 spreadsheets that would track the overtime on over 50 employees, Cells would constitute of

DATE LASTNAME FIRSTNAME TOUR WORKED AMOUNT OF TIME REASON SUPAPPROVAL LOG # for the OT in time and

DATE LASTNAME FIRSTNAME TOUR WORKED AMOUNT OF TIME CASH REASON SUPAPPROVAL LOG # for the OT in cash


Now, in this I would like to be able to pull weekly, monthly, 28 days, quarterly and yearly reports, now my REAL question is, is this feasible in excel or is it better in access? I have no knowledge of access but I guess I could try if is more easier.

Thank you in advance.
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Comfy

Well-known Member
Joined
Dec 21, 2009
Messages
3,377
It is feasible yes, but I would suggest keeping it to one sheet.

So you would have:

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Date</td><td style=";">LastName</td><td style=";">FirstName</td><td style=";">Tour Worked</td><td style=";">Amount of Time</td><td style=";">Hourly Rate</td><td style=";">Reason</td><td style=";">SupaApproval</td><td style=";">Log #</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br />
There's no need to duplicate the info.
 

Mikelowrey

Board Regular
Joined
Apr 20, 2014
Messages
51
Office Version
  1. 2016
Platform
  1. Windows
It is feasible yes, but I would suggest keeping it to one sheet.

So you would have:

Excel 2010
ABCDEFGHI
3DateLastNameFirstNameTour WorkedAmount of TimeHourly RateReasonSupaApprovalLog #

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1


There's no need to duplicate the info.

Comfy,

Thanks for the quick reply, I was thinking the same, all that info in just one sheet, and then have others sheets run the numbers i guess?

I did state the ways I woul dlike to run the reports, plus the following: by individuals, earnings, hours worked, and like a Query type thing. is this possible again? thanks!
 

Comfy

Well-known Member
Joined
Dec 21, 2009
Messages
3,377
Yep, you can setup several Pivot Tables on different sheets to show what ever info you want.

Here are two very quick examples:

Source Data
<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-style: italic;color: #333333;background-color: #F2F6F8;;">Date</td><td style="font-style: italic;color: #333333;background-color: #F2F6F8;;">LastName</td><td style="font-style: italic;color: #333333;background-color: #F2F6F8;;">FirstName</td><td style="font-style: italic;color: #333333;background-color: #F2F6F8;;">Tour Worked</td><td style="font-style: italic;color: #333333;background-color: #F2F6F8;;">Amount of Time</td><td style="font-style: italic;color: #333333;background-color: #F2F6F8;;">Hourly Rate</td><td style="font-style: italic;color: #333333;background-color: #F2F6F8;;">Total Pay</td><td style="font-style: italic;color: #333333;background-color: #F2F6F8;;">Reason</td><td style="font-style: italic;color: #333333;background-color: #F2F6F8;;">SupaApproval</td><td style="font-style: italic;color: #333333;background-color: #F2F6F8;;">Log #</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">01/01/2014</td><td style=";">A</td><td style=";">A</td><td style=";">Tour 1</td><td style="text-align: right;;">2</td><td style="text-align: right;;">7.5</td><td style="text-align: right;;">15</td><td style=";">Staff Shortage</td><td style=";">T Wilkins</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">02/01/2014</td><td style=";">B</td><td style=";">B</td><td style=";">Tour 1</td><td style="text-align: right;;">5</td><td style="text-align: right;;">10</td><td style="text-align: right;;">50</td><td style=";">Staff Shortage</td><td style=";">T Wilkins</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">03/01/2014</td><td style=";">C</td><td style=";">A</td><td style=";">Tour 1</td><td style="text-align: right;;">3</td><td style="text-align: right;;">7.5</td><td style="text-align: right;;">22.5</td><td style=";">Staff Shortage</td><td style=";">T Wilkins</td><td style="text-align: right;;">3</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">04/01/2014</td><td style=";">D</td><td style=";">B</td><td style=";">Tour 1</td><td style="text-align: right;;">6</td><td style="text-align: right;;">5</td><td style="text-align: right;;">30</td><td style=";">Staff Shortage</td><td style=";">T Wilkins</td><td style="text-align: right;;">4</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">05/01/2014</td><td style=";">A</td><td style=";">A</td><td style=";">Tour 1</td><td style="text-align: right;;">4</td><td style="text-align: right;;">7.5</td><td style="text-align: right;;">30</td><td style=";">Staff Shortage</td><td style=";">T Wilkins</td><td style="text-align: right;;">5</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">06/01/2014</td><td style=";">B</td><td style=";">B</td><td style=";">Tour 1</td><td style="text-align: right;;">8</td><td style="text-align: right;;">10</td><td style="text-align: right;;">80</td><td style=";">Staff Shortage</td><td style=";">T Wilkins</td><td style="text-align: right;;">6</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">07/01/2014</td><td style=";">C</td><td style=";">A</td><td style=";">Tour 1</td><td style="text-align: right;;">5</td><td style="text-align: right;;">7.5</td><td style="text-align: right;;">37.5</td><td style=";">Staff Shortage</td><td style=";">T Wilkins</td><td style="text-align: right;;">7</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">08/01/2014</td><td style=";">D</td><td style=";">B</td><td style=";">Tour 1</td><td style="text-align: right;;">2</td><td style="text-align: right;;">5</td><td style="text-align: right;;">10</td><td style=";">Staff Shortage</td><td style=";">T Wilkins</td><td style="text-align: right;;">8</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;">09/01/2014</td><td style=";">A</td><td style=";">A</td><td style=";">Tour 2</td><td style="text-align: right;;">3</td><td style="text-align: right;;">7.5</td><td style="text-align: right;;">22.5</td><td style=";">Staff Shortage</td><td style=";">T Wilkins</td><td style="text-align: right;;">9</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;">10/01/2014</td><td style=";">B</td><td style=";">B</td><td style=";">Tour 2</td><td style="text-align: right;;">6</td><td style="text-align: right;;">10</td><td style="text-align: right;;">60</td><td style=";">Staff Shortage</td><td style=";">T Wilkins</td><td style="text-align: right;;">10</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;">11/01/2014</td><td style=";">C</td><td style=";">A</td><td style=";">Tour 2</td><td style="text-align: right;;">4</td><td style="text-align: right;;">7.5</td><td style="text-align: right;;">30</td><td style=";">Staff Shortage</td><td style=";">J Doe</td><td style="text-align: right;;">11</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;;">12/01/2014</td><td style=";">D</td><td style=";">B</td><td style=";">Tour 2</td><td style="text-align: right;;">1</td><td style="text-align: right;;">5</td><td style="text-align: right;;">5</td><td style=";">Staff Shortage</td><td style=";">J Doe</td><td style="text-align: right;;">12</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;;">13/01/2014</td><td style=";">A</td><td style=";">A</td><td style=";">Tour 2</td><td style="text-align: right;;">5</td><td style="text-align: right;;">7.5</td><td style="text-align: right;;">37.5</td><td style=";">Staff Shortage</td><td style=";">J Doe</td><td style="text-align: right;;">13</td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;;">01/02/2014</td><td style=";">B</td><td style=";">B</td><td style=";">Tour 2</td><td style="text-align: right;;">8</td><td style="text-align: right;;">10</td><td style="text-align: right;;">80</td><td style=";">Staff Shortage</td><td style=";">J Doe</td><td style="text-align: right;;">14</td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: right;;">02/02/2014</td><td style=";">C</td><td style=";">A</td><td style=";">Tour 2</td><td style="text-align: right;;">4</td><td style="text-align: right;;">7.5</td><td style="text-align: right;;">30</td><td style=";">Staff Shortage</td><td style=";">J Doe</td><td style="text-align: right;;">15</td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: right;;">03/02/2014</td><td style=";">D</td><td style=";">B</td><td style=";">Tour 2</td><td style="text-align: right;;">5</td><td style="text-align: right;;">5</td><td style="text-align: right;;">25</td><td style=";">Staff Shortage</td><td style=";">J Doe</td><td style="text-align: right;;">16</td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: right;;">04/02/2014</td><td style=";">A</td><td style=";">A</td><td style=";">Tour 2</td><td style="text-align: right;;">2</td><td style="text-align: right;;">7.5</td><td style="text-align: right;;">15</td><td style=";">Staff Shortage</td><td style=";">J Doe</td><td style="text-align: right;;">17</td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="text-align: right;;">05/02/2014</td><td style=";">B</td><td style=";">B</td><td style=";">Tour 2</td><td style="text-align: right;;">2</td><td style="text-align: right;;">10</td><td style="text-align: right;;">20</td><td style=";">Staff Shortage</td><td style=";">J Doe</td><td style="text-align: right;;">18</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br />

Summary by Employee and Date
<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;"></td><td style=";">Column Labels</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;">2014</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;"></td><td style=";">Jan</td><td style="text-align: right;;"></td><td style=";">Feb</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">Row Labels</td><td style=";">Hours Worked</td><td style=";">Total Owed</td><td style=";">Hours Worked</td><td style=";">Total Owed</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">A</td><td style="text-align: right;;">14</td><td style="text-align: right;;">105</td><td style="text-align: right;;">2</td><td style="text-align: right;;">15</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">B</td><td style="text-align: right;;">19</td><td style="text-align: right;;">190</td><td style="text-align: right;;">10</td><td style="text-align: right;;">100</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">C</td><td style="text-align: right;;">12</td><td style="text-align: right;;">90</td><td style="text-align: right;;">4</td><td style="text-align: right;;">30</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style=";">D</td><td style="text-align: right;;">9</td><td style="text-align: right;;">45</td><td style="text-align: right;;">5</td><td style="text-align: right;;">25</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style=";">Grand Total</td><td style="text-align: right;;">54</td><td style="text-align: right;;">430</td><td style="text-align: right;;">21</td><td style="text-align: right;;">170</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet5</p><br /><br />

Summarised by Tour and Hourly Rate

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Tour and Hourly Rate</td><td style=";">Hours Worked</td><td style=";">Total Owed</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Tour 1</td><td style="text-align: right;;">35</td><td style="text-align: right;;">275</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">5</td><td style="text-align: right;;">8</td><td style="text-align: right;;">40</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">7.5</td><td style="text-align: right;;">14</td><td style="text-align: right;;">105</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">10</td><td style="text-align: right;;">13</td><td style="text-align: right;;">130</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">Tour 2</td><td style="text-align: right;;">40</td><td style="text-align: right;;">325</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">5</td><td style="text-align: right;;">6</td><td style="text-align: right;;">30</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style=";">7.5</td><td style="text-align: right;;">18</td><td style="text-align: right;;">135</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style=";">10</td><td style="text-align: right;;">16</td><td style="text-align: right;;">160</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style=";">Grand Total</td><td style="text-align: right;;">75</td><td style="text-align: right;;">600</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet4</p><br /><br />
 

Watch MrExcel Video

Forum statistics

Threads
1,109,543
Messages
5,529,456
Members
409,878
Latest member
DDhol
Top