Proration of hours

I have a timesheet report that tracks hundreds of people and what projects they work on on a daily basis. So any given person can have multiple entries per day. I also have a column that shows the week #.

Since the majority of our employees aren't paid overtime I need to prorate hours based on a standard 40 hour work week. So if there's 20 entries for a given employee for the week and that totals 48 hours I need to take each of those down by a percentage to a total of 40 hours. If the 20 entries are equal to or less than 40 than it just takes that time.

I can't seem to get a formula that sums the hours by person, by week and if it's over 40 prorates it by the current distribution and if it's not just gives me the current distribution. Any suggestions?

Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

First in create additional column "I"
In "I2"

Formula:

Please Login or Register  to view this content.

"G2"

Formula:

Please Login or Register  to view this content.

"J2"

Formula:

Please Login or Register  to view this content.