Solved I need guidance to calculate Duty hours and Overtime hours (1 Viewer)

[arnelgp]

see the function in post #19 and change this:

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

to:

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

 

[arnelgp]

Can I use this in my own query? I am already running a database.

The functiin i made only compute until 6 am. You need to modify it to include upto 8am.

 

[arnelgp]

I want 9:00 AM to 6:00 PM = 9 hours

Standard work hrs is 8 hrs only (9 minus 1 hr break)

 

[Khurramshaikh47]

The functiin i made only compute until 6 am. You need to modify it to include upto 8am.

I have applied the module now.

 

[Khurramshaikh47]

Standard work hrs is 8 hrs only (9 minus 1 hr break)

yes But we need 9 hours in total when Time-In is 9:00 AM and Time-Out is 6:00 PM

 

[Khurramshaikh47]

I have applied the module now.
View attachment 102656

arnelgp Can you please guide me on what's wrong here? Why DutyTime is -1074568?

I've changed the code in module as you told:

Applied fnComputeTime function in my Query:

 

[Khurramshaikh47]

you can also create a Udf (user-defined function in a module).
you pass to the function:

'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.

 

[CJ_London]

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

 

[arnelgp]

here is another demo, open query1 and input the time_in, time_out
see Module1 for the function.

 

[CJ_London]

arnel prefers the VBA route, I the sql.

So please decide which way you want to go

 

[Khurramshaikh47]

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.

 

[Khurramshaikh47]

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.

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 AM	6:00 PM	9	0
9:00 AM to 9:15 AM	7:00 PM	9	1
9:00 AM to 9:15 AM	8:00 PM	9	2
9:00 AM to 9:15 AM	9:00 PM	9	3
9:00 AM to 9:15 AM	10:00 PM	9	4
9:00 AM to 9:15 AM	11:00 PM	9	5
9:00 AM to 9:15 AM	12:00 AM	9	8 (6 + 2)
9:00 AM to 9:15 AM	01:00 AM	9	9 (8 + 1)
9:00 AM to 9:15 AM	02:00 AM	9	10 (8 + 2)
9:00 AM to 9:15 AM	03:00 AM	9	11 (8 + 3)
9:00 AM to 9:15 AM	04:00 AM	9	12 (8 + 4)
9:00 AM to 9:15 AM	05:00 AM	9	13 (8 + 5)
9:00 AM to 9:15 AM	06:00 AM	9	14 (8 + 6)
9:00 AM to 9:15 AM	07:00 AM	9	15 (8 + 7)
9:00 AM to 9:15 AM	08:00 AM	9	16 (8 + 8)

 

[CJ_London]

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

 

[Khurramshaikh47]

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.

 

[arnelgp]

here Mr.Khurram, check and test the function again.

 

[Khurramshaikh47]

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?

 

[arnelgp]

with the name "BTID" instead of "ID" should I modify this module in my database

dont modify the module, just pass BTID instead of ID from the Query.

 

[Khurramshaikh47]

dont modify the module, just pass BTID instead of ID from the Query.

When I am using this module in my DB:

See how I am using this in my query:

I did not make any changes to the module.

 

[arnelgp]

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.

 

[Khurramshaikh47]

here Mr.Khurram, check and test the function again.

I am copying the module from the demo DB from this post.