3 Replies Latest reply on Nov 10, 2010 1:03 PM by WillChildress

    calculating elapsed time within certain range



      calculating elapsed time within certain range


      I need to calculate the elapsed time (or portion of) that occurs between a specific time, 15:31 to 08:29.  

      My current fields are:  StartTime, EndTime, and Total Reg Case TIme (this calculates the elapsed time between the StartTime and EndTIme)

      - so I want to make a new field called, Total OT Case Time, which will calculate any portion of elapsed time that falls outside of the normal business hours (08:30 to 15:30) from the StartTime and EndTime fields.  

      I'm new to Filemaker so this calculation has me totally stopped, I don't know how to make this happen, can someone please assist?

      Thank you very much.

      PS - Just so everyone knows what I'm looking at:  My StartTime and EndTime fields are just Time fields set to military time.  And my Total Reg Case TIme field is the following calculation:

      (Round(Hour( EndTime - StartTime ) + Minute( EndTime - StartTime ) / 60;2)+If(GetAsNumber(EndTime) <= GetAsNumber(StartTime);24;0)) * 60

      This calculation outputs the elapsed time in minutes and counts time that starts on one day but ends past midnight (the new calculation I'm seeking will have to do the same).

      The result I need will have to count any portion of time that starts before 08:29, but stops counting at 08:30, even if the endtime goes beyond that time; and counts any portion of time that goes beyond 15:31, even if the starttime was prior to 15:30, but does not count the time prior to 15:31 -- for example, if StartTime is 14:30 and EndTime is 18:30 then Total Reg Case Time (the filed I already have) results 240 minutes and Total OT Case Time (the field I'm looking for) will result 179 minutes...

        • 2. Re: calculating elapsed time within certain range

          I've been tinkering with this for two days off and on.

          Here's what I've come up with:

          First, your fields should be timestamp fields instead of time fields. These fields record the date and time and you can compute elapsed time simply by subtracting them, which gives you the elapsed time in seconds. There's no need for special code to handle intervals that span the midnight hour.

          ( TimeEnd - TimeStart ) / 3600 will then compute your elapsed time in hours.

          Using TimeStamp fields, an over time hours calculation can be defined this way:

          Let ( [ OTE1 = GetAsTimestamp ( GetAsDate ( TimeStart) & " 8:30" ) ;
                    OTE2 = OTE1 + 86400 ;
                    OTS2 = GetAsTimestamp ( GetAsDate ( TimeStart) & " 15:30" ) ;
                    OTS1 = OTS2 - 86400;
                    TSO1 = Max ( OTS1 ; TimeStart );
                    TEO1 = Min ( OTE1 ; TimeEnd );
                    TSO2 = Max ( OTS2 ; TimeStart );
                    TEO2 = Min ( OTE2 ; TimeEnd )
                   ] ;
                  Case ( TimeStart > OTE1 and TimeEnd < OTS2 ; 0 ;
                            TSO1 < TEO1 ; ( TEO1 - TSO1 ) / 3600 ;
                            ( TEO2 - TSO2 ) / 3600
                            ) // end case
                  ) // End Let

          The calculation checks to see if any part of the specified time stamp interval overlaps the over time period from two different days in order to handle all possible situations.

          I wouldn't be supreised if there were a simpler calculation for this, but this does appear to produce the correct OT hours for your specified time interval. You can also replace the constants in quotes with references to fields to make this a calculation you can update without having to modify the field definition.

          86400 seconds = 1 day and 3600 seconds = 1 hour.

          • 3. Re: calculating elapsed time within certain range

            PhilModJunk, I don't know how to thank you enough!!!  This is AWESOME -- Thank you, Thank you, Thank you!!!