Converting a general format to time

Copper Contributor

When we export data from Power BI it converts the hr:min:sec into a general number, for example if it was 1:15:20 it would export to 11520. Any advice on how to get this to format back into hr:min:sec or possibly have it converted into one of the following: total seconds, total minutes, or convert the total time to a decimal.



2 Replies

To convert the exported general format (e.g., 11520) back into hh:mm:ss format, total seconds, total minutes, or decimal hours in Excel, you can use various formulas or functions.

  1. Convert to hh:mm:ss Format: You can use the TIME function in Excel to convert the total seconds back into hh:mm:ss format.

    =TIME(INT(A1/3600), INT((A1/60)-INT(A1/3600)*60), MOD(A1, 60))
  2. Convert to Total Seconds: Total seconds are already given. If you want to leave them as they are, no conversion is needed.

  3. Convert to Total Minutes: To convert the total seconds to total minutes, you can divide the number by 60.

  4. Convert to Decimal Hours: To convert the total seconds to decimal hours, divide the number by 3600 (number of seconds in an hour).


These formulas assume that the exported data is in cell A1. Replace A1 with the appropriate cell reference containing your data.

Once you've entered these formulas, you can format the cells as needed to display the values in the desired format (e.g., hh:mm:ss for time, or general format for total seconds, total minutes, or decimal hours).



This formula from bullet point #1 returned a value of 7:50:19 PM, but the original Power BI read 7:14:19.