# 40 hour ratio formula

#### sdurocher

##### Board Regular
I need a formula on my Access query to sum hours; by person, by pay period, by project and If those hours are greater then 40 then calculate a percetange of those project hours to the sumed hour and mulitple them by 40.

The hours for each person per pay period can not be greater that 40 for capiltaztion purposes. I need to get what the true hours are and change the hour (if over 40) to the a ratio per project for a 40 hour week.

SELECT Union_Dates.PayPeriod, Union_Dates.Approve, Union_Dates.Name, Union_Dates.ProjectName, Union_Dates.ProjectDescription, Union_Dates.dbo_TimeSheetTable.Date1, Format([Months.Date1],'mmm') AS [Month]
FROM Union_Dates
GROUP BY Union_Dates.PayPeriod, Union_Dates.Approve, Union_Dates.Name, Union_Dates.ProjectName, Union_Dates.ProjectDescription, Union_Dates.Billable, Union_Dates.dbo_TimeSheetTable.Date1, Format([Months.Date1],'mmm')
HAVING (((Union_Dates.Approve)="A") AND ((Union_Dates.Billable)="Y"));

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Replies
5
Views
1K
Replies
1
Views
680
Replies
3
Views
1K
Replies
16
Views
676
Replies
0
Views
1K

1,191,040
Messages
5,984,295
Members
439,882
Latest member
gerdc

### 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?

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