How would you compute the total week's billing amount for this person?

I had had a confusing time with this for a client recently, so I was just wondering if there are multiple ways to compute OT given the rules below.

Billing Rates:
Normal Billing (non-OT): $29
OT and Holiday Billing rate: $43.50.
Normal work week: 40 Hours. (OT beyond that).

What would you calculate for this person's total week invoice numbers?

Person worked 46.5 hours
Holiday Hours worked: 12
(all hours worked in the same week)

$_________ ????

--

A. $2,138.75, or
B. $1,964.75,

(I'll share what I got.)

That's entirely dependent on jurisdictional rules. Please give us more.

For example… in the jurisdiction where I live, holiday pay is paid regardless if you work over 40 hours or not in a given week. Overtime start the first second you work more than 40 hours in a given week and is paid at 1.5 time regular pay. A holiday that coincides with overtime work is payed twice… therefore 2 time regular pay. Some collective agreements increase this to 2x and 3x pay.

3 Likes

Question:
Did the person work 34.5 regular hours and 12 holiday hours, or did they work 40 regular hours, 6.5 OT hours, and 12 holiday hours?

2 Likes

Good question!
In this week...
The person worked 40 regular hours + 6.5 OT.
Plus
12 Holiday Hours.

I script it this way....

I have a timelines table that calculates hours worked for each job.
A person can work on multiple jobs per day.
Each clock-in / clock-out constitutes one timeline.

I have an auto-calc field called "StaffYearWeek" that concatenates the workerID, the Week of the Year AND the Year (based on the Work Date)

When the clock out occurs and the relevant fields are set in the timeline, I GTRR - Timelines >> Timelines_ID StaffYearWeek (self join) where the relationship is SORTED by:

  1. ID_StaffYearWeek
  2. Work Date
  3. Time In

The sort takes care of a timeline that may not have been entered in chronological order.

Once in this TO, a field called Hours_this_Timecard has already been calculated on each of the timelines: Time Out - Time In. In the first record, I set a number field called Hours_This_Week which will be Hours_this_Timecard for the first record.

Go to 1st Record
Set Variable $hours_this_week = Hours_this_Timecard

Loop
Go to Record Next (exit after last ON)
Set Field Hours_this_Week = $hours_this_week + Hours_this_Timecard
Set Variable $hours_this_week = Hours_this_Week
End Loop

I also have a Hours_Regular_Time field and an Hours_Overtime field.

Hours Regular is this auto-calc:

Let ( [

UntilToday =

T15_TIMELINES::Hours_This_Week - T15_TIMELINES::Hours_This_Timecard ;

ThisCard = T15_TIMELINES::Hours_This_Timecard ;

Now = T15_TIMELINES::Hours_This_Week

] ;

Case ( UntilToday >= 40; 0;

Now > 40; 40 - UntilToday ;

ThisCard ))

This is Hours Overtime:
Let ( [

UntilToday =

T15_TIMELINES::Hours_This_Week - T15_TIMELINES::Hours_This_Timecard ;

ThisCard = T15_TIMELINES::Hours_This_Timecard ;

Now = T15_TIMELINES::Hours_This_Week

] ;

Case ( UntilToday >= 40; ThisCard;

Now > 40; Now - 40 ))

I'll have to think about Holiday Hours. Somehow there needs to be a way to designate a day as a Holiday that will trigger the pay rate to be 1.5 the regular rate.

2 Likes

Sorry, I wasn't looking for code. The code is already done (and a lot more complicated than this -- accounts for holidays, etc.). I was really only asking if there is a "standard" way to compute the billing amount.

Does your code get A. or B. above?

Thanks!

It would get answer B.

@LowSlope Welcome to the Soup! Glad to see you posting here.

1 Like

The client gets A., which surprised me, too.

The difference is that the client includes holiday time as OT also. When we discussed it, I understood how he got the value and now my code does too. But I still wondered if this was an arbitrary rule to get more $ from the existing hours...

Thus, my posting above to get input from others here.

I'm guessing it really just gets down to what's in the contract, but I still wondered if there was a computing standard for these kinds of OT + Holiday calculations.

Thanks so much for all your great replies!

I can easily get answer B but my answer A is different to your answer A

How do you get that figure? I can get it with this formula

12 * 43.5 + 6.5 * 43.5 + 46 * 29 = 2138.75

BUT that is missing half an hour from the time worked.

Here is my breakdown

hoursWorked = 46.5
holidayHoursWorked = 12

payRate = 29
OTRate = 43.5
holidayRate = OTRate
stdHours = 40

OTHoursWorked = if ( hoursWorked > stdHours ; hoursWorked - stdHours ; 0 )
stdHoursWorked = if( hoursWorked > stdHours ; stdHours ; hoursWorked  )

/* OTHours get paid overtime rate + standard Rate  */
holidayHoursWorked * holidayRate + OTHoursWorked * OTRate  +  hoursWorked * payRate  = 2,153.25

/* OTHours get paid overtime rate only  */
holidayHoursWorked * holidayRate + OTHoursWorked * OTRate  +  stdHoursWorked * payRate  = 1,964.75

Hi @LowSlope , welcome to Thesoup !

Hi Malcolm,

Thanks for your reply.

According to the final result, we computed it like this:

Thanks again.

In my business, we would calculate and bill it this way.

image

The difference between our results - $2,138.25 and $1,964.75 is $174.00.

In this particular example, your calculation appears to be billing the holiday hours at $58.00, which is $43.50 + $14.50. ($29.00 + $14.50 + $14.50)

12 * $58.00 = $696.00
12 * $43.50 = $522.00 (as shown above)

$696.00 - $522.00 = $174.00

That's how it appears - $174.00 is evenly divided by
$43.50 (4)
$29.00 (6)
and
12 hours ($14.5)

Thanks for the welcomes @steve_ssh and @planteg !

1 Like

I understand and I'm not saying/implying anything is wrong with your interpretation. In fact, I got the same result and that was the entire reason for my posting. :slight_smile:

For straight overtime calculations (minus holidays) your logic is how mine works also. The difference is how holiday time gets folded in. Again, this is just some business rule I was trying to see if commonly used.

Laplace Transforms are more straightforward than this other company's logic!
Thanks again.