Solved I need guidance to calculate Duty hours and Overtime hours

see the function in post #19 and change this:

tm = DateDiff("n", t1, t2) - 60

to:

tm = DateDiff("n", t1, t2)
 
The functiin i made only compute until 6 am. You need to modify it to include upto 8am.
I have applied the module now.
1660895193987.png
 
you can also create a Udf (user-defined function in a module).
you pass to the function:
Code:
'arnelgp
Public Function fnComputeTime(id As Variant, ByVal t_in As Variant, t_out As Variant, ReturnWhat As String) As Double
' parameters:
'
' id            the pk field value
' t_in          time in
' t_out         time out
' ReturnWhat    which value will the function return.
'               either "reg" (regular time/duty time) or "ot" (overtime hour)
'
Static this_id As Variant
Static regular_time As Double, over_time As Double
Dim tm As Variant, t1 As Variant, t2 As Variant
If IsNull(id) Or IsDate(t_in) = False Or IsDate(t_out) = False Or InStr(1, "/reg/ot/", ReturnWhat) = 0 Then
    Exit Function
End If
If id <> this_id Then
    regular_time = 0: over_time = 0
    this_id = id
   t1 = t_in
   t2 = t_out
    ' compute regular time
    If t1 <= #9:00:00 AM# Then
        t1 = #9:00:00 AM#
    End If
    If t_out > #6:30:00 PM# Or t_out <= #6:00:00 AM# Then
        t2 = #6:00:00 PM#
    Else
        If t_out >= #5:45:00 PM# Then
            t2 = #6:00:00 PM#
        End If
    End If
    tm = DateDiff("n", t1, t2) - 60
    regular_time = tm / 60

    ' compute overtime
    tm = 0
    t1 = t_out
    Select Case t1
    Case Is <= #6:00:00 AM#
        tm = 480 '8 hrs x 60 minutes
        tm = tm + DateDiff("n", #12:00:00 AM#, t1)
    Case Is = #12:00:00 AM#
        tm = 480
    Case Is <= #11:59:59 PM#
        tm = DateDiff("n", #6:00:00 PM#, t1)
    End Select
    tm = tm / 60
    over_time = tm
End If
fnComputeTime = IIf(ReturnWhat = "reg", regular_time, over_time)
End Function

on your Query:

DutyTime: fnComputeTime(BDID, time_in, time_out, "reg")
OverTime: fnComputeTime(BDID, time_in, time_out, "ot")
Its not working please guide me.
 
just revisiting this - the example was for a specific date, you need to change ,#2022-08-20 18:00# to be the current date + 18 hours

for Duty hours
DutyHrs:iif(datediff("n",[Time_In],datevalue([[Time_In])+0.75)<525,datediff("n",[Time_In],datevalue([[Time_In])+0.75),540)


for OT hours

OTHours:iif(datediff("n",datevalue([[Time_In])+0.75,[Time_Out])>0,datediff("n",datevalue([[Time_In])+0.75,[Time_Out]),0)+abs(120*(datediff("n",datevalue([[Time_In])+0.75,[Time_Out])>0))

you should just be able to copy/paste this into your query
 
here is another demo, open query1 and input the time_in, time_out
see Module1 for the function.
 

Attachments

arnel prefers the VBA route, I the sql.

So please decide which way you want to go
It doesn't matter which way I go. I want it to be done. I'm not an expert, that's why I'm bothering you guys by asking questions again and again. Please guide me to what will be better for me.
Actually, I am doing this part-time so sometimes I reply late.
 
here is another demo, open query1 and input the time_in, time_out
see Module1 for the function.
Hi, arnelgp its not calculating everything fine. Please check the screenshot attached.

1660977113854.png


Let me tell you the criteria again:
  • All late arrivals after 15 minutes of grace Time and early departure (any time before 6 PM) will be deducted from DutyHours. If someone arrives at 9:15 and leaves at 5:45, 15 minutes will be deducted from duty hours.
  • If someone arrives at 9:16 AM, 16 minutes will be deducted not 1 minute.
  • If someone arrives before 9:00 will be treated the same as 9:00 because there is no Overtime for early arrivals.
    So if someone arrives at 8:45 and leaves at 5:00? 1 Hour will be deducted from his/her duty hours.
  • The hours he/she works after 6 pm will be included in his overtime. On 12 AM, its overtime hours will change to 8 hours. The hours he works after 12 AM will add up to 8 hours. Until 8 am
Explanation:
Time_In
Time_Out
DutyHours
OTHours
9:00 AM to 9:15 AM6:00 PM90
9:00 AM to 9:15 AM7:00 PM91
9:00 AM to 9:15 AM8:00 PM92
9:00 AM to 9:15 AM9:00 PM93
9:00 AM to 9:15 AM10:00 PM94
9:00 AM to 9:15 AM11:00 PM95
9:00 AM to 9:15 AM12:00 AM98 (6 + 2)
9:00 AM to 9:15 AM01:00 AM99 (8 + 1)
9:00 AM to 9:15 AM02:00 AM910 (8 + 2)
9:00 AM to 9:15 AM03:00 AM911 (8 + 3)
9:00 AM to 9:15 AM04:00 AM912 (8 + 4)
9:00 AM to 9:15 AM05:00 AM913 (8 + 5)
9:00 AM to 9:15 AM06:00 AM914 (8 + 6)
9:00 AM to 9:15 AM07:00 AM915 (8 + 7)
9:00 AM to 9:15 AM08:00 AM916 (8 + 8)
 
Ok well I’ve provided what I think is the way forward so I’ll drop out. We’re here to help, not do your job for you and I’d rather be helping others than pursuing 2 different solutions. Go with arnel’s suggestion

good luck with your project
 
Ok well I’ve provided what I think is the way forward so I’ll drop out. We’re here to help, not do your job for you and I’d rather be helping others than pursuing 2 different solutions. Go with arnel’s suggestion

good luck with your project
Thank you very much for your help.
 
here Mr.Khurram, check and test the function again.
Mr. Arnel,
Thank you very much! Each and every calculation is working perfectly in your demo file. I am just going to use this module in my database. Kindly tell me one thing that I have ID field with the name "BTID" instead of "ID" should I modify this module in my database?
 
you need to Copy the module from my latest demo db.
is BTID unique? if not select a unique key to use in the query.
 

Users who are viewing this thread

Back
Top Bottom