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

[Khurramshaikh47]

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.

Thank you in advance.

 

[CJ_London]

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]

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]

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]

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]

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]

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]

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

 

[CJ_London]

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]

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]

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]

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]

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]

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.

 

[arnelgp]

some demo.

 

[Khurramshaikh47]

some demo.

Thank you arnelgp

 

[Khurramshaikh47]

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:

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]

i am saving the calculation in the table.
see Form2 form and TimeSheet2 table.

 

[arnelgp]

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")

 

[Khurramshaikh47]

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.