Totalling Hours

[ielashi]

Hello,
I have a report that calculates the salaries of employees. In addition to that it calculates overtime hours. Now, I've been trying to work on a formula that would calculate the number of overtime hours. Which is supposed to be if an employee stayed for more than 8 hours at work. It would calculate the rest of the time as overtime. So let's say if an employee worked for 11 hours it would put in the field 3. Any ideas?

[Dnereb]

did you look at DateDiff you can add all working time with this function in minutes ("n"), subtract the amount of minutes that aren't overtime.
if the result is positive you've got overtime if it's negative to employee has taken some free time.

[ielashi]

Well, actually, it's a little complicated than that. Because the employee can punchin and punchout twice on the same day. So perhaps what I was looking for is the sum of DateDiff per day and then calculate if it's over 8 hours then there is over time else there is no over time. If someone can give me a forumla it would really be appreciated. Thanx.

[Ecniv]

Don't know why you cannot read the help files and experiment then post up that experiment...?

Perhaps something like:

Select 
 TheDateF, 
 sqryDiffs.TheTimeDiff as TotalHoursDay, 
 iif((sqryDiffs.TheTimeDiff * 24)>8,(sqryDiffs.TheTimeDiff * 24)-8,0) as OverTime
From
 (select table.employeeid, format(table.thedate,'dd mmm yyyy') as TheDateF, sum(table.clockout-table.clockin) as TheTimeDiff
  From table
  Group By table.employeeid, format(table.thedate,'dd mmm yyyy')
 ) as sqryDiffs

The sub query gets each difference from clockin to clockout and sums them together, grouped by the employeeid and the date the record applies too (thedate).
The main query theen uses the total and works out if it is greater than 8 to subtract 8 to give the over time done.
Now the only possible problem is that the differences would be in fractions of a day (1 being 24 hrs) so I have added that in as well.
Try it and see if it works. If not perhaps you can change it to work correctly, or use it as a base to build a different solution.

good luck

[ielashi]

So, I would make an SQL expression field, paste the following in it, right?
But what should substitute thedateF for?