cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sgross Helper I
Helper I

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.

10 REPLIES 10
MarcelBeug Community Champion
Community Champion

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 Solution Sage
Solution Sage

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 Helper I
Helper I

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.

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

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

MarcelBeug Community Champion
Community Champion

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

lmarsh
New Member

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.

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors