cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Super User
Super User

Excel time format [h]:mm:ss on power BI

Hi All,

 

Am facing problem in time. can u please anyone help me or guide me .

 

Prob :

My source is Excel.

Duration is filed name

Field value like "15:20:00"  like "HH:MMSmiley FrustratedS"

when am getting the excel in our power bi, the column is automatically converted as 

but i need exact value like same as excel. "31-12-1899 03:20:0"

 

thanks & am waiting for your reply.

 

 

@KHorseman

@ankitpatira

@Sean

@Greg_Deckler

@Seth_C_Bauer

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Excel time format [h]:mm:ss on power BI

@Baskar in the query editor, convert the duration column to a decimal data type. Then after you load the query, go to the table editor and add a column:

 

New Duration = FORMAT(TableName[Duration], "HH:mm:ss")

 

Obviously you could also rename the original duration column something like OldDuration and name the new one Duration so you can still use a column with the same name.

11 REPLIES 11
Super User
Super User

Re: Excel time format [h]:mm:ss on power BI

@Baskar in the query editor, convert the duration column to a decimal data type. Then after you load the query, go to the table editor and add a column:

 

New Duration = FORMAT(TableName[Duration], "HH:mm:ss")

 

Obviously you could also rename the original duration column something like OldDuration and name the new one Duration so you can still use a column with the same name.

manutd Frequent Visitor
Frequent Visitor

Re: Excel time format [h]:mm:ss on power BI

I have the same problem but when i apply the decimal changes in the query editor and then go to add the new column, the decimal column appears with all zero values (but in the query editor it had decimal values)?

manutd Frequent Visitor
Frequent Visitor

Re: Excel time format [h]:mm:ss on power BI

I had the same issue, when i made the change in query editor (to make the column decimal format) it had values i applied thoose changes, but when i when to add a new column in the table area that decimal column only had a value of zero in all that column?

Garry Occasional Visitor
Occasional Visitor

Re: Excel time format [h]:mm:ss on power BI

 

 

This solution dose not work the Excell format [H]:mm:ss returns total hours so if decimall is 2.241967593 it returns 53:48:26

wich is the whole number

the format above only returns the time element to the right of the decimal point

I need to show total Duration in hours

Any ideas

Thanks Garry

ShrikantKhanna Regular Visitor
Regular Visitor

Re: Excel time format [h]:mm:ss on power BI

Hi @Garry,

 

Have you got a solution ?

 

I also had the same requirement. I have to aggregate time for the day, week, month and for any selected time range by slicers. 

There is no straight forward solution in Power BI yet. 

 

I broke down calculation to Hours, minutes and seconds and then represent in format to be able to serve my purpose.

 

MEASURES - 

CALLTIMES = SUM(DURATIONINSECONDS)

HRS = TRUNC([CALLTIMES]/3600)

MINS = TRUNC((([CALLTIMES]/3600)-[HRS])*60,0)

SECS = TRUNC((((([CALLTIMES]/3600)-TRUNC([CALLTIMES]/3600))*60)-[MINS])*60)

TIMING = IF(LEN([HRS])=1,"0"&[HRS],[HRS])&":"&IF(LEN([MINS])=1,"0"&[MINS],[MINS])&":"&IF(LEN([SECS])=1,"0"&[SECS],[SECS])

 

 

fersale Frequent Visitor
Frequent Visitor

Re: Excel time format [h]:mm:ss on power BI

This solution is elegant


But what if you need to apply rounding, how to proceed ????

fersale Frequent Visitor
Frequent Visitor

Re: Excel time format [h]:mm:ss on power BI

This solution is elegant.
But what if you need to apply rounding, how to proceed ????

ShrikantKhanna Regular Visitor
Regular Visitor

Re: Excel time format [h]:mm:ss on power BI

Hi @fersale,

The measured I have mentioned above should round off the numers for you. Else, please share an example so I can tell what can be done.

Highlighted
ShrikantKhanna Regular Visitor
Regular Visitor

Re: Excel time format [h]:mm:ss on power BI

Hi @fersale,

The measured I have mentioned above should round off the numers for you. Else, please share an example so I can tell what can be done.