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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Baskar
Resident Rockstar
Resident Rockstar

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: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

 

1 ACCEPTED SOLUTION
KHorseman
Community Champion
Community Champion

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





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

Proud to be a Super User!




View solution in original post

12 REPLIES 12
KHorseman
Community Champion
Community Champion

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





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

Proud to be a Super User!




 

 

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

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
Not applicable

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

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
Not applicable

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
Not applicable

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.

Oh I see.

 

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
Not applicable

This solution is elegant


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

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?

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

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.