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

[Anonymous]

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

[v-gizhi-msft]

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

[Anonymous]

Hey @Anonymous 

@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.

[Anonymous]

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

[Anonymous]

Hey @Anonymous 

Check this thread out and let me know if it works: https://community.powerbi.com/t5/Developer/formatting-time-value-to-hh-mm-ss/td-p/85249

[Anonymous]

@Anonymous 

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

[Anonymous]

Hey @Anonymous 

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

[Anonymous]

@Anonymous 

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

[Anonymous]

Hey @Anonymous 

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.

[Anonymous]

@Anonymous 

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

[Anonymous]

Hey @Anonymous 

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]

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.