Duration conversation 00:60:54 to 01:00:54

[AleksP]

Hi All, 

I have the following issue. 

Data source does not convert to hours when time is in 60min like this example:
00:60:54 - which is 60mins and 54sec .. and when I chagne type = Duration I got an error. 

Any ideas how to convert 0:60:** to 1:00:** ? It should be 1:00:54

Same happens when I have 60 seconds but I have deal with using replace values "00:00:60" = "00:01:00" 

If the minutes are above 60 data source is counting as 1 hour but if is in 60min ... 😕

[AleksP]

Thank you ALL !!! 

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

That one worked flawlessly :)) Have a good day ahead. Thank you! 

[Anonymous]

It's because you have "0:" in there somewhere. Try replacing those Text.Start, .Middle, and .End with Text.BeforeDelimiter, .Between Delimiters, and .AfterDelimiter, using the ":" as the delimiters for each parameter (add 1 for the optional Index parameter in Text.AfterDelimiter though!).

Make that replacement in @slorin 's formula and it will work.

--Nate

[slorin · Accepted solution]

In your first message :  "Duration conversation 00:60:54 to 01:00:54"

but you have just one 0 : "0:28:07"

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

 or

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

 Stéphane 

[slorin]

Hi

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

Stéphane

[AleksP]

Many thanks for quick reply but maybe I have to change the format before adding this custom column? 

Below is before change type to Duration

And here is after change type to Duration