Total Hours Sheet

pedrods

New Member
Joined
Oct 23, 2017
Messages
7
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.

:-/

[TABLE="width: 750"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Name[/TD]
[TD]Total[/TD]
[TD]Time In[/TD]
[TD]Time Out[/TD]
[TD]Time In2[/TD]
[TD]Time Out3[/TD]
[/TR]
[TR]
[TD]Peter[/TD]
[TD]08:57:00[/TD]
[TD]11:02[/TD]
[TD]18:00[/TD]
[TD]18:01[/TD]
[TD]20:00[/TD]
[/TR]
[TR]
[TD]Adrian[/TD]
[TD]08:58:00[/TD]
[TD]11:02[/TD]
[TD]20:00[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Fred[/TD]
[TD]10:58:00[/TD]
[TD]11:02[/TD]
[TD]22:00[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD]11:58:00[/TD]
[TD]11:02[/TD]
[TD]23:00[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Terry[/TD]
[TD]11:52:00[/TD]
[TD]11:08[/TD]
[TD]23:00[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Jannet[/TD]
[TD]12:21:00[/TD]
[TD]11:11[/TD]
[TD]23:32[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Ola[/TD]
[TD]01:00:00[/TD]
[TD]12:59[/TD]
[TD]13:59[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Yanky[/TD]
[TD]00:06:00[/TD]
[TD]12:00[/TD]
[TD]12:06[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
you should wait at least a day before bumping your thread. Apart from anything else, many members focus on threads with zero posts - by bumping your post (twice), you no longer have zero posts on this thread
 
Upvote 0

Forum statistics

Threads
1,223,903
Messages
6,175,289
Members
452,631
Latest member
a_potato

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top