Problem:  I need to be able to capture 2 weeks worth of pay roll data, but only calculate over time 1 week at a time.  Right now, I run 2 separate reports.  One calculating the first week of the pay period and the second for the second week.  I export these two reports into Excel, sort them by associate and then subtotal the hours.  We pay overtime if an associate works more than 40 hours per week, I know there's a way...just can't figure it out!  HELP :)

I have the data in Group Header 2 (by name) and it holds the following:

 

BU (business unit)

PR ID (pay roll ID)

Full Name

@REG HRS

@OT HRS

@VAC HRS

 

 

REG HRS Formula:

if ({@OT HRS}>0.00) then
(sum ({@NON PTO HOURS},{PUNCH.EMPLOYEE ID}) - {@OT HRS})
else (sum ({@NON PTO HOURS},{PUNCH.EMPLOYEE ID}))

 

OT HRS Formula:

if sum({PUNCH.DEPT HOURS},{PUNCH.EMPLOYEE ID})> 40.00
then sum({PUNCH.DEPT HOURS},{PUNCH.EMPLOYEE ID}) - 40.00
else 0

 

VAC HRS Formula:

IF {PUNCH.NONWORKED} = "Vac"
THEN
{PUNCH.DEPT HOURS}
else 0.00

 

I realize the way the OT formula is written that it's only going to calculate OT based on a 40 hour week.  I would really like to rewrite that based on the running total of hours per week - make sense?