Help with data that requires weekly hours worked.

[i8ur4re]

Hi,

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

Example:

[TABLE="width: 500, align: center"]
<tbody>[TR]
[TD]Company Code[/TD]
[TD]Payroll Name[/TD]
[TD]File Number[/TD]
[TD]Payroll Pay Date[/TD]
[TD]Timecard Hours[/TD]
[TD]PayCode[/TD]
[/TR]
[TR]
[TD]AAA[/TD]
[TD]John Smith[/TD]
[TD]0012345[/TD]
[TD]07/26/2018[/TD]
[TD]8.00[/TD]
[TD]Reg[/TD]
[/TR]
[TR]
[TD]AAA[/TD]
[TD]John Smith[/TD]
[TD]0012345[/TD]
[TD]07/27/2018[/TD]
[TD]8.00[/TD]
[TD]Reg[/TD]
[/TR]
[TR]
[TD]AAA[/TD]
[TD]John Smith[/TD]
[TD]0012345[/TD]
[TD]08/02/2018[/TD]
[TD]8.00[/TD]
[TD]Reg[/TD]
[/TR]
[TR]
[TD]AAA[/TD]
[TD]John Smith[/TD]
[TD]0012345[/TD]
[TD]08/03/2018[/TD]
[TD]7.50[/TD]
[TD]Reg[/TD]
[/TR]
[TR]
[TD]AAA[/TD]
[TD]John Smith[/TD]
[TD]0012345[/TD]
[TD]08/09/2018[/TD]
[TD]8.30[/TD]
[TD]Reg[/TD]
[/TR]
[TR]
[TD]AAA[/TD]
[TD]John Smith[/TD]
[TD]0012345[/TD]
[TD]08/10/2018[/TD]
[TD]8.40[/TD]
[TD]Reg[/TD]
[/TR]
[TR]
[TD]AAA[/TD]
[TD]John Smith [/TD]
[TD]0012345[/TD]
[TD]08/11/2018[/TD]
[TD]6.60[/TD]
[TD]Reg[/TD]
[/TR]
</tbody>[/TABLE]



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.

 

[steve the fish]

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.

 

[i8ur4re]

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

 

[steve the fish]

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

<colgroup><col style="mso-width-source:userset;mso-width-alt:4022; width:83pt" width="110" span="3"> </colgroup><tbody>
[TD="class: xl63, width: 110"]Week Start[/TD]
[TD="class: xl63, width: 110"]Week End[/TD]
[TD="class: xl63, width: 110"]YYYYWW
[/TD]

[TD="class: xl64"]01/01/2018[/TD]
[TD="class: xl64"]01/07/2018[/TD]
[TD="class: xl63"]201801[/TD]

[TD="class: xl64"]01/08/2018[/TD]
[TD="class: xl63"]01/14/2018[/TD]
[TD="class: xl63"]201802[/TD]

</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.