2 Replies Latest reply on Jan 26, 2016 11:13 AM by Alijay

    FormCalc Newbie, Time Difference Equation


      Hey everyone, I'm pretty green when it comes to this stuff but I'm trying to figure this out so I'm reaching out here. I'm trying to develop an equation in FormCalc that will calculate the difference, in hours, between two times. The times will be entered in GMT and I have the equation below, so far, that works but the problem is when the second (OFF_DUTY_TIME) is beyond 24:00, such as 02:30. Any insights or suggestions?


      What I have so far:



      if (HasValue (OFF_DUTY_TIME)  and HasValue (ON_DUTY_TIME)) then (Time2Num(OFF_DUTY_TIME.formattedValue, "HH:MM") - Time2Num(ON_DUTY_TIME.formattedValue, "HH:MM")) / (60 * 60 * 1000) endif

        • 1. Re: FormCalc Newbie, Time Difference Equation
          joshm2863114 Level 1

          Ok, so I changed it up to the equation below, but now if the difference is calculated between two times on the same day (GMT), it is one minute off. But if the difference is between two times on different 'days' (once again, all times GMT), it is correct. Any advice?


          var startT = ON_DUTY_TIME.formattedValue

          var endT = OFF_DUTY_TIME.formattedValue

          var differenceT = Time2Num(endT, "HH:MM") - Time2Num(startT, "HH:MM")


          $ = Num2GMTime(differenceT, "HH:MM")

          • 2. Re: FormCalc Newbie, Time Difference Equation
            Alijay Level 1



            Im not sure if you still need assistance, but the following worked for me. I found it in an earlier thred and tweaked it a bit. Im also not very familiar with code but I hope it works for you.


            var TimeCount

            var OneMinute = 1/60

            var StartTime=Time2Num(Row3.Start1.formattedValue,"HH:MM")

            var EndTime=Time2Num(Row4.End1.formattedValue,"HH:MM")



            if (StartTime ne null and EndTime ne null) then

                 //if start time is lower than end time.

                      if (Time2Num(Row3.Start1.formattedValue, "HH:MM") < Time2Num(Row4.End1.formattedValue, "HH:MM")) then

                           TimeCount = Abs(Time2Num(Row3.Start1.formattedValue, "HH:MM") - Time2Num(Row4.End1.formattedValue, "HH:MM")) /(60 * 60 * 1000)

                      //If start time is higher than end time


                           TimeCount = 24 - Abs(Time2Num(Row4.End1.formattedValue, "HH:MM") - Time2Num(Row3.Start1.formattedValue, "HH:MM")) /(60 * 60 * 1000)




                 var Minutes = Round(TimeCount / OneMinute)

                 var Hours = 0


                 for i = 0 upto 48 step 1 do

                      if (Minutes >= 60) then

                           Minutes = Minutes - 60

                           Hours = i + 1



                 $ = Concat(Hours,":",format("99",Minutes))