Duration Conversion

[SURYA3696]

Hello All,

I am having troubles when I try converting a column within my dataset to duration. The column in my dataset records the duration as a total of hours,minutes and seconds (for example; 46:31:38 - where the format is hh:mm:ss). However, when I try converting the datatype of this column to durations, any cell with a value greater than 24 hrs shows up as an error. 

@artemus I know you have solved something similar before. Can you please look into this  and advise. 

[slorin · Accepted solution]

Try

Duration.From(
  (Number.From(Text.BeforeDelimiter([Calculated Duration],":"))*3600 +
   Number.From(Text.BetweenDelimiters([Calculated Duration],":",":"))*60 +
   Number.From(Text.AfterDelimiter([Calculated Duration],":",RelativePosition.FromEnd))
   )/86400)

or

Duration.From(
  List.Sum(
    List.Transform(
      List.Zip(
       {List.Transform(Text.Split([Calculated Duration],":"),Number.From),
       {3600,60,1}}),
    List.Product)
   )/86400))

Stéphane 

[SURYA3696]

Hello @slorin ,

This solution has worked. I have actually only tried the first formula and it worked perfectly. 

Thank you for this mate. 

[slorin]

Hi,

Duration.From((
    Number.From(Text.Start([Calculated Duration],2))*3600 + 
    Number.From(Text.Middle([Calculated Duration],3,2))*60 + 
    Number.From(Text.End([Calculated Duration],2)))
  /86400)

Stéphane 

[SURYA3696]

I am now having an error output for cells with values of the format 103:46:23, can you please help me with modifying the formula to suit such cells as well?