4 Replies Latest reply: May 15, 2014 10:09 AM by Data Kruncher RSS

    Time Conversion

    chickenjoy _

      Hi!

       

      Can you help me how will I be able to convert this data:

       

      4:52

      34:53

      53:10

      5:02

       

      **The above is a character field.

       

      Into this...

       

      00:04:52

      00:34:53

      00:53:10

      00:05:02

       

       

      Thanks for all your help! I am on 10.5 Pro.

       

       

      Chickenjoy

        • Time Conversion
          joey

          Do you want the output format to be a string or a date/time field? ALso, will you ever have hours or will it always be minutes and seconds.  There's a few extra steps either way.

           

           

          I'd tackle this with RSplits on the ':', and check the length of the first, second, and third parts adding leading zeros if needed.  Then add them back together with ':' in between.

            • Time Conversion
              Data Kruncher

              Joey's got this one defined perfectly.

               

              "00:"+

              Str(Val(LSplit(RptTime,2,":",1)),2,0,"0")+

              ":"+

              Str(Val(LSplit(RptTime,2,":",2)),2,0,"0")[/code]Like the input field, this is a Character field.

               

              Edit: I suppose that the second Str(Val(LSplit is a bit needless as the zero padding already exists...

                • Time Conversion
                  chickenjoy _

                  Joey's got this one defined perfectly.

                   

                  "00:"+

                  Str(Val(LSplit(RptTime,2,":",1)),2,0,"0")+

                  ":"+

                  Str(Val(LSplit(RptTime,2,":",2)),2,0,"0")[/code]Like the input field, this is a Character field.

                   

                  Edit: I suppose that the second Str(Val(LSplit is a bit needless as the zero padding already exists...[/QUOTE]

                   

                  This worked perfectly! However, I would like to include this to my summary and perform a sum of all its values. Which means that the sum of...

                   

                  00:04:52

                  00:34:53

                  00:53:10

                  00:05:02

                   

                  should be =     00:97:17

                   

                  A summary will not work because the field property is Character. how can I solve this???

                    • Time Conversion
                      Data Kruncher

                      That's an unusual presentation. Are you considering the first segment to be the number of days? Or is hh:mm:ss acceptable, meaning that 00:97:17 (should be 00:97:57?) is equivalent to 01:37:57?

                       

                      If the formula given above is for a Character field named PreTime, then HMSTime is a Numeric (not date/time), with the format Time span applied, and uses the formula:

                      CharToSeconds(PreTime)[/CODE]

                       

                      Summarizing HMSTime gives the total 01:37:57.

                       

                      Is this acceptable?