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.

mickey64's avatar
mickey64
Super User
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.