1 2 Previous Next 19 Replies Latest reply on Jan 6, 2017 10:42 AM by laura@solutionmakers.com

# Overtime by day/week

Hello all:

I've reviewed the posts for overtime calcs but none seemed to match the same requirement I have.

Customer is using FM 13.

They have a schedule database that has unique records by Staff and Week of Year. Overtime needs to be calculated both at the daily level and the weekly level. Anytime over 9 hours in a day is 1.5  overtime. Easy. However, if the person has over 40 hours REGULAR time in a week, they get paid 1.5 time for that too. Here's an example:

Monday           9.75 hours

Tuesday          9.75 hours

Wednesday     9.75 hours

Thursday         9.75 hours

Friday              9.75 hours

-------

Total Week:  48.75

So, using the 9 hour rule:

Mon         9 Reg  and 0.75 OT

Tue          9 Reg and  0.75 OT

Wed         9 Reg and 0.75 OT

Thu          9 Reg  and 0.75 OT

at this point the staff person has received 36 of regular time, and 3 hours of OT.

After 40 hours of REGULAR TIME, he/she will get more OT so

Fri            4  Reg   and 5.75 OT

Not sure why my brain is struggling, but not sure how best to calc the weekly OT. Ideas? I have a self join by staff and week of year. The daily OT calcs are easy until I get to the weekly OT.

Thanks in advance for suggestions!

Laura

• ###### 1. Re: Overtime by day/week

laura@solutionmakers.com wrote:

I've reviewed the posts for overtime calcs but none seemed to match the same requirement I have.

They have a schedule database that has unique records by Staff and Week of Year. Overtime needs to be calculated both at the daily level and the weekly level. Anytime over 9 hours in a day is 1.5 overtime. Easy. However, if the person has over 40 hours REGULAR time in a week, they get paid 1.5 time for that too. Here's an example:

[...]

Not sure why my brain is struggling, but not sure how best to calc the weekly OT. Ideas? I have a self join by staff and week of year. The daily OT calcs are easy until I get to the weekly OT.

Try

Let ( [

weekSum = Sum ( TimeCard__byStaffAndWeek::hrs ) ;

OT = Max ( weekSum - 40 ; 0 ) ;

RT = weekSum - OT

] ;

RT + OT * 1.5

)

Funnily, someone had the exact same issue just recently - though not within the context of overtime, which must be why you didn't find it.

EDIT: so as it happened, I was being told that this is a great calc, except that it doesn't take into account the OT already deducted per day. (Actually, only the second part. )

Laura, according to your statement, after 40 RT, they get paid extra for any OT, too - but not in addition to the daily overtime. Which means that the count of WOT begins the day that RT has reached 40, but at the same day, DOT stops. Correct?

• ###### 2. Re: Overtime by day/week

Hi,

how are you storing the hourly data?  I would suspect something like a table with one record per employee per shift with start and stop timestamps.

• ###### 3. Re: Overtime by day/week

Thanks for your suggestion. I wish it were that easy of just over 40 hours, but it's not. Please re-read my post and let me know if you have a way to make that work.

Thanks!

Laura Bowyer

~~~~~~~~~~~~~~~~~~

Sent from my iPhone- please excuse any typos!

• ###### 4. Re: Overtime by day/week

Yes, exactly. Based on a simple time card entry of start and stop times.

Thanks!

Laura Bowyer

~~~~~~~~~~~~~~~~~~

Sent from my iPhone- please excuse any typos!

• ###### 5. Re: Overtime by day/week

laura@solutionmakers.com wrote:

I wish it were that easy of just over 40 hours, but it's not.

A fine solution-maker you are ...

Please read the edit of my original post.

• ###### 6. Re: Overtime by day/week

As Columbo might say, "one more thing ..."

How is one week delineated from the next?  I don't know if it's an issue, but if your week started at midnight on Saturday night, and an employee worked until 2:00 am, I'd assume that that shift straddling two week periods would need to be evaluated to separate last week from this week.  Yes?

• ###### 7. Re: Overtime by day/week

One way to do it by calculation:

* create a self-join for the TimeCard table where

staffID = staffID, weekOfYear = weekOfYear and date <= date

* calculate from the record data ...

daily OT if Sum ( SelfJoin::hrs ) <= 40, else weekly OT

* since both types are mutually exclusive, you can use a single field

Then your result is simply Sum ( RT ) + Sum ( OT ) * 1.5

• ###### 8. Re: Overtime by day/week

To elaborate on the previous suggestion:

Create the self-join with a TimeSheet__sameWeekButOlder TO as

TimeSheet::staffID = TimeSheet__sameWeekButOlder::staffID

TimeSheet::cWeekOfYear = TimeSheet__sameWeekButOlder::cWeekOfYear

TimeSheet::theDate < TimeSheet__sameWeekButOlder::theDate

RT =

Let ( [

RTuntilToday = Sum ( TimeSheet__sameWeekButOlder::RT ) ;

includingToday = RTuntilToday + hrs

] ;

Case (

includingToday <= 40 ;

hrs - Max ( hrs - 9 ; 0 ) ;

40 - RTuntilToday

)

)

OT =

Let ( [

RTuntilToday = Sum ( TimeSheet__sameWeekButOlder::RT ) ;

includingToday = RTuntilToday + hrs

] ;

Case (

includingToday <= 40 ;

Max ( hrs - 9 ; 0 ) ;

includingToday - 40

)

)

1 of 1 people found this helpful
• ###### 9. Re: Overtime by day/week

Yes, that is correct.

Thank you!

Laura

• ###### 10. Re: Overtime by day/week

That looks like it will do it - thanks so much!!!

Happy holidays,

Laura

• ###### 11. Re: Overtime by day/week

laura@solutionmakers.com wrote:

thanks so much!!!

Happy holidays

You're welcome, and the same to you.

Note that this …

laura@solutionmakers.com wrote:

TimeSheet::theDate < TimeSheet__sameWeekButOlder::theDate

… must be the other way around (which you would have found out anyway):

TimeSheet::theDate > TimeSheet__sameWeekButOlder::theDate

It was correct in my test solution from which I generated the screenshot, but I mistyped it here.

• ###### 12. Re: Overtime by day/week

Thanks again!!

Laura

• ###### 13. Re: Overtime by day/week

Hello:

Thanks for your assistance previously! The Let statements worked perfectly for the attendants. Now I am trying to figure out a different overtime and I tried modifying your statements but can’t seem to get them to work.

If the staff person is a certain type (LVN or RN), then the daily overtime starts after 8 regular hours in one day or after 40 regular hours/week – double time after 12 hours in one day. How would I modify your Let statements for that calc?

Thanks for your assistance!

Laura

• ###### 14. Re: Overtime by day/week

Where the original expression has constants for total hours per day and overtime rates, etc. Put in references to fields from a table of "types". One record for each type. Put the specific values needed into fields of these records and use a "type" field in the employee table to reference the correct set of values to use in the overtime calculations.

1 2 Previous Next