Bi-Weekly Payroll Hours Report Formula Help

[davismar]

I have a bi-weekly payroll hours report, that currently 1 grand total of all hours per employee for the 2 week period. It also has a total of hours for each day.

I need to sum the totals for week 1 and week 2.
Also, I need to combine the type of timelogs by Regular, Overtime, Vacation and Sick categories for each week.

Sample Data:

Group Header 1:
Employee = Andy Sum of @ TotalTime = 77.50 hours

Group Header 2:
tblSOLogs.StartDateTime

Week 1 and Week 2 runs from Saturday through Friday
The report is generated on the following Monday after Week 2 end.

Saturday - 0 hours
Sunday - 0 hours
Monday - 8 hours Other - Vacation
Tuesday - 8 hours Regular .5 hour Overtime
Wednesday - 7.5 hours Regular
Thursday - 7.0 hours Regular
Friday - 6.5 hours Regular 1.0 hour Sick

Result Needed:
Week 1 - Regular Hours 29.0 Vacation Hours = 8.0 Sick = 1.0 OT = .5
Total Hours = 38.5


In order to obtain the hours for the categories, they are based on a "time log reason" and the total hours for the week.

Regular = All time log reasons except Other - Vacation, Other - Personal, Other - Sick up to 40 hours. tblSOLogs.TimeLogReason

Overtime = Regular hours greater than 40.0 not including the above 2 codes.

Vacation = Time log reason of "Other - Vacation"

Personal = Time log reason of "Other - Personal" or "Other - Sick"

I will need these totals for each week.
The end result should equal the sum of total hours for each day.

Thanks in advance for the help.

 

[Madawc]

Looks like the sort of thing a Crosstab would handle easily.

You find Crosstab under Insert on the menu. Worth getting to know what it can do, even if it isn't suitable here.

Madawc Williams (East Anglia, UK). Using Crystal 2008 with SQL and Windows XP