Forum Discussion
Converting Decimal Hours to Time Format
Is there a nice easy way to convert decimal hours into hours/minutes in Power BI? A function that essentially does this?
For example, turning 3.5 hours into 3 hours and 30 minutes?
I've found a calculation to perform this function in Excel, but it apparently doesn't work for any values over 24.
12 Replies
In your scenario, you can take the integer part for Hours and use decimal part to calculate the Minutes. Populate both fields into TIME() function and format them into a time. Please refer to formula below:
Column = FORMAT(TIME(TRUNC(Table1[Column2],0),(Table1[Column2]-TRUNC(Table1[Column2],0))*60,0),"long time")
Regards,
Hello v-sihou-msft, thanks for your help. I'm completely new to this, so I could use a little more direction. Are you entering those functions using R Script?
Is there any way to achieve the same effect in the Query Editor? Creating a seperate column with the converted values?Hi
I have this case but in reverse. I need to convert a Time value to a Decimal value. In Excel I did not have any problems doing it because I took the "Tiempo Transcurrido (Time Lapsed)" field and multiplied it by 24.
I tried some functions (Time, Value) but I can not find the result. I have tried how to extract each of these values and then convert them into a number but I can not find a function that does it.
If I try to convert that column to Time format, it gives me an error
For me this value of Hour in decimal is very important for the calculations that I need to do.
I would greatly appreciate your help
Thank you
jbolivar wrote:Hi
I have this case but in reverse. I need to convert a Time value to a Decimal value. In Excel I did not have any problems doing it because I took the "Tiempo Transcurrido (Time Lapsed)" field and multiplied it by 24.
I tried some functions (Time, Value) but I can not find the result. I have tried how to extract each of these values and then convert them into a number but I can not find a function that does it.
If I try to convert that column to Time format, it gives me an error
For me this value of Hour in decimal is very important for the calculations that I need to do.
I would greatly appreciate your help
Thank you
Friends,
I have solved this case using Power Query M function
=Number.FromText(Text.BeforeDelimiter([Tiempo transcurrido],":",0)) + (Number.FromText(Text.BetweenDelimiters([Tiempo transcurrido],":",":")))/60 + (Number.FromText(Text.AfterDelimiter([Tiempo transcurrido],":",1)))/3600.
Thank you
If you want to exceed 24 hours, then you need Duration format, like:
Duration.From([DecimalTime]/24)
- Yes, this saved me. I can't believe it was so hard to find this. So many convoluted solutions lol
You might be able to use this DAX pattern, based on seconds rather than hours: https://community.powerbi.com/t5/Community-Blog/Aggregating-Duration-Time/ba-p/22486
Great question! In Power BI, you can handle this by creating a custom column using DAX. For example, if your column is [DecimalHours], you can use:
This will convert 3.5 into 03:30. If you need to handle values over 24 hours, consider avoiding the TIME () function and just stick to string formatting like this. Power Query also works well for such transformations. Hope this helps!
