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

[Baskar]

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:MM:SS"

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

[KHorseman · Accepted solution]

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

[Garry]

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]

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

[Anonymous]

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

[ShrikantKhanna]

Hi @Anonymous,

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.

[Anonymous]

Hi @ShrikantKhanna

I use if to solve this, i create others measures where i roud the number with criterea i need, not beatiful , but usefull

exmples below:

ConsumoMinuto = if ([CONSUMO_SECS]>=30;[ConsumoM]+1;[ConsumoM])

I am work to round a hour now.

Thanks

[Anonymous]

Hi Following version improves measurement to format [h]: mm: ss Consumo Horas = var passo1= (SUM(Realizado[Total Horas]))*60*60 var passo2= TRUNC(passo1/3600) var passo3= ((passo1/3600)-passo2)*60 var passo4= if([TPerfil]=0;0; IF(len(passo2)=1;0&passo2;passo2)&":"& IF(LEN(ROUND(passo3;0))=1;"0"&ROUND(passo3;0);ROUND(passo3;0))) Return passo4 However, this measure has a strange effect on the visuals as it is applied. She repeats items that have no calculation for the measure, I tried several alternatives and none worked. Only and in some cases through the filters it is possible to remove the zeroed items.

[ShrikantKhanna]

Oh I see.

[ShrikantKhanna]

Hi @Anonymous,

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.

[Anonymous]

This solution is elegant

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

[manutd]

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?

[manutd]

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