cancel
Showing results for
Did you mean:
sgross Regular Visitor

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.

9 REPLIES 9 Super User

Re: Converting Decimal Hours to Time Format

If you want to exceed 24 hours, then you need Duration format, like:

Duration.From([DecimalTime]/24)

Specializing in Power Query Formula Language (M) v-sihou-msft
Moderator

Re: Converting Decimal Hours to Time Format

@sgross

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,

Steve_Wheeler Established Member

Re: Converting Decimal Hours to Time Format

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

sgross Regular Visitor

Re: Converting Decimal Hours to Time Format

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?

jbolivar Frequent Visitor

Re: Converting Decimal Hours to Time Format

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 Frequent Visitor

Re: Converting Decimal Hours to Time Format

@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 Super User

Re: Converting Decimal Hours to Time Format

An alternative would be:

```= List.Sum(
List.Transform(
List.Zip({
Text.Split(
[Tiempo transcurrido],
":"),
{1,60,3600}}),
each Number.From(_{0})/_{1})
)```
Specializing in Power Query Formula Language (M)
jbolivar Frequent Visitor

Re: Converting Decimal Hours to Time Format

@MarcelBeug wrote:

An alternative would be:

```= List.Sum(
List.Transform(
List.Zip({
Text.Split(
[Tiempo transcurrido],
":"),
{1,60,3600}}),
each Number.From(_{0})/_{1})
)```

Excellent..it worked too

Thank you

Highlighted
lmarsh Frequent Visitor

Re: Converting Decimal Hours to Time Format

Thank you for this, this worked really well.
Is there a way to stop it being a "text" format though as i would like to SUM the output of this.