# SolvedI need guidance to calculate Duty hours and Overtime hours (1 Viewer)

see the function in post #19 and change this:

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

to:

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

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.

I want 9:00 AM to 6:00 PM = 9 hours
Standard work hrs is 8 hrs only (9 minus 1 hr break)

The functiin i made only compute until 6 am. You need to modify it to include upto 8am.
I have applied the module now.

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

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:

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``````

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

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.

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)

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

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

Thank you very much for your help.

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

#### Attachments

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?

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.

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.

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.

here Mr.Khurram, check and test the function again.
I am copying the module from the demo DB from this post.

Replies
5
Views
309
Replies
6
Views
590
Replies
14
Views
1,039
Replies
2
Views
839
Replies
3
Views
789