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

Local time
Today, 23:14
Joined
Aug 19, 2021
Messages
212
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.
Yes BTID is unique. It can not be duplicated.
 
Local time
Today, 23:14
Joined
Aug 19, 2021
Messages
212
I tried by creating a new simple DB and importing your module in it. And then tried the fnComputeTime function. Please See the Screenshot
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.
1661236171160.png

When this function is applied, it returns the perfect output with the current (Time In & Time Out) values once. Then when I am changing TimeIn and TimeOut the output is not changing. Please See the Screenshot
1661236462159.png


Then I copied all the components of my existing database like tables, queries, forms, and reports to your given dummy database. And then tried this (fnComputeTime) function.
1661238107571.png


Result is same:
1661238161133.png


Hope for your response.

Thank you again for your precious time.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 02:14
Joined
May 7, 2009
Messages
19,246
you comment out this Second If:

'If ID <> this_id Then
...
...
...
'End If
 
Local time
Today, 23:14
Joined
Aug 19, 2021
Messages
212
you comment out this Second If:

'If ID <> this_id Then
...
...
...
'End If
Commented Out
1661241078565.png

An Error occurred when I am trying to use this function in query now:
1661241396018.png

Error In VB when compiling:
1661241550804.png

Please check the code:

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, tfOT As Boolean
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:15:00 AM# Then
'        t1 = #9:00:00 AM#
'    End If
    If (t2 >= #12:00:00 AM# And t2 <= #8:59:59 AM#) Or _
        (t2 >= #5:45:00 PM#) Then
        t2 = #6:00:00 PM#
    End If
    tm = DateDiff("n", t1, t2)
    tm = tm / 60
    regular_time = Round(tm, 2)
    ' compute overtime
    t2 = t_out
    tm = 0
    tfOT = True
    Select Case True
    Case (t2 = #12:00:00 AM#)
        t1 = #1:00:00 PM#
        t2 = #9:00:00 PM#
    Case (t2 > #12:00:00 AM# And t2 <= #8:59:59 AM#)
        tm = 480
        t1 = #12:00:00 AM#
    Case (t2 >= #5:45:00 PM# And t2 <= #6:30:00 PM#)
        tfOT = False
    Case (t2 >= #6:00:00 PM# And t2 <= #11:59:59 PM#)
        t1 = #6:00:00 PM#
    Case Else
        tfOT = False
    End Select
    If tfOT Then
        tm = tm + DateDiff("n", t1, t2)
        tm = tm / 60
    End If
    over_time = Round(tm, 2)
End If
fnComputeTime = IIf(ReturnWhat = "reg", regular_time, over_time)
End Function
 
Local time
Today, 23:14
Joined
Aug 19, 2021
Messages
212
you comment out this Second If:

'If ID <> this_id Then
...
...
...
'End If
I commented
If and End If and leave the content between them.
1661245467163.png

And Deleted the last End If before over_time = Round(tm, 2)
Its working now.

1661245597399.png


Thank Yo So much to all specially Mr. Arnel (A greate Teacher).
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 02:14
Joined
May 7, 2009
Messages
19,246
i mean this code:
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, tfOT As Boolean
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:15:00 AM# Then
        t1 = #9:00:00 AM#
    End If
    If (t2 >= #12:00:00 AM# And t2 <= #8:59:59 AM#) Or _
        (t2 >= #5:45:00 PM#) Then
        t2 = #6:00:00 PM#
    End If
    tm = DateDiff("n", t1, t2)
    tm = tm / 60
    regular_time = Round(tm, 2)
    ' compute overtime
    t2 = t_out
    tm = 0
    tfOT = True
    Select Case True
    Case (t2 = #12:00:00 AM#)
        t1 = #1:00:00 PM#
        t2 = #9:00:00 PM#
    Case (t2 > #12:00:00 AM# And t2 <= #8:59:59 AM#)
        tm = 480
        t1 = #12:00:00 AM#
    Case (t2 >= #5:45:00 PM# And t2 <= #6:30:00 PM#)
        tfOT = False
    Case (t2 >= #6:00:00 PM# And t2 <= #11:59:59 PM#)
        t1 = #6:00:00 PM#
    Case Else
        tfOT = False
    End Select
    If tfOT Then
        tm = tm + DateDiff("n", t1, t2)
        tm = tm / 60
    End If
    over_time = Round(tm, 2)
'End If
fnComputeTime = IIf(ReturnWhat = "reg", regular_time, over_time)
End Function
 
Local time
Today, 23:14
Joined
Aug 19, 2021
Messages
212
i mean this code:
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, tfOT As Boolean
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:15:00 AM# Then
        t1 = #9:00:00 AM#
    End If
    If (t2 >= #12:00:00 AM# And t2 <= #8:59:59 AM#) Or _
        (t2 >= #5:45:00 PM#) Then
        t2 = #6:00:00 PM#
    End If
    tm = DateDiff("n", t1, t2)
    tm = tm / 60
    regular_time = Round(tm, 2)
    ' compute overtime
    t2 = t_out
    tm = 0
    tfOT = True
    Select Case True
    Case (t2 = #12:00:00 AM#)
        t1 = #1:00:00 PM#
        t2 = #9:00:00 PM#
    Case (t2 > #12:00:00 AM# And t2 <= #8:59:59 AM#)
        tm = 480
        t1 = #12:00:00 AM#
    Case (t2 >= #5:45:00 PM# And t2 <= #6:30:00 PM#)
        tfOT = False
    Case (t2 >= #6:00:00 PM# And t2 <= #11:59:59 PM#)
        t1 = #6:00:00 PM#
    Case Else
        tfOT = False
    End Select
    If tfOT Then
        tm = tm + DateDiff("n", t1, t2)
        tm = tm / 60
    End If
    over_time = Round(tm, 2)
'End If
fnComputeTime = IIf(ReturnWhat = "reg", regular_time, over_time)
End Function
Thank you so much, Mr. Arnel :giggle:
 

Users who are viewing this thread

Top Bottom