Forum Discussion

IPGeorgiev's avatar
IPGeorgiev
Icon for Helper III rankHelper III
6 years ago
Solved

Convert HH:MM:SS to seconds

Hi All,   I have the following column in my table:   434:03:22 434:03:22 167:44:05 167:44:05 155:00:37 155:00:37 140:34:43 140:34:43 119:35:42 119:35:42 97:04:53 ...
Need Help
Tutorial Requests
  • kriscoupe's avatar
    kriscoupe
    6 years ago

    Hi IPGeorgiev,

     

    To avoid having to split the column by delimeter in the case you aren't sure of the length of each string being consistent always you can apply the same methods but using a combination of Text.BeforeDelimeter, Text.AfterDelimeter and Text.BetweenDelimeter M functions.

     

    Number.FromText( Text.BeforeDelimiter( [Time], ":" ) ) * 3600
+
Number.FromText( Text.BetweenDelimiters( [Time], ":", ":", 0, 0 ) ) * 60
+
Number.FromText( Text.AfterDelimiter( [Time], ":", 1 ) )

     

    Hope it helps.

     

    Kris

     

jdbuchanan71's avatar
jdbuchanan71
Icon for Super User rankSuper User
6 years ago

In PowerQuery you can add a custom column like this.

Number.FromText (Text.Start([Time], Text.Length ([Time]) - 6 ) ) * 3600
+
Number.FromText (Text.Start ( Text.End ( [Time], 5), 2)) * 60
+
Number.FromText ( Text.End ([Time] , 2) )

 

This assumes that the minutes and seconds are ALWAYS 2 characters.  If not you would want to split the time by delimeter (:) the do the conversion on each then add them back up.