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
Exciting changes

Power BI Community Changes

Check out the changes to the Power BI Community announced at Build.

May 2023 update

Power BI May 2023 Update

Find out more about the May 2023 update.

Kudo Data Story carousel

Data Stories Gallery

Visit our Data Stories Gallery and give kudos to your favorite Data Stories.

Top Solution Authors