cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sgross Regular Visitor
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
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)
Moderator 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")

99.PNG

 

 

Regards,

Steve_Wheeler Established Member
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
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
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.

TiempoTranscurrido Excel_SLA.JPG

 

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

 

TiempoTranscurrido.JPG

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
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.

TiempoTranscurrido Excel_SLA.JPG

 

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

 

TiempoTranscurrido.JPG

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

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

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