Need to adjust totals proportionally

This is my first post, so please be patient!

I have a set of data from our time reporting system. The data contains 6 columns: Employee name, Project name, Project Activity Number, Project Activity, Number of Hours and Date.

Capture.JPG

I need to sum the hours for each week by employee. If the employee worked more or less than 40 hours, I need to be able to adjust the hours proportionally so they add up to 40.

For example, if an employee worked on 2 projects and spent 25 hours on each during the week, his total would be 50. I need to calculate that the difference is 20%, then subtract 20% from each of the project hours (25 hours) in order to get back to a total of 40.

I download this data once a month to upload into a different system, so I would like it to be fairly well automated.

I created a pivot table by employee/project/activity then grouped the sum of the hours by week, however, I am not sure if this is the right approach, as I don't know what to do next.

Thank you,
Julie

I am stuck figuring out how to make it automatically figure out which lines are totals over 40 hours. I am thinking I need to add some columns that aren't part of the pivot table. Since I have about 7,000 lines of data each month, I really prefer this to be as automated as possible.

I have attached a sample workbook showing what I have done.