Forum Discussion

El_Bastien's avatar
El_Bastien
Frequent Visitor
2 years ago
Solved

Change Time to Duration

I have this Excel spreadsheet with duration values, and when I upload the data to PBI, it changes to time values. Some are right, but as you can see, the big numbers aren't the same. If I change the ...
Need Help
  • mickey64's avatar
    mickey64
    2 years ago

    You need to change the data type of the time value to a decimal.

    Date/Time --> Decimal

    00:41:00    --> 0.02847...day (=41/(60*24))

    0.02847... day = 0.02847...day*(60*24)min/day=41min

     

    227:12:57 --> 9.4673...day (=(227/24)+(12/(60*24))+(57/(60*60*24)))

     

    Power BI cannot display times longer than 24 hours in Date/Time type, so if you want to display it in "hh:mm:ss" format, you need to calculate the hours, minutes, and seconds separately, and then combine the results in text type.

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi El_Bastien ,

I create a table as you mentioned.

Then it will get what you said in the topic.

Next I think you can change your data type.

Finally you will get what you want.

 

 

 

Best Regards

Yilong Zhou

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