Calculate Average Monthly Hours Based On Week Ending

[Goetch]

Hi, I have a table called "Employee Hours" that has Fields "Weekending"(date) and "employeename"(text). I would like to average out the daily hours so I can take "Employee Hours" and divide by 7 and get a daily average for that week. I want to somehow apply that weekly daily average to a month. Any ideas on how I could proceed with this?

An example for October 2011

Bill had 10 hours for weekending 10/1/2011 30 hours for weekending 10/8/2011 15 hours for weekending 10/15/2011 40 hours for week ending 10/29/2011 and 25 hours for week ending 11/5/2011.

Each entry is a record in my database. So I want to just find the average daily hours for 10/1/2011 to 10/31/2011 how would I go about that?

We use this average to calculate an average daily productivity with the person sales numbers (in a separate table linked by employee name) Unfortunately the hours data will only come in with a totals hours worked and a weekending from the payroll dept.

[comment]

So I want to just find the average daily hours for 10/1/2011 to 10/31/2011

You can't really find that with what you have. The nearest you could get would be:

10 hours for week ending 10/1/2011 (1 day in October)

30 hours for week ending 10/8/2011 (7 days in October)

15 hours for week ending 10/15/2011 (7 days in October)

20 hours for week ending 10/22/2011 (7 days in October)

40 hours for week ending 10/29/2011 (7 days in October)

25 hours for week ending 11/5/2011 (2 days in October)

10 hours * 1 / 7 = 1.43

30 hours * 7 / 7 = 30

15 hours * 7 / 7 = 5

20 hours * 7 / 7 = 20

40 hours * 7 / 7 = 40

25 hours * 2 / 7 = 7.14

giving 113.57 estimated total hours in October. October has 31 days, so 113.57 / 31 = 3.66 hours per day. Of course, this completely ignores weekends and holidays.

The other issue is that the calculation takes the month (here: October, 2011) as one of its inputs - so it won't be easy to produce a report accounting for more than one month.