Time Card Query Issue (dateDiff)

[ShostyFan]

I have a time card application with an issue. If someone clocks in and then clocks out, the below executes as expected. The issue is if they clock in and out multiple times during the same day and the total amount worked is greater then 8 hours no overtime is granted. How can I group TotalTime by the same day?

TotalTime: DateDiff("n",[StartTime],[FinishTime])
overtimeMin: IIf([TotalTime]>480,[TotalTime]-480)
OverTimeHours: [overtimeMin]\60 & Format([overTimeMin] Mod 60,"\:00")


TotalMinLessOvertime: IIf([TotalTime]>480,480,[TotalTime])


Thanks,

[June7]

Maybe calc the date part of the StartTime value and do aggregate query that groups on that.

Complication arises if workday crosses midnight.

[Rawb]

The best way to handle this is to store three pieces of information for each record:
ClockInDate: This is the date the user clocked in on. This should be stored separately so that later on, you can use this field in a GROUP BY clause. Formatted as a Short Date.
StartTime: This is the date and time the user clocked in. Formatted as a General Date.
FinishTime: This is the date and time the user clocked out. Formatted as a General Date.

Then, to get your minutes worked, change your Query to Totals and use the following calculated fields:

TotalTime: Sum(DateDiff("n",[StartTime],[FinishTime]))
overtimeMin: IIf(Sum(DateDiff("n",[StartTime],[FinishTime]))>480,Sum(DateDiff("n",[StartTime],[FinishTime]))-480, 0)
OverTimeHours: Format(Int(IIf(Sum(DateDiff("n",[StartTime],[FinishTime]))>480,Sum(DateDiff("n",[StartTime],[FinishTime]))-480, 0)/60), "00") & Format(IIf(Sum(DateDiff("n",[StartTime],[FinishTime]))>480,Sum(DateDiff("n",[StartTime],[FinishTime]))-480, 0) Mod 60,"\:00")
TotalMinLessOvertime: Sum(DateDiff("n",[StartTime],[FinishTime]))-IIf(Sum(DateDiff("n",[StartTime],[FinishTime]))>480,Sum(DateDiff("n",[StartTime],[FinishTime]))-480,0)

For all 4 of these fields, you will need to change the Totals column from "Group By" to "Expression".

I know the OverTimeHours field is particularly ugly, but that's a byproduct of using a Totals Query - You can no longer just use the alias, you have to re-enter the whole formula.

Check out the attached DB for an example.

[ShostyFan]

thank you so much for the time you put into this. I was able to get everything working exactly the way I wanted it to.