Forum Discussion
Solved
Duration Conversion
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.
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
4 Replies
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
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?
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
