7 Replies Latest reply on May 5, 2013 2:42 PM by O.Bass

    Time Cards with automatic overtime

    PhantomDatabase

      Title

      Time Cards with automatic overtime

      Post

           Hey all,

           I have a database for time cards and I need to add in overtime. I would like to have the database automaically calculate these but it seems like a multi level calculation and I haven't had luck in figuring it out.

           It's a 2 parter:

           I need to calculate anything over 8 hours in a day as time and a half and 12 hours as double time

           AND

           anything over 40 hours in a week is time and a half.

            

           I can easily get it to do 1 or the other but not both.

            

           So for example:

           Monday: 8 hours

           Tuesday: 8 hours 2 OT

           Wednesday: 8 hours 4 OT 1 DOT

           Thursday: 8 hours

           Friday: 8 hours 2 OT

           (we have now hit 40 hours of regular time so automaically any hours worked are OT and over 8 is DOT)

           Saturday: 8 OT 1 DOT

           Sunday: Day off

            

           Can anyone help me with this logic? Thanks

        • 1. Re: Time Cards with automatic overtime
          philmodjunk

               How did you structure your table to record hours worked?

               Do you have one record for each day an employee works or one record for each week that an employee works with either multiple fields or a repeating field for recording daily hours?

          • 2. Re: Time Cards with automatic overtime
            PhantomDatabase

                 right now it is 1 record for the week with repeating fields for each day.

                 So there is a clock in, clock out, hours of breaks, and total.  so you can say you worked 9am - 6pm with an hour lunch

            • 3. Re: Time Cards with automatic overtime
              philmodjunk

                   Are there any shifts worked that start on one day and then end after midnight? If so, how do you record that time worked?

                   Unlike the starter solution that comes with FileMaker, I generally lean in favor of one record per day and then using finds and/or relationships to group data by a date range such as the current week.

                   This can make some of your layouts a bit more complex, but the calcualtions become simpler--I think.

                   I'm feeling my way towards a solution here...

                   Looks like hours worked for any given day can be divided up into 3 categories by total time worked:

                   0 - 8, Greater than 8 but less than 12, More than 12

                   Each of these figures will be multiplied by an hourly rate and then added to provide a total wages for the day, but the rate is the employee's paygrade multiplied by an "overtimeModifier" and it is that value that can be modified by the total hours worked for a given week.

                   Make sense so far?

                   What I'm mulling over here is what should happen if total hours worked for the day produces a total hours for the wee of 41 hours. What do we then do with that 1 extra hour in terms of our calculation? (I believe that I see how to do it "on paper", but am considering options for how to handle that in the DB.)

              • 4. Re: Time Cards with automatic overtime
                PhantomDatabase

                     Yeah you are on the same track as I am.

                     What I have right now 3 categories:

                     Regular hours: 1-8 hours worked

                     OT: 8-12 hours worked

                     DOT: 12+ hours worked

                     So if you work saturday and go into 40+ hours for the week, anything over 40 is no longer regular hours. it now goes into the OT category

                • 5. Re: Time Cards with automatic overtime
                  PhantomDatabase

                       Thanks for your help! I finally figured it out!!!  It has taken me a long time to finally figure out all the logic but now you just enter in your hours and all of  the labor laws for OT take over.  The only thing I haven't added is 7 day penalties.

                       If anyone needs help figuring this out, let me know and I can post my formulas and explain it.

                  • 6. Re: Time Cards with automatic overtime
                    philmodjunk

                         I encourage you to post your solution. Not only can that help others (sometimes people search this forum for solutions to specific problems), but the ensuing discussion might result in ideas that further improve the solution that you shared.

                    • 7. Re: Time Cards with automatic overtime
                      O.Bass

                           Hi,

                           I am looking for the same type of solution, a time card layout with these exact same parameters; I would love to have your layout or formulas.

                            

                           Best regards,