- Forums
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Power Query
- Mobile Apps
- Developer
- DAX Commands and Tips
- Let's Talk Data
- Custom Visuals Development Discussion
- Community Support
- Community Information
- Community Accounts & Registration
- Using the Community
- Community Feedback
- Training and Consulting
- Dashboard in a Day
- EdX Specific Training Discussion Forum

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- Re: Converting Decimal Hours to Time Format

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

sgross

Regular Visitor

Converting Decimal Hours to Time Format

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-25-2016
05:33 AM

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.

9 REPLIES 9

MarcelBeug

Super User

Re: Converting Decimal Hours to Time Format

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-25-2016
01:33 PM

If you want to exceed 24 hours, then you need Duration format, like:

Duration.From([DecimalTime]/24)

Specializing in Power Query Formula Language (M)

v-sihou-msft

Moderator

Re: Converting Decimal Hours to Time Format

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-27-2016
07:33 PM

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,

Steve_Wheeler

Established Member

Re: Converting Decimal Hours to Time Format

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-27-2016
07:57 PM

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

Regular Visitor

Re: Converting Decimal Hours to Time Format

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-30-2016
08:20 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-29-2017
07:16 AM

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

Frequent Visitor

Re: Converting Decimal Hours to Time Format

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-29-2017
01:01 PM

@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

Highlighted
##

MarcelBeug

Super User

Re: Converting Decimal Hours to Time Format

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-29-2017
01:56 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-30-2017
11:21 AM

@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

Frequent Visitor

Re: Converting Decimal Hours to Time Format

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-12-2018
07:09 AM

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.