Hi there everyone,
I need some help please with this little project i decided to put together.
Essentially, the idea is an excel sheet with everyday of the week represented by a tab each. I use a barcode scanner gun to scan employee names into the sheet. The sheet automatically writes a time stamp everytime a new employee is scanned into the Monday tab for example.
A simple formula then works out the total hours for the day for each employee. The next bit is where i get lost, i want each employee "total day worked time", Monday - Saturday to show as a total weekly hours worked on a separate tab which summarises each tab's employee hours worked.
Each tab which represents a day of the week will have each employee scan in to the sheet randomly through out the week, so no two days are the same.
1. Scan in
2. Scan out
3. Calculate total time per day
4. Calculate total employee time for the week
5. Calculate Overtime worked
That's basically what im trying to achieve, but want it to be automated so i dont have to create pivot tables each week or "consolidate" functions which are all manual options.
How hard can this be?
(Below i have attached a quick snapshot of what the format looks like when it captures the data i want.
There's Time in and Out - two times to allow for breaks etc.
:-/
<colgroup><col><col><col><col><col><col></colgroup><tbody>
</tbody>
I need some help please with this little project i decided to put together.
Essentially, the idea is an excel sheet with everyday of the week represented by a tab each. I use a barcode scanner gun to scan employee names into the sheet. The sheet automatically writes a time stamp everytime a new employee is scanned into the Monday tab for example.
A simple formula then works out the total hours for the day for each employee. The next bit is where i get lost, i want each employee "total day worked time", Monday - Saturday to show as a total weekly hours worked on a separate tab which summarises each tab's employee hours worked.
Each tab which represents a day of the week will have each employee scan in to the sheet randomly through out the week, so no two days are the same.
1. Scan in
2. Scan out
3. Calculate total time per day
4. Calculate total employee time for the week
5. Calculate Overtime worked
That's basically what im trying to achieve, but want it to be automated so i dont have to create pivot tables each week or "consolidate" functions which are all manual options.
How hard can this be?
(Below i have attached a quick snapshot of what the format looks like when it captures the data i want.
There's Time in and Out - two times to allow for breaks etc.
:-/
| Name | Total | Time In | Time Out | Time In2 | Time Out3 |
| Peter | 08:57:00 | 11:02 | 18:00 | 18:01 | 20:00 |
| Adrian | 08:58:00 | 11:02 | 20:00 | ||
| Fred | 10:58:00 | 11:02 | 22:00 | ||
| Bob | 11:58:00 | 11:02 | 23:00 | ||
| Terry | 11:52:00 | 11:08 | 23:00 | ||
| Jannet | 12:21:00 | 11:11 | 23:32 | ||
| Ola | 01:00:00 | 12:59 | 13:59 | ||
| Yanky | 00:06:00 | 12:00 | 12:06 |
<colgroup><col><col><col><col><col><col></colgroup><tbody>
</tbody>
