11 Replies Latest reply on May 20, 2010 10:34 AM by philmodjunk

    Calculate Work Hours By Day Type

    PaulSimon

      Title

      Calculate Work Hours By Day Type

      Post

      I'm starting with modifying the Starter Solution Time Card.
      I've added more entries to the day types and added a second set of In and Out times to each day.

      No problem getting the calculations for work hours to function correctly for this. The only stumbling block I've encountered so far is I want to generate hours worked using two sets of conditions.

      Condition 1 calculates work hours based on "day_type" = "Regular"
      Condition 2 calculates work hours based on "day_type" ≠ "Regular"

      A bonus is calculating overtime as anything over 40 "Regular" hours, which excludes any other type of "day_type" since those don't contribute to overtime pay.

      I haven't been able to figure out how to do that, yet.
      Any help or guidance would be appreciated.

      Thanks.






        • 1. Re: Calculate Work Hours By Day Type
          philmodjunk

          A case or IF function can handle this.

           

          If ( day_type = "Regular" ; //calculation for regular hours ; //calculation for non-regular hours )

           

          or

           

          Case ( day_type = "Regular" ; //calculation for regular hours ; //calculation for non-regular hours )

          • 2. Re: Calculate Work Hours By Day Type
            comment_1

            I am not sure what you mean by this:

             


            Paul Simon wrote:
            I want to generate hours worked using two sets of conditions.

            Condition 1 calculates work hours based on "day_type" = "Regular"
            Condition 2 calculates work hours based on "day_type" ≠ "Regular

            I see only one condition here, and no clue what should be calculated if it's true and what otherwise.

             

             


            Paul Simon wrote:
            A bonus is calculating overtime as anything over 40 "Regular" hours

             

            Try something like =

             

            Max ( 0 ; TotalHours - 40 )

             



            • 3. Re: Calculate Work Hours By Day Type
              PaulSimon

              Not sure.

              I calculate the hours worked in a day regardless of day_type.

              I want sum up the daily hours worked as Regular Hours and Non-Regular Hours (in different calculation fields) by summing up the work hours based on the day_type.

               

              Does that make sense?

              • 4. Re: Calculate Work Hours By Day Type
                philmodjunk

                Just a slight variation on the previously posted If or case functions will do this:

                 

                If ( day_type = "Regular" ; //calculation for regular hours ; "" )  (You can also leave out ; "" and get the same results)

                If ( day_type ≠ "Regular" ; //calculation for non regular hours ; "" )

                 

                Put each in a different field and use Comment's calc for over time hours.

                • 5. Re: Calculate Work Hours By Day Type
                  PaulSimon

                  It's still not differentiating between Regular and Non-Regular hours.

                  I'm getting Regular Hours summed as all the hours worked and the non-Regular Hours are getting no total.

                  This means somethings not being read right from the the day_type field, which is a drop-down value list:

                   

                  Regular

                  -

                  Holiday

                  Vacation

                  Sick Day

                  Bereavement

                  Miscellaneous

                  Training

                  -

                   

                  The calculation for Hours_Worked_This_Week_Reg is:

                   

                  If ( Day_Type = "Regular" ; Round(Sum( Work Hours ); 2))

                   

                   

                  The calculation for Work_Hours is:

                   

                  Round(

                  Hour( Time_End_1 - Time_Start_1 ) + Minute( Time_End_1 - Time_Start_1 ) / 60;

                  2)

                  +

                  Round(

                  Hour( Time_End_2 - Time_Start_2 ) + Minute( Time_End_2 - Time_Start_2 ) / 60;

                  2)

                  - Hours_Lunch

                   

                  • 6. Re: Calculate Work Hours By Day Type
                    philmodjunk

                    What you've posted should work. Perhaps you could upload the file to a file sharing site and post the link here. Then we could take a look at the file.

                    • 7. Re: Calculate Work Hours By Day Type
                      PaulSimon

                      Oh, just discovered this gem.

                       

                      If my first day_type of the week is regular I get the sum as I said above.

                      If I change it to anything else the total changes to Non-Regular Hours.

                       

                      I guess this is since it's a repeating field it doesn't matter what I put there in subsequent entries, only the first one gets read.

                       

                      Am I right in thinking repeating fields for the week are not the way to go and I should be using a portal instead?

                       

                      Hope not because I have no idea where to start.

                      I suppose if I have to it only helps me learn and be less of a novice in the long run.

                       

                      Thanks again.

                      • 9. Re: Calculate Work Hours By Day Type
                        philmodjunk

                        That these are repeating fields is the missing detail. We can fix this, but a much better design would be to toss out the repeating fields and replace them with a portal to a related table where each field repetition row has been replaced by a separate related record. Look up "Portals" in filemaker help to learn more.

                         

                        To fix the actual calculation:

                         

                        Your calculation must also be defined as a repeating field. Since you have 7 repetitions in your layout, you must also have 7 repetitions specified for your calculation. Use the box in the lower left corner of specify calculation to set it up with 7 repetitions.

                        • 10. Re: Calculate Work Hours By Day Type
                          PaulSimon

                          I did change the calculation to seven repetitions, but doesn't work.

                          For now I'm doing as you suggested and setting it up as a portal.

                           

                          Can I use self related fields for this from the current table?

                          • 11. Re: Calculate Work Hours By Day Type
                            philmodjunk
                            I did change the calculation to seven repetitions, but doesn't work.

                            If you're interested, compare your design to this test file I used to make sure my information was correct: http://www.4shared.com/file/tX-dVoON/RepeatingFIeldCalc.html

                             

                            For now I'm doing as you suggested and setting it up as a portal.

                            Can I use self related fields for this from the current table?

                             

                            I can't really say from here; I don't know enough about your table structure.