Solved I need guidance to calculate Duty hours and Overtime hours

I tried by creating a new simple DB and importing your module in it. And then tried the fnComputeTime function. Please See the Screenshot
arnelgp said:
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.
Click to expand...
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.
 
you comment out this Second If:

'If ID <> this_id Then
...
...
...
'End If
 
arnelgp said:
you comment out this Second If:

'If ID <> this_id Then
...
...
...
'End If
Click to expand...
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
 
arnelgp said:
you comment out this Second If:

'If ID <> this_id Then
...
...
...
'End If
Click to expand...
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).
 
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
 
arnelgp said:
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
Click to expand...
Thank you so much, Mr. Arnel :giggle:
 
You must log in or register to reply here.

Similar threads

S
Hours
Replies
5
Views
351
June7
June7
D
Table Search and Total
Replies
18
Views
363
DDJKSA
D
K
Solved I need help converting overtime hours to the round figure.
Replies
6
Views
616
Khurramshaikh47
K
S
ChildCarePlan
Replies
14
Views
1,087
silentwolf
S
M
Crosstab report with calculated label to show each day of month as label and working hours depending on employee shifts
Replies
2
Views
895
Gasman
Gasman

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom