Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Administrator
4 years ago
Solved

Converting Decimal Hour to HH:MM Format

Hi All,

 

I am trying to convert a column which has value stored as decimal hour to hh:mm format.

For eg:
2.78 - 02:46
25.56 - 25:33
Also attached a pbix file for reference. 
Quick help will be appreciated.

Thanks,

  • v-jingzhang's avatar
    v-jingzhang
    4 years ago

    Hi Syndicate_Admin 

     

    Try this column

    Column = 
VAR _hours = INT('Table'[TIMEDECHOURS])
VAR _minutes = ROUND(('Table'[TIMEDECHOURS]-_hours)*60,0)
RETURN
RIGHT("0"&_hours,2)&":"&_minutes

     

    With above code, the column is of Text type. If the hours part is larger than or equal to 100 hours, it would return an incorrect value. You need to modify the hours part in that case. And I used ROUND function for minutes part. You could replace it with ROUNDUP or ROUNDDOWN if needed. 

     

    Best Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as Solution to help other members find it.

4 Replies

  • KNP's avatar
    KNP
    Super User
    4 years ago

    Hi,

     

    I think Duration.From([YourDecimalTime]/24) is what you're looking for.

    See modified attached PBIX.

    Edit: You can't have the HH:MM format (unless you want it as a text column) when your values exceed 24 hours.

     

  • Syndicate_Admin's avatar
    Syndicate_Admin
    Administrator
    4 years ago

    @Syndicate_Admin I couldn't find any attachment. It would be great if there is a DAX formula to resolve this. Is there any possibility to get a calculated column which displays the decimal hours in hh:mm format.

    Thanks again

  • v-jingzhang's avatar
    v-jingzhang
    Community Support
    4 years ago

    Hi Syndicate_Admin 

     

    Try this column

    Column = 
VAR _hours = INT('Table'[TIMEDECHOURS])
VAR _minutes = ROUND(('Table'[TIMEDECHOURS]-_hours)*60,0)
RETURN
RIGHT("0"&_hours,2)&":"&_minutes

     

    With above code, the column is of Text type. If the hours part is larger than or equal to 100 hours, it would return an incorrect value. You need to modify the hours part in that case. And I used ROUND function for minutes part. You could replace it with ROUNDUP or ROUNDDOWN if needed. 

     

    Best Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as Solution to help other members find it.

  • Syndicate_Admin's avatar
    Syndicate_Admin
    Administrator
    3 years ago

    Hi Guys, thanks for the suggested solutions, but I need a return in Time format instead of text.
    I have tried a whole bunch of possibilities online, but no solutions that works.

    Value in the column, for example: 0,1667 => that is 10 minutes

     

    I have added a new column with a formula:
    xx_starting_time_reformat = FORMAT(TIME(TRUNC(KPI_ODOO_account_analytic_line[xx_starting_time],0),(KPI_ODOO_account_analytic_line[xx_starting_time]-TRUNC(KPI_ODOO_account_analytic_line[xx_starting_time],0))*60,0),"long time")

    The formula works great to convert time from decimals to time format, but it won't work when I use it for duration, especially when it is less then 1 hour.

    Error: An argument of function 'TIME' has the wrong data type or the result is too large or too small.