2 Replies Latest reply: May 15, 2014 9:55 AM by Matthijs _ RSS

    Calculated Fields -  Formula Based

    Matthijs _

      Hi there,

       

      I've imported some data thru an ODBC database. It consists the following fields:

      CStart_Year, CStart_Month, CStart_Day, CStart_Hour, CStart_Minute,CStart_Sec[/i]  and also:

      CStop_Year, CStop_Month, CStop_Day, CStop_Hour, CStop_Minute,CStop_Sec[/i]

       

      What I want as a result are three new calculated fields. First one is the start time (hh:mm:ss), second: stoptime (hh:mm:ss) and the third one is the calculated time (Stop - Start).

       

      Does anyone know what kind of formula i have to use?

        • Calculated Fields -  Formula Based
          Grant Perkins

          Hi Matthijs and welcome to the forum.

           

          How complex this becomes will vary according to whether you need to take the DATE into account when calculating the time difference.

           

          If the separate values arriving through the ODBC connection are NUMERIC and the YEAR MONTH and DAY are not part of the calculation, you could simply convert them to seconds, sum the results and then change them back to a time value to get HH:MM:SS.

           

          Take one from the other (sort of) and you will have the answer you require for the time difference shown as the decimal part of a day. So you need to multiply by the number of seconds in a day (86400) to get the result in hh:mm:ss format.

           

          If the values arrive in character type fields or a mixture of numeric and character they need to be converted to numeric before the calculation can take place. The VAL function should do the trick.

           

          If the dates are important then your best option would probably be to define a formula to create a date and time field for each start and stop group and then subtract one from the other giving the result as a numeric time span format field.

           

          This might be messy rather than difficult. But if I cover all of the issues I can think of that are to do with manipulating  incoming  strings which may be numeric or may be text and which all need to be set up as character and then converted to dates and times .. well, it gets complicated to describe with words.

           

          So, do you have numeric or text values coming in from the database, or a mix of the two? (Leading zeros can be the problem if they exist.)

           

          I'll base the rest of the response on the answer to that question.

           

          Grant

          • Calculated Fields -  Formula Based
            Matthijs _

            Hi Grant,

             

            Thank you for your help. All the fields are NUMERIC. I've multiplied the hours and minutes and summed them all together as you said. Then I used a new calculated field with the option: SecondsToChar()[/b] That works really good. Thanks for helping me this way!