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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
MeghnaZutshi
Frequent Visitor

Is there Excel time format 37:30:55 in Power BI

I have one excel file with one field having time data type ( 37:30:55 format). Once i import that file in PBI, the time format gets lost( it doesn't consider time greater than 24). Can i have the data from excel as is in PBI? Any solution for having 37:30:55 format in PBI.

1 ACCEPTED SOLUTION
v-xiaotang
Community Support
Community Support

Hi @MeghnaZutshi 

please try this, change the format to duration,

vxiaotang_0-1669364307579.png

if hh>24, for example, value= 24:30:55, please change it to 1.0.30.55, value= 37:30:55, please change it to 1.13.30.55,

vxiaotang_1-1669364479894.png

then create the measure

total = 
var _total= CALCULATE(SUM('Table'[duration - Copy]),ALLEXCEPT('Table','Table'[name])) *24*3600
var _hh=INT(_total/3600)
var _mm=int(mod(_total -(_hh *3600),3600)/60)  
var _ss=roundup(mod(mod( _total-(_hh *3600),3600),60),0)   
return _hh&":"&_mm&":"&_ss

result

vxiaotang_2-1669364924598.png

Solved: Calculate Duration in hh:mm:ss - Microsoft Power BI Community

 

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
v-xiaotang
Community Support
Community Support

Hi @MeghnaZutshi 

please try this, change the format to duration,

vxiaotang_0-1669364307579.png

if hh>24, for example, value= 24:30:55, please change it to 1.0.30.55, value= 37:30:55, please change it to 1.13.30.55,

vxiaotang_1-1669364479894.png

then create the measure

total = 
var _total= CALCULATE(SUM('Table'[duration - Copy]),ALLEXCEPT('Table','Table'[name])) *24*3600
var _hh=INT(_total/3600)
var _mm=int(mod(_total -(_hh *3600),3600)/60)  
var _ss=roundup(mod(mod( _total-(_hh *3600),3600),60),0)   
return _hh&":"&_mm&":"&_ss

result

vxiaotang_2-1669364924598.png

Solved: Calculate Duration in hh:mm:ss - Microsoft Power BI Community

 

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

I need to perform arithmetic operations like Sum or Average so i cant import it as text.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.