Forum Discussion
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 values to Text, the info is not right.
I want to have the same values as in Excel.
Any ideas?
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.
2 Replies
- mickey64
Super User
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.
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.
