Accruing months of payroll

Trying to figure out how to accrue data for multiple months. Data consists of thousands of lines each with a different pay date for employees. For instance I show up on 26 different lines for data pulled for an entire year (paid bi-weekly). I need to show what my payroll (and everyone else's) is each month using an accrual calculation. I can easily accrue one month at a time but I cant accrue all 12 months since a pay date can applies to two months accrual calculation. I cant find any examples of this anywhere. Pivot table would be ideal. I just dont know how to go about accomplishing that. Any resource would help. I have attached a sample of data with an accrual tab that has the calculations for each pay period. On the first tab is what Id like the pivot table to give me (bottom right).

Hi and welcome to the forum.
Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.

Show a before and after situation with manually calculated results, explaining which information is data and which is results, and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.

To upload a file click the Go Advanced button at the foot of your post, look underneath the post area for the Manage Attachments section and take it from there.

Is Excel 2013 really your latest version or do you have later versions which will have more functionality?

This proposal employs four added columns to the data.
The first column is populated using: =EOMONTH(D2,-1)+1
The second column is populated using:

Formula:

Please Login or Register  to view this content.

The third column is populated using:

Formula:

Please Login or Register  to view this content.

The fourth column is populated using: =SUMPRODUCT((B$2:B2=B2)*(MONTH(C$2:C2)=MONTH(C2)))
On Sheet1 the pivot table has a calculated field called Pay which has the formula: ='Gross Pay'+'Fut. Acc.' -'Prior Acc.'
Note that Fut. Acc. and Prior Acc. are new columns from the Data sheet.
Let us know if you have any questions.

Originally Posted by tonynelson33

I updated appropriately.

Thanks,

Information about exactly which version would have been better.

You're Welcome and thank you for the feedback. If the method works with the 100k rows of data then please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post.
Attempting to view the attachment results in: "Invalid Attachment specified. If you followed a valid link, please notify the administrator"
BTW, I was in your fair city in June, had a great time and hope to get back next year.
I hope that you have a blessed day.