Solved: Converting number to a time again

bsharp · ‎02-09-2021

Can someone help me conver this data to a time?

I need the TrnTime to be in HH:MM:SS:MS

I tried to modify this but I'm not having much luck.

harshnathani · ‎02-09-2021

You can try this

HHMMSS = 
INT(Table[TimeSpent] / 3600) & ":" &
RIGHT("0" & INT((Table[TimeSpent] - INT(Table[TimeSpent] / 3600) * 3600) / 60), 2) & ":" &
RIGHT("0" & MOD(Table[TimeSpent], 3600), 2)

You can also have a look at this article

https://gist.github.com/jongio/5b8dcf13ab957cecfec3932a42db0e58

https://community.powerbi.com/t5/Quick-Measures-Gallery/Milliseconds-Duration/m-p/406698

Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

View solution in original post

VijayP · ‎02-09-2021

@bsharp

Use Column From Example in Power Query and give few examples like 9:10:11:12 and 11:12:10:09 to get the resulte and then change the data type

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
MY Blog || My YouTube Channel || Connect with me on Linkedin || My Latest Data Story - Ageing Analysis

Proud to be a Super User!

harshnathani · ‎02-09-2021

HI @bsharp ,

You can try this

HHMMSS = 
INT(Table[TimeSpent] / 3600) & ":" &
RIGHT("0" & INT((Table[TimeSpent] - INT(Table[TimeSpent] / 3600) * 3600) / 60), 2) & ":" &
RIGHT("0" & MOD(Table[TimeSpent], 3600), 2)

You can also have a look at this article

https://gist.github.com/jongio/5b8dcf13ab957cecfec3932a42db0e58

https://community.powerbi.com/t5/Quick-Measures-Gallery/Milliseconds-Duration/m-p/406698

Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)