Forum Discussion

sgross's avatar
sgross
Helper I
9 years ago

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...
v-sihou-msft's avatar
v-sihou-msft
Microsoft Employee
9 years ago

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,

jbolivar's avatar
jbolivar
Frequent Visitor
8 years ago

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's avatar
    jbolivar
    Frequent Visitor
    8 years ago
    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

    • MarcelBeug's avatar
      MarcelBeug
      Community Champion
      8 years ago

      An alternative would be:

       

      = List.Sum(
    List.Transform(
        List.Zip({
            Text.Split(
                [Tiempo transcurrido],
                ":"),
            {1,60,3600}}),
        each Number.From(_{0})/_{1})
    )
      • jbolivar's avatar
        jbolivar
        Frequent Visitor
        8 years ago
        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