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

Khurramshaikh47

Member
I am working on the attendance table to get Overtime and Duty hours.

Conditions:
1. Duty hours are 9:00:00 AM to 6:00:00 PM & Grace Time is 15 Minutes (If an employee arrives after 9:15, the number of minutes he is late will be deducted from his 9 hours duty hours).
2. Overtime will be started after 6:00:00 PM when he worked till 12 AM he will get 8 hours instead of 6 hours as a night benefit. Any additional hours he works after 12 midnight will be added to his 8 hours. Note that late arrivals in the morning should not affect overtime. It will be deducted in duty hours.
I need guidance to create 2 Columns:

1.2 DutyHours (in which 9 duty hours will be calculated, by deducting late arrival and early departures)
2.2 OTHours (in which overtimes will be calculated if the employee is working after 6:00:00 PM)

I have 2 columns to input employee arrival and departure times:
• Time-In (Data Type is Date/Time)
• Time-Out (Data Type is Date/Time)

Waiting for experts’ replies.

CJ_London

Super Moderator
Staff member
Does 15min grace time apply to late arrival and early departure or shared between the two?

so someone who arrives at 9:15 and leaves at 5:45 gets 9 hours? Or 8:45?

and anything happen around lunchtime? Or can employee have a 3 hr lunch?

Khurramshaikh47

Member
Does 15min grace time apply to late arrival and early departure or shared between the two?

so someone who arrives at 9:15 and leaves at 5:45 gets 9 hours? Or 8:45?

and anything happen around lunchtime? Or can employee have a 3 hr lunch?
If someone arrives at 9:15 and leaves at 5:45, 15 minutes will be deducted from duty hours. nothing will happen around lunchtime.

arnelgp

..forever waiting... waiting for jellybean!
it would be simple if you just compute the hours a worker worked (normal/ot) from his timecard.
before entering it to the system.

CJ_London

Super Moderator
Staff member
and if someone arrives at 8:45 and leaves at 5:00? do they have 45 mins deducted? or 30 mins?

All these variations affect the way the calculation would be done

Khurramshaikh47

Member
and if someone arrives at 8:45 and leaves at 5:00? do they have 45 mins deducted? or 30 mins?

All these variations affect the way the calculation would be done
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.

Khurramshaikh47

Member
and if someone arrives at 8:45 and leaves at 5:00? do they have 45 mins deducted? or 30 mins?

All these variations affect the way the calculation would be done
If someone arrives at 9:16 AM 16 minutes will be deducted not 1 minute.

gemma-the-husky

Super Moderator
Staff member
Do you ever adjust the times manually.

I would be inclined to just store the start time and time, and then leave it to your query to evaluate the base hours worked, and o/t hours worked.
If necessary, I would have a function to do the work, as this is the sort of thing that might be awkward (but possible) to build directly into a query.

Depending on whether the times might ever change, eg due to a adjustment of recorded hours authorised by management, you may or may not decide to store the result of the time calculations. If management authorise the start time to be set to 9:00 for some special reason, that will adjust any stored values you have. If you don't store the working time value, you don't need to adjust it.

it's easier to express if starttime>9:00 and if finishtime<18:00 etc, than build it into an inline expression in a query. And you can add comments to a function to explain the process.

You might have part-timers who don't work 9.00 till 6:00, and might have other regular hours for some staff. You might want to evaluate weekend overtime. All sorts of things.

Finally, is there a "granularity" with the time. What if an employee is 20 minutes late. Does he lose 5 minutes pay (net of the 15 minutes allowance), or 15 minutes pay (ie 5 minutes rounded up to the next quarter hour).

Last edited:

CJ_London

Super Moderator
Staff member
using the immediate window - this calculation gives you duty hours in minutes
arrived early
?iif(datediff("n",#2022-08-20 08:45#,#2022-08-20 18:00#)<525,datediff("n",#2022-08-20 08:45#,#2022-08-20 18:00#),540)
540

arrived after grace period
?iif(datediff("n",#2022-08-20 09:20#,#2022-08-20 18:00#)<525,datediff("n",#2022-08-20 09:20#,#2022-08-20 18:00#),540)
520

arrived during grace period
?iif(datediff("n",#2022-08-20 09:10#,#2022-08-20 18:00#)<525,datediff("n",#2022-08-20 09:10#,#2022-08-20 18:00#),540)
540

for OT hours
finished before midnight
?iif(datediff("n",#2022-08-20 18:00#,#2022-08-20 23:45#)>0,datediff("n",#2022-08-20 18:00#,#2022-08-20 23:45#),0)+abs(120*(datediff("n",#2022-08-20 18:00#,#2022-08-20 23:45#)>0))
465

finished after midnight
?iif(datediff("n",#2022-08-20 18:00#,#2022-08-21 00:45#)>0,datediff("n",#2022-08-20 18:00#,#2022-08-21 00:45#),0)+abs(120*(datediff("n",#2022-08-20 18:00#,#2022-08-21 00:45#)>0))
525

You don't actually need to date element for the duty ouhrs because they are the same day, but you do for OT hours because you are into a new day

Yo haven't say how you want the result formatted, so I'll leave that up to you -

520 minutes can be expressed as 8.6666667 hours using 520/60
Or using format(520*60/86400,"hh:mm") will give you a text value of '08:40' which will be OK so long as the total time does not exceed 24 hours

Khurramshaikh47

Member
using the immediate window - this calculation gives you duty hours in minutes
arrived early
?iif(datediff("n",#2022-08-20 08:45#,#2022-08-20 18:00#)<525,datediff("n",#2022-08-20 08:45#,#2022-08-20 18:00#),540)
540

arrived after grace period
?iif(datediff("n",#2022-08-20 09:20#,#2022-08-20 18:00#)<525,datediff("n",#2022-08-20 09:20#,#2022-08-20 18:00#),540)
520

arrived during grace period
?iif(datediff("n",#2022-08-20 09:10#,#2022-08-20 18:00#)<525,datediff("n",#2022-08-20 09:10#,#2022-08-20 18:00#),540)
540

for OT hours
finished before midnight
?iif(datediff("n",#2022-08-20 18:00#,#2022-08-20 23:45#)>0,datediff("n",#2022-08-20 18:00#,#2022-08-20 23:45#),0)+abs(120*(datediff("n",#2022-08-20 18:00#,#2022-08-20 23:45#)>0))
465

finished after midnight
?iif(datediff("n",#2022-08-20 18:00#,#2022-08-21 00:45#)>0,datediff("n",#2022-08-20 18:00#,#2022-08-21 00:45#),0)+abs(120*(datediff("n",#2022-08-20 18:00#,#2022-08-21 00:45#)>0))
525

You don't actually need to date element for the duty ouhrs because they are the same day, but you do for OT hours because you are into a new day

Yo haven't say how you want the result formatted, so I'll leave that up to you -

520 minutes can be expressed as 8.6666667 hours using 520/60
Or using format(520*60/86400,"hh:mm") will give you a text value of '08:40' which will be OK so long as the total time does not exceed 24 hours
Thank you so much, Can you guide me on where should I use these codes? is it for expression builder? Where are the Time-In and Time-Out in these codes?

CJ_London

Super Moderator
Staff member
Can you guide me on where should I use these codes?
depends on how you are going to use them. they could go in a query, as a controlsource to a control or use in vba

time in/timeout are the ones that are not #2022-08-20 18:00#

time in times are used in the duty hours calculation
time out times in the OT hours calculation

Khurramshaikh47

Member
depends on how you are going to use them. they could go in a query, as a controlsource to a control or use in vba

time in/timeout are the ones that are not #2022-08-20 18:00#

time in times are used in the duty hours calculation
time out times in the OT hours calculation
I am really sorry to bother you again I am confused on some points. Can you please tell me:

1) arrived early
?iif(datediff("n",#Time_In#,#2022-08-20 18:00#)<525,datediff("n",#Time_In#,#2022-08-20 18:00#),540)
540
Is the above code correct?

2) I am using a query to calculate duty and OT hours can I use these codes in expression builder?

3) How can I combine these codes to put them in one place? Should I Copy and paste it as it is?

Please take a look on screenshot of my query below:

CJ_London

Super Moderator
Staff member
1) arrived early
?iif(datediff("n",#Time_In#,#2022-08-20 18:00#)<525,datediff("n",#Time_In#,#2022-08-20 18:00#),540)
No - should be in a new column

DutyTime: iif(datediff("n",[Time_In],#2022-08-20 18:00#)<525,datediff("n",[Time_In],#2022-08-20 18:00#),540)

2) I am using a query to calculate duty and OT hours can I use these codes in expression builder?
only by a mixture of typing and selecting
3) How can I combine these codes to put them in one place? Should I Copy and paste it as it is?
can do, then change as per above

Khurramshaikh47

Member
No - should be in a new column

DutyTime: iif(datediff("n",[Time_In],#2022-08-20 18:00#)<525,datediff("n",[Time_In],#2022-08-20 18:00#),540)

only by a mixture of typing and selecting

can do, then change as per above
Thank you so much let me try it.

some demo.

Attachments

• timecard.accdb
760 KB · Views: 163

Khurramshaikh47

Member
No - should be in a new column

DutyTime: iif(datediff("n",[Time_In],#2022-08-20 18:00#)<525,datediff("n",[Time_In],#2022-08-20 18:00#),540)

only by a mixture of typing and selecting

can do, then change as per above
I am using the following code for DutyHours:
Code:
``DutyHours: iif(datediff("n",[Time_In],#2022-08-20 18:00#)<525,datediff("n",[Time_In],#2022-08-20 18:00#),540,iif(datediff("n",[Time_In],#2022-08-20 18:00#)<525,datediff("n",[Time_In],#2022-08-20 18:00#),540,iif(datediff("n",[Time_In],#2022-08-20 18:00#)<525,datediff("n",[Time_In],#2022-08-20 18:00#),540)))``

But query is not running

arnelgp

..forever waiting... waiting for jellybean!
i am saving the calculation in the table.
see Form2 form and TimeSheet2 table.

Attachments

• timecard.accdb
1 MB · Views: 143

arnelgp

..forever waiting... waiting for jellybean!
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")

Last edited:

Khurramshaikh47

Member
i am saving the calculation in the table.
see Form2 form and TimeSheet2 table.
Thank you, dear,

It’s overall working fine but

• I want 9:00 AM to 6:00 PM = 9 hours instead of 8 hours.
• After 6 Am on night regular hours and overtime hours are converting to negative value while I want 16 hours as overtime till 8:00 AM on the night.
• Can I use this in my own query? I am already running a database. I have a lot of data there in my DB. I cannot start working on another DB. Can you please guide me on how to add these to my own query.

Replies
5
Views
257
Replies
6
Views
558
Replies
14
Views
994
Replies
2
Views
787
Replies
3
Views
701