Solved I need help converting overtime hours to the round figure. (1 Viewer)

[Khurramshaikh47]

Hi,
I have an attendance table. There are some fields:
1) Time_In
2) Time_Out
3) Overtime

The field overtime calculates the overtime hours after completing 9 working hours.
I need to restrict the overtime hours value:

• If the Overtime hour is less than 1.00 then convert it to 0.00.
• After the first 1 hour convert the overtime to round down to 0.50.
• 0.00 and negative values will remain the same.

Example:
In the screenshot below the Overtime column is the column that is currently in use while Overtime New column is my desired column.

Thank you in advance.

 

[arnelgp]

i made a function in Module1 and called it in Query1.
see query1.

the only problem is OT=0.78, the new OT should be 0.50
based on your rule (which can be seen in OT=2.75 also).

 

[Khurramshaikh47]

i made a function in Module1 and called it in Query1.
see query1.

the only problem is OT=0.78, the new OT should be 0.50
based on your rule (which can be seen in OT=2.75 also).

Actually, the rule for the first 1 hour is different. A full hour of work is required to earn the first hour of overtime.

 

[arnelgp]

so for OT below 1 hour the New OT = 0?

 

[CJ_London]

is this example data? your dates are for October 2023

and why is the record for 7-10-23 showing 1 hour new overtime when the actual overtime is 0.78 and the rule is 'f the Overtime hour is less than 1.00 then convert it to 0.00.'

 

[Khurramshaikh47]

is this example data? your dates are for October 2023

and why is the record for 7-10-23 showing 1 hour new overtime when the actual overtime is 0.78 and the rule is 'f the Overtime hour is less than 1.00 then convert it to 0.00.'

Ahh, It's a mistake in my example sheet.

 

[Khurramshaikh47]

so for OT below 1 hour the New OT = 0?

Thank you so much arnel.