Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
bryanc78
Helper IV
Helper IV

h:mm:ss excel format converting to date/time in PBI when over 1 hour

I have PBI pulling in an excel spreadsheet and one of the colums is in h:mm:ss format.  The column is used to show how long someone is doing something.

 

It works when the time is under 1 hour but when it's over an hour, it starts to show date/time instead.  I've tried transforming it to several formats and nothing worked.

 

Any ideas on how to show just the time?

 

Looking at the below picture, the first one with the date, I want to to look like the others below it - 1:19:44  With no date in front

 

Capture.PNG

 

11 REPLIES 11
v-gizhi-msft
Community Support
Community Support

Hi,

 

Please change this column data type to 'time' and then create a calculated column:

column = Format('Table'[TimeTaken],"h:mm:ss")

 

Best Regards,

Giotto Zhi

Tad17
Solution Sage
Solution Sage

Hey @bryanc78 

 

@Adescrit is correct that you can do this in modelling in the format tab. However, I recommend doing it in the Query editor in the "Transform" tab.

Did not work the way I need it to

 

Anything under 1 hour, it's showing as Error.  Everything over 1 hour, it's showing as time.

 

For example, 1 hour and 15 minutes, which is how long it took someone to work on their project is now showing as 1:15AM

 

I need it to stay as h:mm:ss  So if they worked 4 hours and 15 minutes, I need 4:15:00, not 4:15AM or PM

@Tad17 

 

No as that column is in seconds but it did lead me to another post where I found this:

 

Custom = #duration(0, Time.Hour([Time in Project]), Time.Minute([Time in Project]), Time.Second([Time in Project]))

 

However, this fixes the ones that are over an hour (though I don't like that leading 0) but errors for those under an hour are now occurring

 

Capture1.PNG

Hey @bryanc78 

 

I agree the leading 0's are not ideal. To solve the Errors, just embed an IF statement to make the hours 0 or leave out the hour.

 

Also, have you tried using FORMAT in DAX? https://docs.microsoft.com/en-us/dax/format-function-dax

@Tad17 

 

I need the hours.  How would the IF statement look?

Hey @bryanc78 

 

Check out this link for IFERROR function: https://docs.microsoft.com/en-us/dax/iferror-function-dax

 

Put the IFERROR around your time.hour to return 0 if time.hour errors.

@Tad17 

 

It's not working unless my sytax is the issue

 

#duration(0, IFERROR(Time.Hour([Time in Project],0)), Time.Minute([Time in Project]), Time.Second([Time in Project]))

Hey @bryanc78 

 

Check out the link below: https://community.powerbi.com/t5/Desktop/Duration-format/td-p/447543

 

I'm not sure if what you are trying to accomplish is possible. The general workaround is to convert your times to seconds and then convert to HH:MM:SS. Aside from this and the article in the thread above I don't think there is another solution.

Adescrit
Impactful Individual
Impactful Individual

Select the column or measure. Go to the "Modelling" menu at top of Power BI desktop. Here there should be options to choose the format of the selected column or measure. One of the options (along with "Text", "Whole Number" etc.) should be "Time".

 

If you select this option it should format the whole column or measure as a time, excluding the date from your view.


Did I answer your question? Mark my post as a solution!
My LinkedIn

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.