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

    Overtime by day/week

    laura@solutionmakers.com

      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
          erolst

          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
            David Moyer

            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
              laura@solutionmakers.com

              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
                laura@solutionmakers.com

                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
                  erolst

                  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
                    David Moyer

                    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
                      erolst

                      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
                        erolst

                        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

                          )

                        )

                         

                        Screen Shot 2016-12-22 at 11.14.33.png

                        1 of 1 people found this helpful
                        • 9. Re: Overtime by day/week
                          laura@solutionmakers.com

                          Yes, that is correct.

                           

                          Thank you!

                          Laura

                          • 10. Re: Overtime by day/week
                            laura@solutionmakers.com

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

                             

                            Happy holidays,

                            Laura

                            • 11. Re: Overtime by day/week
                              erolst

                              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
                                laura@solutionmakers.com

                                Thanks again!!

                                 

                                 

                                 

                                Laura

                                • 13. Re: Overtime by day/week
                                  laura@solutionmakers.com

                                  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
                                    philmodjunk

                                    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