6 Replies Latest reply on Dec 22, 2012 5:11 PM by CarlWeston

    Calculating hours worked in a given week

    CarlWeston

      Title

      Calculating hours worked in a given week

      Post

           I have searched the forums but I can't seem to get the answer I'm looking for.

           I am a freelance graphic designer and get paid by the hour. I have created my own Timesheet.

           I am trying to get the total income earned for a specific week.

           For instance, I have 1 record per day showing the current date, time start, time finish, hours worked, rate, income earned, tax and total hours worked for the day.
           If I worked for 5 days this week, how do I calculate what the total earnings are for those 5 days?

           For example;
           17 Dec = 9hrs
           18 Dec = 8.25hrs
           19 Dec = 9hrs
           20 Dec = 8.5hrs
           21 Dec = 9.5hrs
           Total Hours worked for the week = 44.25, multiply this by my rate and I get the total income earned for that week (+ tax).

           Obviously this is really simple with a calculator, but for the life of me I can't seem to get my head around how to do this automatically in Filemaker.

           Any help or examples of calculations would be appreciated.

           Cheers,
           Carl.

        • 1. Re: Calculating hours worked in a given week
          philmodjunk

               If you perform a find for all records in a given week, you can use a date range criterion: 12/17/2012...12/21/2012 in your find, a summary field will then accurately report the total hours for that week.

               If you use this calculation:

               DateField - DayOfWeek ( DateField ) + 1

               With "date" as the return type, you get the date for Sunday of the same week. You can use such a field in sorting and in relationships to get weekly totals as well.

          • 2. Re: Calculating hours worked in a given week
            CarlWeston

                 Thanks Phil for your feedback.

                 Is there a way I can do this without performing a find? There are a lot of records I would need find week by week to get the result.

                 I would like to have the result (hours worked in a week multiplied by the rate) to be displayed on the last day of the specified week.
                 I was thinking if on the last day of the week I entered the start date of the week and the finish date of the week I could do some sort of calculation that would total the hours worked for those dates.

                 If I did do the find and got the result as a summary, could that result be displayed as a permanent figure. i.e if I did a find for the first week of December and got a summary result and then did another find for the last week of June would I be able to go back to those weeks later on and see the result without doing another find?

                 I hope all this makes sense.

                 Cheers,
                 Carl.

            • 3. Re: Calculating hours worked in a given week
              philmodjunk

                   That's why I mentioned using the calcualtion field to produce a common value for all records falling in the same week.

                   You can add a subsummary layout part to your layout "when sorted by" this calculation field, put the summary field in this sub summary layout part. Sort your records by this calculation field to group them by week and you'll get a weekly total for every record in your found set--whether it be a single week or many weeks.

                   And via a relationship using this same field, you can also compute weekly totals--either by referencing the same summary field or by using a calcualtion field with the Sum aggregate function. Such a relationship could be via a table with one record for each week or a self join matching by this field could be used.

              • 4. Re: Calculating hours worked in a given week
                TKnTexas

                     I create a week number field on my daily transactions.  I use the WeekOfYearFiscal.  My calculation appends the year, YYYYWK and I format week number as 2digit padded with a zero.  Then as Phil mentioned subsummary on that field. 

                • 5. Re: Calculating hours worked in a given week
                  philmodjunk

                       I find this expression simpler for producing a value unique to each week:

                       DateField - DayOfWeek ( DateField ) + 1

                  • 6. Re: Calculating hours worked in a given week
                    CarlWeston

                         Thank you Phil and TKnTexas for your suggestions.
                         I'll give them a try and see how I go.

                         All the best to you and yours from me and mine and have a Merry Christmas and a Happy New Year.

                         Cheers,
                         Carl.