cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
Highlighted
Helper I
Helper I

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

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.

Highlighted
Solution Sage
Solution Sage

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

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.

Highlighted
Helper IV
Helper IV

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

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

Highlighted
Solution Sage
Solution Sage

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

Highlighted
Helper IV
Helper IV

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

@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

Highlighted
Solution Sage
Solution Sage

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

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

Highlighted
Helper IV
Helper IV

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

@Tad17 

 

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

Highlighted
Solution Sage
Solution Sage

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

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.

Highlighted
Helper IV
Helper IV

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

@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]))

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors
Users online (770)