13 Replies Latest reply on Aug 26, 2016 7:24 PM by philmodjunk

    Weekly Overtime

    addison

      I have a time card database which is almost complete, but I don't know how to tell it to calculate overtime if someone works more than 40 hours in a week.

       

      I have it set up so that one day is one record, and our payroll is biweekly, so two weeks makes up a pay period.

       

      I have a payroll report that groups the records for each pay period by employee. Regular time and overtime are calculated on each record, and totals are added up with summary fields in the payroll report. All of this works as it should, but I realized that there is no way for the report to know if someone worked more than 40 regular hours in a week (which would go into overtime).

       

      The challenge is that two weeks makes up a pay period, so I think I will need to somehow indicate when a week starts and ends. I have a table for Pay Periods, which associates dates with start/end dates for the pay period, but I'm not sure where to go from there.

       

      Please let me know if anyone has suggestions, or if you need more information.

       

      Thank you for any help!

        • 1. Re: Weekly Overtime
          philmodjunk

          A relationship that matches by employee ID and those start end dates would match only to the records for one employee for a given pay period. Then either Sum or a summary field can be used to compute totals and other aggregate values.

           

          You might create a "paycheck" table where you create one record per employee per pay period with the needed dates and employee ID so that you can compute and display such totals.

           

          But your summary report could also do the same if you had a field that enabled you to group records by the same pay periods.

          • 2. Re: Weekly Overtime
            addison

            Hi Phil, thanks for the quick response. Yes, I think my report is set up the way you're describing.

             

            Right now, I have three tables: Time Cards, Pay Periods, and Employees. Time Cards is linked to Pay Periods by the date, and it's linked to Employees by account name. Pay Periods has fields for "Today", "Pay Period Start", and "Pay Period End".

             

            My Payroll Report is a sub-summary report by Employee ID (which appears on the daily time card records along with other employee info from the Employees table) and it then sorts by the pay period end date, resulting in a time card-like format that can be saved as a pdf and sent to our payroll provider. I've used summary fields in the report to add up the total regular hours and overtime hours for the pay period, but that only accounts for daily overtime (working more than 8 hours a day).

             

            I think the problem I'm running into is that I need to perform a calculation based on a week's worth of dates, in order to get the 'weekly' overtime in addition to the daily overtime, while still maintaining our bi-weekly pay period setup. So I think I need to tell FileMaker which dates fit within a week, and if the regular hours for one week for any given employee are greater than 40, then it needs to overflow into overtime. Does that make sense?

            • 3. Re: Weekly Overtime
              DrewTenenholz

              Addison --

               

              Am I correct in stating that overtime is calculated per week, not per pay period?  In other words, if someone works 50 hours in week one and 30 hours in week two, their pay should be:

               

              40 hours x base rate/hour + 10 hours x overtime rate/hour + 30 hours x base rate/hour

               

              and NOT 80 hours x base rate/hour + no overtime

               

              If that is the case, you need to have a way to link all the days for a work week for a given employee together.  That's a variation on what philmodjunk wrote, in that you need to link each and every week, not the pay periods.

               

              To do so, you could use a table of weeks and use some sort of date range in the relationship graph.  A more old-fashioned way would be to create a relationship to the 'work day' table for the employee that uses some form of the Year() and WeekofYear() or WeekofYearFiscal() that is used in combination with the employee record ID and a Dayof Week() calculation to help you set up other calculated fields that shows total number of hours an employee has worked for the given week and split that into overtime and full time hours.

               

              What I'm thinking of is a version of the self-join (the 'work days') table to a second occurrence of itself in the graph  based on:

              EmployeeID=EmployeeID AND

              Year(work date)=Year(work date) and

              WeekofYear(work date) = WeekofYear (work date) AND

              DayofWeek(workDate) > DayofWeek(work date)

               

              The last item in the linking is a way of saying, "Show me all records for this employee for this week that came before myself".  Now, you can set up calculated fields for:

               

              How many hours (prior to today) has the employee worked for the week?

              How many hours (including today) has the employee worked for the week?

              If the number of hours worked (including today) is over forty, then split up the hours into regular time and overtime and get me the total for that day's work.

               

              If you repeat this over the whole series, you should be able to come up with the correct amount to be paid and it will be done on a per day basis.

               

              This way, you don't have to mess with your pay periods work which you say is doing what it should.  The only change is the calculation for how much did the employee earn today.

               

              Hope this helps,

              Drew Tenenholz

               

              P.S.  Look into the WeekOfyear() and WeekofYearFiscal() as well as the DayOfWeek() functions so you can include the correct offsets for weeks that are Sunday-Saturday or Monday - Sunday or whatever you are doing in your operation.  Also pay attention to the week of year, since funny things happen around Dec. 25 - Jan 6, depending on the year.  You want to be doing things the same way your bookkeeper does.

              1 of 1 people found this helpful
              • 4. Re: Weekly Overtime
                addison

                Thanks Drew, I will look into those functions. It sounds like I might be able to add an additional field to my Pay Periods table that would designate the day of the week for each date.

                 

                Yes, you are correct, overtime is calculated by week. I currently have it set up to show daily overtime and then calculate that for the pay period, but I realized that that could potentially create issues if, for example, someone worked 8 hours a day Monday through Friday, and then came in for 4 hours on a Saturday. Those 4 hours would need to count as overtime but the way I have it set up right now they would appear as regular time.

                 

                I'm actually not calculating the rates/pay in the database; all our payroll provider needs from us is the hours worked, so at least that simplifies it a bit.

                 

                I will also attempt your suggestion of the self-join table and see which works out best. Thanks so much for the help!

                • 5. Re: Weekly Overtime
                  philmodjunk

                  A pay period table is not the same as a paycheck table. The first has one record per pay period, the second has one record per employee paycheck. You need the ability to group records by both employee AND by specified pay period. This can be done by such a table and a relationship. Alternatively, it can be done by grouping records by employee and by pay period.

                  • 6. Re: Weekly Overtime
                    addison

                    Yes, that is what I am doing in my report, sorry if that wasn't clear. The report sorts by employee and by pay period. Are you suggesting something different? I wanted to avoid creating one record per employee per pay period, because it gets extremely difficult to handle with so many fields and calculations (this is how I initially tried to create this database, only to find that creating one record per day per employee, and then creating a report from those records, was much more manageable). Perhaps I don't understand what you mean by a paycheck table?

                    Thanks!

                    • 7. Re: Weekly Overtime
                      philmodjunk

                      A paycheck table need not have many fields, mainly date and employee fields.

                       

                      With the subsummary report and no paycheck table, it's all in how you set up sub summary parts and sort your records. You need a field that uniquely identifies each pay period--which you probably have.

                       

                      Subsummary Sorted by Employee

                      Body

                      SubSummary Sorted by Pay Period

                       

                      Summary fields placed in the bottom subsummary part will compute subtotals based on the time card data for one employee for one pay period provided you sort records by both employee AND by pay period.

                      1 of 1 people found this helpful
                      • 8. Re: Weekly Overtime
                        addison

                        I think I understand what you're saying. Right now, I'm using the Pay Period End field as a 'unique identifier' I suppose.

                        My report looks like:

                         

                        SubSummary Sorted by Employee ID

                        Body

                        SubSummary Sorted by Pay Period End

                         

                        This works great for adding up the regular hours and the daily overtime, but it's sounding like I will need to have not only a unique identifier for each pay period, but a unique identifier for each week, in order to calculate the weekly overtime.

                         

                        For example, if an employee worked 8 regular hours each day Monday through Friday, but then they worked 6 hours on Saturday, it would calculate 46 regular hours instead of 40 regular and 6 overtime. I think I'm getting confused because payroll is bi-weekly while overtime has to be calculated on a daily (over 8 hours) and weekly (over 40 hours) basis.

                        • 9. Re: Weekly Overtime
                          philmodjunk

                          Sounds right to me and in my previous posts, I missed the detail that you needed weekly subtotals.

                          • 10. Re: Weekly Overtime
                            addison

                            Hi Phil,

                             

                            I made a new calculation field called EndofWeek. I think this will work as an identifier for each week, but what's been confusing me now is how to:

                            1) separately calculate each week's hours (would I do this in the report?)

                            2) when regular hours for one week exceed 40, add the extra hours into overtime for the pay period

                             

                            I understand that I will probably have to create a few more fields (calculated fields? Summaries?) in order to do this, since it seems like a multi-step calculation, but do you have any suggestions on how I would get started?

                            • 11. Re: Weekly Overtime
                              TKnTexas

                              Create a field for week number using the WeekOfYearFiscal.  I create my result as yyyy.ww such the week 15 of 2016 is different than week 15 of 2015 or 2017.   I have summarized the DailyTime table (one record per day) into a PayWeek Table (one record per week).

                              1 of 1 people found this helpful
                              • 12. Re: Weekly Overtime
                                addison

                                Thanks, I just made a field for week number, as you suggested, and it seems to be working. I created it in my table for the daily time cards-- are you suggesting that I then use this field in the report I already have?

                                 

                                And how would I go about telling my report to calculate the hours separately for the two weeks per pay period, but then to add together the regular and OT hours? It sounds relatively simple to me but I can't quite wrap my head around how to do it.

                                • 13. Re: Weekly Overtime
                                  philmodjunk

                                  You can create a subsummary when sorted on this week number field and put a summary field in it to get a subtotal for the week. In calculations to compute something such as weeklyOvertime * overtimerate, you can use the getsummary function