7 Replies Latest reply on Nov 19, 2016 9:06 PM by taylorsharpe

    Calculating RT/OT/DT on weekly time


      I am struggling a time card system and it is such a common need I assume someone has dealt with it before me.  This client want time card hour parsed to Rt/Ot/Dt based on hour work.


      To my surprise all the templates I find online parse RT/OT/DT based what time of day someone worked. 


      I have a system with a standard weekly time card. 


      People clock in and then then clock out and I extract the total hour they worked for that day.  Easy.


      Then come all the rules that are both daily and weekly.


      Hours 1 - 8 are RT

      Hours 8 - 12 are OT

      Hours 12 and beyond are DT


      this too is easy.


      Then comes the weekly consideration.


      Once they reach 40 RT hours they go straight to OT (such as working a full week then working Saturday)




      If they work all 7 days they go straight to DT regardless show many hour they work.


      So... who can help me? 


      One bottle of your favorite drink shipped to the home of the one who solves my problem.

        • 1. Re: Calculating RT/OT/DT on weekly time
          Johan Hedman

          If you have a script running every weekend you can make that script first summarise that week and then handle where the Overtime are gonna end up


          On your Employee you add following fields






          Your script should then add/withdraw from RT/OT/DT and then after that move hours from RT to OT if it reach over 40.


          This fields show of course not be editable on the layout

          • 2. Re: Calculating RT/OT/DT on weekly time

            Thanks for the reply but I guess I did not post enough information.


            This is a fully interactive TC which must have realtime feedback for staff.


            Here is a sample event:


            Staff enters Time In: 8am

            Staff enters Time Out: 9pm

            Total Time is: 13 Hours.  Could be 8RT, 4OT, 1DT

            BUT if it is Saturday and he already has completed 40 hour it would be 0RT, 12OT, 1DT

            BUT if is is Sunday and he already completed 40 hours AND he as work 7 consecutive days it would be 0RT,8OT, 5DT.

            OR what if it is Saturday and he worked only completed 35 hours it would be: 4RT, 9OT, 0DT


            But wait!  There more.  This client is a union shops.  So if the above staff member is part of the union the hours are parsed differently


            But wait!  There is even more.  Some staff have contracts that could change the way hours are parsed (such a minimum 9 hour a day guarantee)


            Everything you see about must provide user with instant feedback because they are responsible for coding 100% of their time to jobs.   So if they their day is 8RT, 4OT, 1DT they need to be sure they have accounted for all those hours to job tickets.


            In other words this is NOT something where they can log there time and at the end of the week a script run through and organizes everything.

            • 3. Re: Calculating RT/OT/DT on weekly time
              David Moyer


              that's a very complicated scenario.  "But wait, there's more ..." suggests I get something for free, but have to pay for shipping.

              Seriously, I think that you'd need to work through a few more scenarios, maybe in spreadsheet fashion, showing the days of the week and the desired result.  Then, you still might not get free help.  But you might.

              • 4. Re: Calculating RT/OT/DT on weekly time

                Yes, complicated does do this justice.


                But, before you get into Union and Contracts, this is all basic payroll.  Anyone who write a time card system needs to handle this type of data crunching which I why I am so surprised I can not find any examples.


                But since I can't and since I have to do it, free is not part of the equation anymore (forgive the pun).


                If you or someone you know want to work with me to build this then I would be ready to pay for the service.


                you can email me directly if you want phil@gershwin-consulting.com

                • 5. Re: Calculating RT/OT/DT on weekly time

                  Yes, this is exactly something where the users log their time and whenever the payroll manager is ready (or automatically) a script is ran which calculates and organizes everything.


                  Our Kosmas solution handles this scenario and it's 100% built in FileMaker, so you're definitely not the first.  The basic RT/OT/DT situation is an example of standard California labor law.  Keep in mind that's not how all states handle that.  We also handle union rates, Davis-Bacon Act and other prevailing wage rates.  Our system also includes a full mobile time clock system with GPS for all employees.


                  For Department of Labor (DOL) purposes, you'll need to keep track of your calculations per time clock entry or time card approval.  If you're not careful, your client may be audited and you'll be in trouble for noncompliance.  So, ensure you're storing your calculation results in a way that is clear, lockable and easily understood.


                  Furthermore, it's likely your client wants time clock entries (as opposed to more generic time card) to handle labor job costing and workers' compensation calculations.  Therefore, it's important to store the RT/OT/DT calculation results, job key, activity key, and corresponding rates of pay in each individual punch.  We've built complex scripts that take everything into account and set this while handling gaps, errors, etc.


                  If you attempt to use unstored calculations to generate these results without a script running, you will eventually hit a performance wall once there are too many punches.  I recommend you build it with static fields, handle these calculations using scripts, and set the results that way.


                  Eric Miller


                  • 6. Re: Calculating RT/OT/DT on weekly time

                    OK, sound like you know what I need so how do I get Kosmas and integrate it into my solution?




                    Philip Gershwin | Gershwin Consulting | www.Gershwin-Consulting.com

                    323.600.5439 | phil@gershwin-consulting.com

                    <https://www.facebook.com/Gershwin-Consulting-1034695379946286/?pnref=story.unseen-section> <https://www.linkedin.com/in/philip-gershwin-7725b111a>

                    • 7. Re: Calculating RT/OT/DT on weekly time

                      The challenge really is in understanding the rules and making sure you can convert them into formulas.  You have to lay out all the rules first and then work on formulas to apply them all including knowing which ones supersede other rules, etc.  Such as do they always get a lunch break of a set time or do some people clock in and out for lunch, etc.  Being an engineer, I'm never afraid of the formulas.  The challenge usually is getting the client to write down all the rules and their relationships, etc.  And as soon as you put such a system in place, you will find out about additional exception not built into the system and will end up rearrange things again.  The benefit of going with someone who has already done this for your State, which is sounds like Eric Miller has, means they have a solution with probably most of your scenarios and would just need some minor tweaking to customize it for you.


                      Let us know if Eric can't help you out.  But if you need to do it yourself, you'll need to write down all of those rules before anyone can begin making calculations and scripts.


                      PS:  If you need to find a consultant in your State, check out:  FileMaker Consultants, Data Consultants, Database Consultants