Staff overtime calculations

[Brainiac58]

Hello all-

I'm trying to come up with a calculation that will calculate overtime hours worked in a scheduling database. We pay a staff person overtime if tthey work more than 8 hours in a 24 hour day and we pay overtime after a staff has worked more than 80 hours in a 2 week pay period.

The calculation I came up with to calculate overtime hours worked for a Friday of week 1 (Staff1Wk1FriOt) of the pay period is:

Case (Staff1Wk1FriSum ≥ 80; Staff1Wk1FriOt = Staff1Wk1FriHrs; Staff1Wk1FriHrs≤ 8 ; "0"; Staff1Wk1FriHrs > 8;Staff1Wk1FriHrs-8)

Where Staff1Wk1FriSum = the total hours worked for days Monday through Friday. Staff1Wk1FriHrs is a calculation that uses Friday's start time and end time to calculate the hours worked.

When i tried this calculation it gave an answer of "0" for overtime hours worked even after I set the Monday-Friday hours total beyond 80 hours.

Any suggestions?

Thanks.

[Anuviel]

Not sure but do you store your calculated hours of work as a number or as a time? If storing everything as time make sure that the overtime calculation returns time as a result and not a number.

You might want to clarify what each field does in your calculation so it might be easier for me and others to check it out.

[comment]

I can't tell what the calculation is supposed to do, but it seems the syntax is incorrect. I think you wanted to say:

Case (Staff1Wk1FriSum ≥ 80; Staff1Wk1FriHrs; Staff1Wk1FriHrs≤ 8 ; 0; Staff1Wk1FriHrs > 8;Staff1Wk1FriHrs-8)

[Anuviel]

Here is one way it might be done in:

Time_In (Start time)

Time_Out (End Time)

Total_Day_Time (Calculates total time worked in a day using the above, might double up the field to account for break time)

Total_Week_Time (sums Total_Day_Time)

Question: Do you pay overtime if someone worked over 8 hours in a day but the total work time in a two week period is less then 80 or does it have to be more then 80 in order for you to pay overtime regardless if someone worked 9 or 10 hours in a day?

Depending on the answer to the above question above the calculation will be different. Here is one example for 40 hrs:

Case (Total_Week_Time > 40 ; Total_Week_Time - 40 ; 0)

Basically if Total_Week _Time is over 40 it will deduct 40 from itself giving you the extra hours if it is not over 40 it will return 0 as there is no overtime.

[Brainiac58]

Our organization pays overtime if someone works more than 8 hours in a day and we also pay overtime if someone works more than 80 hours in a 2 week pay period.

I tried this formula but the logic is not right. It will calculate the number of hours beyond 80 (which would be overtime) but it does not take into account the scenario where someone worked more than 8 hours on the same day they exceeded 80 hours for the pay period.

Case (Staff1Wk1FriSum ≥ 80; Staff1Wk1FriSum-80; Staff1Wk1FriHrs≤ 8 ; "0"; Staff1Wk1FriHrs > 8; Staff1Wk1FriHrs-8)

[Anuviel]

Thanks., but I do not get it - still.

If someone works over 8 hours a day then you pay them for overtime.

If someone works over 80 hours in a two week period you pay overtime as well. Would it not stand that they already got paid for their overtime since you pay based on a day if it was over 8 hours, then your 80 hours two week period does not make sense. Let me put it this way:

Worked 41 hour in a week = he will get paid for 1 hour of overtime

worked 81 hours in a two week period = he will get paid for 1 hour of overtime

In both cases the person gets paid 1 hour of overtime.

For overtime calculation, I think, there is no point in showing both 80 hours against 40 hours - just calculate it daily and add daily overtime.

If you want to show the overtime in a week and two week period set up a separate calculation.

Also do you keep one day as a record or is it one week as a record with multiple fields for each day?

Our organization pays overtime if someone works more than 8 hours in a day and we also pay overtime if someone works more than 80 hours in a 2 week pay period.

I get this as: I will be paid overtime if I worked more then 8 hours in a day and I will be paid overtime again if the total comes to 81 hours in a two week period. So basically someone who works 1 hour overtime and works all the rest days full 8 hours a day will get paid double overtime?