Help with data that requires weekly hours worked.

i8ur4re

Board Regular
Joined
Mar 1, 2015
Messages
97
Hi,

I have an excel sheet that has over 34,000 rows. It has the employees name, payroll date, and time card hours.

Example:

Company CodePayroll NameFile NumberPayroll Pay DateTimecard HoursPayCode
AAAJohn Smith001234507/26/20188.00Reg
AAAJohn Smith001234507/27/20188.00Reg
AAAJohn Smith001234508/02/20188.00Reg
AAAJohn Smith001234508/03/20187.50Reg
AAAJohn Smith001234508/09/20188.30Reg
AAAJohn Smith001234508/10/20188.40Reg
AAAJohn Smith 001234508/11/20186.60Reg

<tbody>
</tbody>



This employee is one out of 200, the "Payroll Pay Date" continues to todays date. What I am trying to accomplish is this, for every work week, starting on Monday and ending on Sunday, I would like to calculate that persons hours for that specific week.

So for John Smith, he worked on 07/26/2018 and 07/27/2018, that would be his total hours for the week. His next set of hours for the week would be for 08/02/2018 and 08/03/2018.

I hope that makes sense. All in all, I am trying to break down this excel sheet to show weekly hours worked for each employee, with 34,000 rows.
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
The way i would do this is to create a YYYYWW helper column on your data then create a pivot table with the data based on that. Other than that if you want formula you need to provide the start and end dates and the list of employees then do a SUMIFS formula. These are a bit slow though on lots of rows.
 
Upvote 0
The way i would do this is to create a YYYYWW helper column on your data then create a pivot table with the data based on that. Other than that if you want formula you need to provide the start and end dates and the list of employees then do a SUMIFS formula. These are a bit slow though on lots of rows.


How would i go about creating a YYYYWW helper, also what does that do?

Thank you
 
Upvote 0
It enables you to sum based on a week. You could make lookup table to create it eg

Week StartWeek EndYYYYWW
01/01/201801/07/2018201801
01/08/201801/14/2018201802

<colgroup><col style="mso-width-source:userset;mso-width-alt:4022; width:83pt" width="110" span="3"> </colgroup><tbody>
</tbody>

etc.

You then stamp each transaction with the YYYYWW and create your pivot. You will then have the ability to sum based on the YYYYWW column per name.
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,148
Members
448,552
Latest member
WORKINGWITHNOLEADER

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