Convert Text field to duration assuming 10 hours in a day.

[Anonymous]

Hello,

I am trying to convert an incoming text column in this format - "hh:mm:ss" to a duration column with "dd.hh:mm:ss" format.

These are the constraints i need to work with -

1. The hours(hh) part of the string exceeds 23 hrs; ex - "239:29:46" (239 hours; 29 min; 46 sec). This will need to be handled - I am currently using - #duration(0, Int64.From(parsed{0}), Int8.From(parsed{1}), Int8.From(parsed{2})) to parse these chunks and convert to type duration.

2. The above formula assumes 24 hours in a day to handle this conversion, so the output will be(for the above case) "9.23:29:46"(approx). However I want to assume only 10 hours in a day, so the output will need to be "23.9:29:46".

Is there a way to do this using power query?

Any help is much appreciated.

Thanks,

-K

[mahoneypat · Accepted solution]

Here is one way to do it.  Put this formula in the pop-up box when you add a custom column, substituting your column name in place of Column1.

let
templist = List.Transform(Text.Split([Column1], ":"), each Number.FromText(_))
in
#duration(Number.RoundDown(templist{0}/10),Number.Mod(templist{0},10), templist{1}, templist{2})

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Anonymous]

Hi @mahoneypat 

This seemed to do the trick. Will this also work for 8 hour days if i just switch out the 10's with 8's?

[mahoneypat]

It should work with 8s too.

Regards,

Pat