cancel
Showing results for 
Search instead for 
Did you mean: 
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
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Top Solution Authors