7 Replies Latest reply on Jul 25, 2016 6:43 AM by Mark Fraser

    Convert string time (ie: 1 hour 30 mins) to time format 01:30/1:30

    Faith Haynes

      We have a table that populates hours and minutes as literal hours and minutes (examples: "1 hour 30 mins", "6 mins", "30 hours", "2 hours 10 mins") - horrible data, I know. I need these times to show as hh:mm. I was able to create 4 additional fields to separate hours and mins into separate columns and account for null values, but am unable to find a calculation to concatenate them and turn them into 01:30, 00:06, 30:00, 02:10. I have to do this for 3 different hours/mins dimensions, so any way to simply this would also be appreciated... Workbook with formulas attached (I'm currently working in v9.3)

       

      Currently, the formulas being used to split the columns are:

      hours:

      IF CONTAINS([Time Spent],"hours")=TRUE

      then TRIM(SPLIT( [Time Spent], "hours", 1 ) )

      ELSEIF CONTAINS([Time Spent],"hour")=true

      then TRIM(SPLIT( [Time Spent], "hour", 1 ) )

      ELSEIF CONTAINS([Time Spent],"h")=true

      then TRIM( SPLIT( [Time Spent], "h", 1 ) )

      END

       

      mins:

      IF (CONTAINS([Time Spent],"mins")=TRUE

      or CONTAINS([Time Spent],"min")=TRUE)

      and (CONTAINS([Time Spent],"hours")=TRUE

      or CONTAINS([Time Spent],"hour")=TRUE)

      then TRIM( SPLIT( [Time Spent], " ", 3 ) )

      ELSEIF (CONTAINS([Time Spent],"min")=TRUE

      or CONTAINS([Time Spent],"mins")=TRUE)

      and (CONTAINS([Time Spent],"hours")=FALSE

      or CONTAINS([Time Spent],"hour")=FALSE)

      then TRIM( SPLIT( [Time Spent], " ", 1 ) )

      ELSEIF CONTAINS([Time Spent],"m")=true

      then TRIM( SPLIT( [Time Spent], " ", 3 ) )

      END

       

      Null Values:

      Hours:

      FLOAT( IIF(ISNULL([Split Time Spent Hours]),"0",[Split Time Spent Hours]))

      Mins:

      FLOAT(IIF(ISNULL([Split Time Spent Mins]),"0",[Split Time Spent Mins]))

       

      Message was edited by: Faith Haynes - new attachment 7/20 @3:43pm.