Running Totals Calculating Overtime

[dhgrrg]

I need to calculate a running total of elapsed hours for a period and so the day overtime occurrs it is shown

It is simple if I run it for the entire period but I have problems when I run it for a partial period that does not include the first working day..

Example:.
During Period 36 which starts Tuesday at 2:00AM and runs until the following Tuesday @ 1:59 AM

Mary works
9/7 Tues 10 hrs 10 Running Total
9/8 Wed 16 hrs 26 RT
9/9 Thurs 10 hrs 36 RT
9/10 Friday 8 hrs 44 RT
9/11 Saturday 6 hrs 50 RT

When a daily report is run for Friday the hours worked should show (because of 40 hr work week)
Reg 8 hours OT hours 4

When a daily report is run for Saturday, the hours would show
Reg 6 Hrs OT Hours 6

How can I designate and individual date Ex. 9/10 and calculate all hours from the beginning of that period thru that 9/10 so I can calculate overtime?


NOTE: Clock In / Clock out (both date time) AND period is stored with each Record.

I am familiar with subreports and have a little experience with variables.

 

[lbass]

I would set this up with a parameter {?date} for the date of interest. For the record selection formula use:

{table.datetime} in {?date}-dayofweek({?date},crTuesday)+1+time(2,0,0) to {?date}+1+time(1,59,59)

Insert a group on employee and then another group on the following formula {@week}:

datepart("ww",{table.date},crTuesday)

To display the week as the group name, check "customize group name" and "use a formula as group name and enter:

totext(dateadd("ww",{@week}-1,date(year({table.date}),01,01)-dayofweek(date(year({table.date}),01,01),crTuesday)+1),"MM/dd/yyyy")+" to "+
totext(dateadd("ww",{@week}-1,date(year({table.date}),01,01)-dayofweek(date(year({table.date}),01,01),crTuesday)+8),"MM/dd/yyyy")

Create a formula {@hrswkd}:

datediff("h",{table.clockin},{table.clockout})

Then use the running total editor to create the running total {#wklyhrswkd} by selecting {@hrswkd}, sum, evaluate for each record, reset on change of group (@week).

Next create a formula {@regOT}:

if {#wklyhrswkd} <= 40 then
"Reg hours: " + totext({@hrswkd},0) + " OT hours: 0" else
if {#wklyhrswkd} > 40 then
"Reg hours: " + totext({@hrswkd},0) + " OT hours: " +
totext({#wklyhrswkd}-40,0)

If you only want to display the results for the parameter date, then go to the section expert->details->suppress->x+2 and enter:

not({table.datetime} in {?date}+time(2,0,0) to {?date}+1+time(1,59,59))

-LB

 

[dhgrrg]

Thanks --- I'll start working thru the logic.

rrg