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,

  • sgross's avatar
    sgross
    Helper I
    9 years ago

    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'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})
    )
  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      You can also do this in the Query editor:

       

      = Time.Hour([Time])+(Time.Minute([Time)/60)+(Time.Minute([Time])/3600)

       

      No problems with text format.