8 Replies Latest reply on Feb 22, 2013 8:16 AM by philmodjunk

    Elapsed time calculation excluding date

    MedicInMe

      Title

      Elapsed time calculation excluding date

      Post

           Hello

            

      I am looking to calculate elapsed time in military format that lapses only one day. For example I send out a technician at 23:00 and they work until 02:10. This would be 3 hours and 10 minutes. 
      
      Due to the setup and client's wants the date on the form is for when the call starts. I have set up an excel sheet that performs the function quiet well however the I can't use the same calculation. 
      
      The excel calculation is =--TEXT(MOD(TEXT(B19,"0\:00")-TEXT(B11,"0\:00"),1),"hhmm").

           I do not want to use timestamps as times will not always be entered as things are happening. I have found some calculations that work to calculate the time elapse however the formatting is comes back as 3.1666666666667 instead of 3:10 (3 hours and 10 minutes). 

            

           Any help would be greatly appreciated.

            

           Thanks

           Brian

        • 1. Re: Elapsed time calculation excluding date
          philmodjunk

               I'd still use timestamp fields for this. The techinician need not enter data in either field, however--which I think is the issue with using timestamp fields.

               Use buttons with scripts to log the start and stop times into timestamp fields named "Start" and "stop": The user need only click a button to log the start time and click another to log the stop time.

               Start time script:

               Set Field [YourTable::Start ; Get ( CurrentTimeStamp ) ]

               Stop time script:

               Set FIeld [YourTable::Stop ; Get ( CurrentTimeStamp ) ]

               Then a calculation field with this expression:

               Stop - Start

               can select "time" as the return type to show your elapsed time. (Difference of two time or two timestamp fields computes elapsed time in seconds). Data formatting can be specified on the layout to show HH:MM format for your elapsed time or you can use:

               (stop - Start ) / 3600

               with Number as the return type to return elapsed time as a decimal value expressed in hours.

               Other options:

               Start can be set up to auto-enter the current timestamp.

               You can use three fields: 1 date, 2 time to produce two time stamp fields for computing elapsed time:

               GetAsTimeStamp ( StartDate & " " & EndTime ) - GetAsTimeStamp ( StartDate & " " & StartTime )

          • 2. Re: Elapsed time calculation excluding date
            MedicInMe

                 Hello Phil

                  

                 Thanks for the response. The only with this is some of this data entry will be after the fact. Sometimes times will get written down and entered hours later so this will not work. I was hoping to find a calcualtion that is similar to the excel calucaltion that would convert it into hours and minutes (3:10) instead of a decimal (3.1666666667). At this point a calculation that would convert the result from a decimal into a time format of hh:mm. would be acceptable as well.

                  

                 Thanks

                 Brian

            • 3. Re: Elapsed time calculation excluding date
              philmodjunk

                   That's why I added this at the end of my post as an optional approach:

                   You can use three fields: 1 date, 2 time to produce two time stamp fields for computing elapsed time:

                   GetAsTimeStamp ( StartDate & " " & EndTime ) - GetAsTimeStamp ( StartDate & " " & StartTime )

                   You enter the date in StartDate, the time they started in StartTime and the Time the ended in EndTIme. The above calculation then computes the elapsed time.

              • 4. Re: Elapsed time calculation excluding date
                MedicInMe

                     This works for times in the same day. If you go over the 24 hour mark and use the start date it will produce a negative time value as a result. 

                      

                     23:00 start

                     01:45 end

                     -21:15 result

                • 5. Re: Elapsed time calculation excluding date
                  MedicInMe

                       If you add 86400 to the end of that calculation it works for times going into the next day but times in the same day will get a result over 24 hours. Would it be possible to write a second part of the calculation that state.   If result is greater that 24:01 subtract 24:00 and if less than 24:00 do nothing?

                  • 6. Re: Elapsed time calculation excluding date
                    philmodjunk

                         Good catch but you can't have it both ways, either you specify the date twice or always assume a date for the stop time--either always the same day or always the next day.

                         I recommend using:

                         GetAsTimeStamp ( EndDate & " " & EndTime ) - GetAsTimeStamp ( StartDate & " " & StartTime )

                         You can set up EndDate to auto-enter either the same date as StartDate or StartDate + 1 to automatically enter the most typical date, yet still have a field where you can edit the date to modify it when needed.

                    • 7. Re: Elapsed time calculation excluding date
                      MedicInMe

                           How about having filemaker look at the start date at the top of the form and the start time field. If the start time is between 12:00 (noon) to 23:59 (11:59 pm) and the end time is between 00:00 (midnight) and 11:45 am it will set the end date to the next day. Otherwise end date will be the same as the start date. 

                            

                           Or an easier way would be if the start time is is greater than the end time (ie  start 22:00 and end time is 05:00) it will set the end date as the next day unless the calculation is over 22 hours at which point it will generate and error and ask the user to verify times. 

                            

                            

                            

                      • 8. Re: Elapsed time calculation excluding date
                        philmodjunk

                             Both options are possible using if functions to compare the times. Here's an expression for the second option:

                             GetAsTimeStamp ( If ( EndTime > StartTime ; StartDate ; StartDate + 1 ) & " " & EndTime ) - GetAsTimeStamp ( StartDate & " " & StartTime )