query to figure out deductions in pay based on times

[Elmobram22]

Hi,

I am creating a timesheet database for work. We will be using TimeStation app which allows export of excel documents. I have linked the table with the same headings as the excel dosument and am importing the data based on that. What I want to be able to do is run a query that gives us deductions for breaks based on a few values.

I would need it to deduct 0.75 hours from any shift longer than 6 hours where the person clocked in between 8.30am and 12.50pm

I would need to deduct 0.5 hours from any shift longer than 6 hours where the person clocked in between 12.51pm and 5.31pm

and To deduct 1.25 hours from any shift over 12 hours which cancels out the above.

I have uploaded the database to give an idea what data we have to work with.

Thanks,

Paul

 

[plog]

The below code should do what you want:

Function getDeductions(Tin, Tout) As Double
    ' determines deduction in time based on time in (Tin) and time out (Tout)
 
Dim ret As Double                   ' return value of function, will hold deduction
Dim Tdif As Double                    ' holds difference in time between Tin and Tout
Dim TinMinute As Integer              ' holds minute of day timed in
 
ret = 0
Tdif = DateDiff("n", Tin, Tout)
TinMinute = Hour(Tin) * 60 + Minute(Tin)
 
If Tdif > 360 Then
  ' calculates deductions for working more than 6 hours, based on time Clocked in
    If TinMinute >= 510 And TinMinute <= 770 Then ret = 0.75
    If TinMinute >= 771 And TinMinute <= 1051 Then ret = 0.5
    End If
 
If Tdif > 720 Then ret = 1.25
    ' if worked over 12 hours deduction is 1.25 regardless of time in
 
getDeductions = ret
 
End Function

Paste that into a module, then use it in a query like this:

Deductions: getDeductions([In],[Out])

 

[Elmobram22]

The below code should do what you want:

Function getDeductions(Tin, Tout) As Double
    ' determines deduction in time based on time in (Tin) and time out (Tout)
 
Dim ret As Double                   ' return value of function, will hold deduction
Dim Tdif As Double                    ' holds difference in time between Tin and Tout
Dim TinMinute As Integer              ' holds minute of day timed in
 
ret = 0
Tdif = DateDiff("n", Tin, Tout)
TinMinute = Hour(Tin) * 60 + Minute(Tin)
 
If Tdif > 360 Then
  ' calculates deductions for working more than 6 hours, based on time Clocked in
    If TinMinute >= 510 And TinMinute <= 770 Then ret = 0.75
    If TinMinute >= 771 And TinMinute <= 1051 Then ret = 0.5
    End If
 
If Tdif > 720 Then ret = 1.25
    ' if worked over 12 hours deduction is 1.25 regardless of time in
 
getDeductions = ret
 
End Function

Paste that into a module, then use it in a query like this:

Deductions: getDeductions([In],[Out])

Absolutely perfect that! Thanks a lot!!