7 Replies Latest reply on Aug 12, 2013 8:16 PM by philmodjunk

    Work time calculation

    LynnRogers

      Title

      Work time calculation

      Post

           Hey everybody'

            I need a time calculation the can return 30 minute intervals as ".5". I am using FM pro 12 advance and my DB has a "Start Work", "Start Lunch", "End Lunch" & "End Work" fields. example

           Start Work           8:00                        Start Work          8:00                                Start Work         800:00

            Start Lunch       12:00                       Start Lunch      12:00                               Start Lunch

           End Lunch        12:30                        End Lunch       12:30                               End Lunch

           End Work           4:30                         End Work          4:00                                 End Work         1430:00

           Hours Worked    8.0                          Hours Worked   7.5                                  Hours Worked      6.5

           Military times works just as good and maybe better because I want need AM/PM

           Thanks for any help

            

            

        • 1. Re: Work time calculation
          philmodjunk

               24 hour and 12 hour time formats are stored and calculate the same, but Timestamps would be best. THey will work even when the start time is before midnight and the end time comes after midnight.

               The difference between two time fields or two time stamp fields will be measured in seconds. To show the results as hours with a decimal for the fraction of an hour, use this expression:

               ( TimeStampEnd - TimeStampStart ) / 3600

               And if you need to round to the nearest half hour, this basic approach can be modified to show that as well.

          • 2. Re: Work time calculation
            LynnRogers

                 I tried using the timestamp but the issue with that is the layout form already has a location for the date and the fields for the work times are not large enough for the the timestamp format. ie. "08/08/2013 1130:03". Is there anyway to enter just the work times and get the hours worked calculated as .5 for all half hour intervals.  (1.0, 1.5, 2.0, 2.5 etc)

                 Thanks Lynn

            • 3. Re: Work time calculation
              philmodjunk

                   You can use two separate fields for date and time and still get a timestamp value:
                   GetAsTimeStamp ( DateField & " " & TimeField )
                   You can plug that in in place of a timestamp field in my calculation example.

                   Can you explain in more detail what you mean by: "and get the hours worked calculated as .5 for all half hour intervals.  (1.0, 1.5, 2.0, 2.5 etc)"

                   What should be the result if the person works 8 hours 15 minutes? Should that calculate as 8.5? Do you want to round to the nearest half hour?

              • 4. Re: Work time calculation
                LynnRogers

                     If I worked 8 hours the results should be 8.0

                     for 8 hours and 15 minutes the results should be 8.25

                     8 hours and 30 minutes 8.5

                     and 8 hours and 45 minutes 8.75

                     This calculation has me stumped but the computers at my work have these results

                     Any help is appreciated

                     Thanks Lynn

                • 5. Re: Work time calculation
                  philmodjunk

                       But what result should you get if the person worked 8 hours 5 minutes? Are you rounding to the nearest 15 minutes--a common requirement with timecard systems or do you just need the decimal equivalent of 8 hours 5 minutes in hours?

                       To round to the nearest 15 minutes, you can use this expression:

                       Round( ( TimeStampEnd - TimeStampStart ) / 60 / 15  ; 0 ) / 4

                  • 6. Re: Work time calculation
                    LynnRogers

                         No I want need filemaker to round to the nearest 15 minute.  when I enter the time in my work orders I will round the time to the nearest 15 minutes.

                    • 7. Re: Work time calculation
                      philmodjunk

                           Then my previous post has what you need.