Converting Decimal Hour to HH:MM Format

[Syndicate_Admin]

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,

[Syndicate_Admin]

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.

[v-jingzhang · Accepted solution]

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]

@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

[KNP]

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.