4 Replies Latest reply on Jan 8, 2015 11:58 PM by philmodjunk

    Complex (at least for me) Time Clock Timesheet (Union Rules)

    RyanYoung

      Title

      Complex (at least for me) Time Clock Timesheet (Union Rules)

      Post

      Hello world!

       

      I'm trying to create a work sheet that performs many functions. This is for a union job so I have to calculate all these "nonsense" rules. The couple I'm having most trouble with are the following:

             
      • Night Premium 1: Time worked between 8:00 PM and 1:00 AM are paid +10% scale
      •      
      • Night Premium 2: Time worked between 1:00 AM and 6:00 AM are paid +20% scale

      I have 5 sets of "Time In" and "Time Out" depending on the type of day I have. However, I need to calculate how much time is worked between those two "Night Premium" ranges so that I can calculate the appropriate payment.

       

      I'm also having trouble with the rollover into the next day. I can upload the entire file if someone would be so kind to dig deeply into it. I can explain all of the other functions as well.

       

      Many thanks!

       

      -Ryan

      ryan at soundforfilmandtv dot com

      Screen_Shot_2015-01-06_at_5.13.52_PM.png

        • 1. Re: Complex (at least for me) Time Clock Timesheet (Union Rules)
          bigtom

          I did a time clock for my office.It can be done a few ways. If you supply a file I will take a look and get you some ideas.

          It can depend on how breaks are tracked if at all. Are the actual workers clocking in or is this entered by a foreman/site manager?

          In the simplest sense you would base everything off of the initial clock in time for the shift. That makes getting the rest pretty easy depending on if your minimum pay increment is 1 hour .5 hour or .25 hour. It looks like Scale will have a reference to the actual pay per hour. Many unions pay a four hour minimum and you need to factor that in depending on what hour they started as well. Do you pay 1.5xOT+10% for a shift over certain hours that runs into the premium hours?

          Once you have all the small pieces it is just some algebra getting it all together.

          • 2. Re: Complex (at least for me) Time Clock Timesheet (Union Rules)
            RyanYoung

            Hi Tom,

            Thank you so much for your response and thank you so much for your generosity. I'm new to FileMaker Pro and have had troubles trying to do this with Excel in the past. So far (with my 1 day of experimenting) this is becoming a MUCH better solution for me. I'll answer your questions and will post the file online so you can see my attempts.

            The minimum pay increment is every 6 minutes (.1 hours). I created a drop-down menu with pre-set times rather than rounding. The pre-set also assures that the calculation will work correctly.

            Scale references a hidden "Rate" field. This changes depending on which "Scale" is selected. However, rates increase every year so I should find a better way to put the rate in.

            You're absolutely correct about the minimums! My union has a 4-hour minimum as well as a 9-hour minimum.

            The 10% and 20% night premiums do, indeed, apply additionally only to the 1.5xOT

            I appreciate every bit of help and advice you may give. I'm not expecting you (or anybody) to do a complete job for me (and for free) but I will be thorough in what I'm trying to achieve, and learn.

            Best,

            -Ryan

            http://www.soundforfilmandtv.com/fmp/WB%20Time%20Card.fmp12

            (Clicking the above link WILL automatically download the file)

            Here are the rules I want to include in my form:

            (YEESH!)

             

            Scales:

                   
            • Y-1 = $x.xx
            •      
            • Y-9 = $y.yy

             

            4-hour:

                   
            • 0-4 hours worked = 6 hours pay
            •      
            • 4-12 hours worked = 6 hours pay + 1.5x hours worked between hour 4-12
            •      
            • 12-15 hours worked = 6 hours pay + 1.5x hours worked between hour 4-12 + 2.0x hours worked over hour 12+

             

            9-hour:

                   
            • 0-9 hours worked = 9 hours pay
            •      
            • 9-12 hours worked = 9 hours pay + 1.5x hours worked between hour 9-12
            •      
            • 12-15 hours worked = 9 hours pay + 1.5x hours worked between hour 9-12 + 2.0x hours worked over hour 12+

             

            Golden Time (2.0x):

                   
            • 2.0x starts after 12 elapsed hours from "Time In #1" even if there's time off during lunch

             

            Weekly OT:

                   
            • 1.5x hours worked over hour 40 in the week

             

            6th day (1.5x):

                   
            • 4-hour : 0-12 hours worked = 1.5x 6 hours pay
            •      
            • 4-hour : 12+ hours worked = 1.5x 6 hours pay + 3.0x hours worked over hour 12+
            •      
            • 9-hour : 0-12 hours worked = 1.5x 9 hours pay
            •      
            • 9-hour : 12+ hours worked = 1.5x 9 hours pay + 3.0x hours worked over hour 12+

             

            7th day or holiday (2.0x):

                   
            • 4-hour : 0-12 hours worked = 2.0x 6 hours pay
            •      
            • 4-hour : 12+ hours worked = 2.0x 6 hours pay + 4.0x hours worked over hour 12+
            •      
            • 9-hour : 0-12 hours worked = 2.0x 9 hours pay
            •      
            • 9-hour : 12+ hours worked = 2.0x 9 hours pay + 4.0x hours worked over hour 12+

             

            Meal penalty:

                   
            • First ½ hour meal delay or fraction thereof = $8.50
            •      
            • Second ½ hour meal delay or fraction thereof = $11.00
            •      
            • Third and each succeeding ½ hour meal delay or fraction thereof = $13.50

             

            Night premium:

                   
            • 8:00 pm - 1:00 am = Current rate + 10% (in addition to only to the 1.5xOT)
            •      
            • 1:00 am - 6:00 am = Current rate + 20% (in addition to only to the 1.5xOT)

             

            Turnaround:

                   
            • Starting 0-4 hours after latest Time Out = Paid during these hours, continue at whatever scale was paid last (even if at Golden Time)
            •      
            • Starting 4-8 hours after latest Time Out = Continue at whatever scale was paid last (even if at Golden Time)
            • 3. Re: Complex (at least for me) Time Clock Timesheet (Union Rules)
              RyanYoung

              Coming up with some ideas. This is pretty fun! Here's a look at some nonsense that I'm coming up with so far.

              • 4. Re: Complex (at least for me) Time Clock Timesheet (Union Rules)
                philmodjunk

                When computing elapsed time between time in and time out where the time in may precede Midnight and the time out may come after midnight. Use timestamp fields instead of time fields.

                TimeStampOut - TimeStampIn will compute the elapsed time in seconds without any need for special handling to handle intervals that span midnight like you would with two time fields.

                And the timestamp values can be created with the timestamp function that combines separate date and time values.