1 2 Previous Next 24 Replies Latest reply on May 18, 2016 12:22 AM by olesimonsen

    Time calculation. Subtract a number of minuts from a field with time of day

    olesimonsen

      Hi,

       

      I am faced with what I thought was a fairly simple task.

       

      In the solution I am trying to create, I want users to insert a time of day into a time field (24H). (Field 1.)

       

      In another field I would like to display another time field (Field 2) which is the time in field 1 - xx minutes.

       

      Field 2 is a calculation: GetAsTime (Field 1 - XX)

       

      To show an example. If I enter 1300 into field 1 (Time, hh) and the time I would like to subtract is 45 minutes I get a return in field 2 of 1299.15.00 in stead of just 1215.

       

      For some odd reason I can not get this to work...

       

      Any ideas?

       

      Best regards

       

      Ole

        • 1. Re: Time calculation. Subtract a number of minuts from a field with time of day
          erolst

          The base unit for time are seconds, not minutes; also, FM doesn't parse a numeric input into a time the way you expect it.

           

          That means you must convert both the user input and your subtraction value to the correct type and unit, respectively. e.g.

           

          Let ( [

            input = 1300 ; // replace with input field

            subtract = 45 ;  // hardcoded subtraction value

            theTime = Time ( Left ( input ; Length ( input ) - 2 ) ; Right ( input ; 2 ) ; 0 ) ;

            minutesToSubtract = subtract * 60

            ] ;

            theTime - minutesToSubtract

          )

           

          returns 12:15 – if your calc field is set to result type Time.

          • 2. Re: Time calculation. Subtract a number of minuts from a field with time of day
            casystems

            Time calculations work in seconds

             

            So:

            Input is a time field

            Subtract is a number field (for the minutes)

            theTime= Input-(Subtract*60) (time calculation field)

             

            That should work...

            • 3. Re: Time calculation. Subtract a number of minuts from a field with time of day
              erolst

              Hi Ole -

               

              for some reason I mis-read that the user input goes into a text field. If they are populating a time field, then you don't need any parsing, and casystems's approach if of course the easier one.

              • 4. Re: Time calculation. Subtract a number of minuts from a field with time of day
                olesimonsen

                Hi both,

                thank you for swift responses. I will give it a try later today when time permits....

                 

                thx again!

                 

                best regards

                 

                Ole

                • 5. Re: Time calculation. Subtract a number of minuts from a field with time of day
                  user19752

                  There is reason to mis-read (or not), since OP wrote "a time of day into a time field (24H)" but the value is 1300. time of day should be < 24 hours.

                  • 6. Re: Time calculation. Subtract a number of minuts from a field with time of day
                    olesimonsen

                    Hi all,

                     

                    The solution:

                     

                    Let ( [

                      input = 1300 ; // replace with input field

                      subtract = 45 ;  // hardcoded subtraction value

                      theTime = Time ( Left ( input ; Length ( input ) - 2 ) ; Right ( input ; 2 ) ; 0 ) ;

                      minutesToSubtract = subtract * 60

                      ] ;

                      theTime - minutesToSubtract

                    )

                     

                    worked brilliantly for this as the subtract time was a fixed value.

                     

                    Thank you all for your inputs!!

                     

                    I now have another problem with a fairly simple task.

                     

                    I have a time field with 24h hhmm time format. (Field 1)

                     

                    Another field formatted exactly the same way as field 1 where a user can insert another time of day. (Field 2)

                     

                    What I am trying to achieve is getting the difference in time to show a delay (Field 3):

                     

                    "The bus was supposed to come at time (field 1) but did not arrive until time (Field 2) so the delay was xx minutes (field 3).

                     

                    Field 3 is set up as a calculation:

                     

                    (field2 - field1) result in time mm.

                     

                    I get some very erratic results. Anything I am getting?

                     

                    Best regards

                     

                    O

                    • 7. Re: Time calculation. Subtract a number of minuts from a field with time of day
                      erolst

                      olesimonsen wrote:

                      I get some very erratic results. Anything I am getting?

                      Erratic results?

                       

                      olesimonsen wrote:

                      I have a time field with 24h hhmm time format. (Field 1)

                       

                      Another field formatted exactly the same way as field 1 where a user can insert another time of day. (Field 2)

                       

                      Field 3 is set up as a calculation:

                       

                      (field2 - field1) result in time mm.

                      Don't forget to set the correct result type for your calculation field (or data type if it is a field with auto-enter calc).

                       

                      If you only want the minutes, than that type would be number; try

                       

                      Ceiling ( ( time2 - time1 ) / 60 )

                       

                      to always round up, or Int() instead of Ceiling() to always round down.

                       

                      btw, formatting is just that; these calculations all deal with the internally stored value, which is simply the number of seconds since midnight.

                      • 8. Re: Time calculation. Subtract a number of minuts from a field with time of day
                        casystems

                        One word of warning, with your solution..

                         

                        While it is convenient to use a text field to enter the time, which you then split into hours and minutes you don't have any check in place to stop meaningless entries. For example somebody could enter say 2675 as a time. While this would convert to 26 hours and 75 minutes and the calculation would still probably work there is no 'checking' on what's entered... Using a time field instead would ensure that 'proper' times are entered..

                         

                        Re the two time calculations

                        Convert the two 'times' into actual times using the Time ( hours ; minutes ; seconds ) function

                        Subtract one from the other to get the difference in seconds and the divide the result by 60 to get minutes

                         

                        Carl

                        • 9. Re: Time calculation. Subtract a number of minuts from a field with time of day
                          olesimonsen

                          Thank you Erolst and Carl,

                           

                          I have a nasty feeling of being somewhat dim-witted, but I just can't get my head around this.

                          If I set up field 1 & 2 as Time fields hmm and use the Ceiling (field 2 - field 1) / 60 I still don't get the desired result:

                           

                          If field 1 is time 0850 and field 2 is  time 0905 I get the result 55, so it seems the ceiling (field 2 - field 1) / 60 treats the values as 805 and 905 respectively which is somewhat strange as I have set the field type to time.

                           

                          As to your suggestion Carl I am not quite sure how to apply the time function in this instance. I have tried to use the GetAsTime function but no joy?

                           

                          Best regards

                           

                          Ole

                          • 10. Re: Time calculation. Subtract a number of minuts from a field with time of day
                            casystems

                            Field1 = your time as text in the format hhmm so 0850

                            Field2 = your time as text in the format hhmm so 0905

                            Field1Hours= Left(Field1;2) as a number

                            Field1Mins= Right(Field1;2) as a number

                            Field2Hours= Left(Field2;2) as a number

                            Field2Mins= Right(Field2;2) as a number

                            Time1=Time(Field1Hours;Field1Mins;0) as time

                            Time2=Time(Field2Hours;Field2Mins;0) as time

                             

                            Timedifference=(Time2-Time1)/60 as number to get difference in minutes

                             

                            Can't see why you should need to round anything as you will always have a whole number of seconds

                             

                            Carl

                            • 11. Re: Time calculation. Subtract a number of minuts from a field with time of day
                              erolst

                              casystems wrote:

                              Timedifference=(Time2-Time1)/60 as number to get difference in minutes

                               

                              Can't see why you should need to round anything as you will always have a whole number of seconds

                              That's not much help if you want a “whole number of minutes – unless it happens that

                               

                              Mod ( aWholeNumberOfSeconds ; 60 ) = 0

                               

                              Ole –

                               

                              see screenshot: two time fields, and a calculation field, type number (with some error trapping):

                               

                              Screen Shot 2016-05-17 at 20.45.20.png

                              • 12. Re: Time calculation. Subtract a number of minuts from a field with time of day
                                olesimonsen

                                Wow, that was fast! Thank you!

                                 

                                You are quite right. I do not really need the rounding off as I only use whole number.

                                 

                                The calculation actually is about the off blok times of an aircraft leaving the gate.

                                 

                                The times are "ScheduledOffBlok" and "ActualOffBlok".

                                 

                                I am unsure if this can be done in one calculation?

                                 

                                I suspect I will need to use the Let function?

                                 

                                Let

                                 

                                ScheduledOffBlokHours= Left(ScheduledOffBlok;2)

                                ScheduleOffBlokMins= Right(ScheduleOffBlok;2)

                                ActualOffBlokHours= Left(ActualOffBlok;2)

                                ActualOffBlokMins= Right(ActualOffBlok;2)

                                Time1=Time(ScheduledOffBlokHours;ScheduledOffBlokMins;0) //as time

                                Time2=Time(ActualOffBlokHours;ActualOffBlokMins;0) //as time

                                 

                                Timedifference=(Time2-Time1)/60

                                 

                                Best regards

                                 

                                Ole

                                • 13. Re: Time calculation. Subtract a number of minuts from a field with time of day
                                  casystems

                                  Flying is my hobby :-) and yes you need Let unless you use multiple fields ..

                                  Carl

                                  • 14. Re: Time calculation. Subtract a number of minuts from a field with time of day
                                    olesimonsen

                                    ;-) great hobby!!

                                     

                                    From your info I think you live close to Duxford. I envy you ;-)

                                     

                                    So:

                                     

                                    Let ( [

                                    ScheduledOffBlokHours= Left(ScheduledOffBlok;2)

                                    ScheduleOffBlokMins= Right(ScheduleOffBlok;2)

                                    ActualOffBlokHours= Left(ActualOffBlok;2)

                                    ActualOffBlokMins= Right(ActualOffBlok;2)

                                    Time1=Time(ScheduledOffBlokHours;ScheduledOffBlokMins;0) //as time

                                    Time2=Time(ActualOffBlokHours;ActualOffBlokMins;0) //as time

                                     

                                    Timedifference=(Time2-Time1) / 60

                                    )

                                     

                                    ??

                                     

                                    Ole

                                    1 2 Previous Next