Multiple Cols Convert Txt HHH:MM:SS to Duration (Hrs)

[SM4437]

Hi I have multiple colums for txt HHH:MM:SS and i'd like to convert to Duration (Hours), any help would be appreciated.

[amitchandak · Accepted solution]

@SM4437 , You need to deal it with in calculation, hour will not display more than 24 hours

Best it is to split that in-power query into 3 columns

Duration
https://radacad.com/calculate-duration-in-days-hours-minutes-and-seconds-dynamically-in-power-bi-usi...
https://social.technet.microsoft.com/wiki/contents/articles/33644.powerbi-aggregating-durationtime-i...
https://www.pbiusergroup.com/communities/community-home/digestviewer/viewthread?GroupId=547&MessageK...
https://community.powerbi.com/t5/Quick-Measures-Gallery/Chelsie-Eiden-s-Duration/m-p/793639#M389

[amitchandak · Accepted solution]

@SM4437 , You need to deal it with in calculation, hour will not display more than 24 hours

Best it is to split that in-power query into 3 columns

Duration
https://radacad.com/calculate-duration-in-days-hours-minutes-and-seconds-dynamically-in-power-bi-usi...
https://social.technet.microsoft.com/wiki/contents/articles/33644.powerbi-aggregating-durationtime-i...
https://www.pbiusergroup.com/communities/community-home/digestviewer/viewthread?GroupId=547&MessageK...
https://community.powerbi.com/t5/Quick-Measures-Gallery/Chelsie-Eiden-s-Duration/m-p/793639#M389

[SM4437]

Thanks, does that mean i need to split all colums? in Excel you can format [H],MM,SS to show more that 24hrs, can you not do this in Power BI?

[RicoZhou · Accepted solution]

Hi @SM4437 ,

According to your screenshot, I think your columns should be in text data type due to your hour is larger than 24. So we couldn't use Hour() to get hour value. Here I suggest you to use Split function to get hour value before first ":".

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.