Convert Power BI Time Numbers to Excel Time Formats
Question details
The user needs to convert time values exported from Power BI, which are represented as total seconds, into standard Excel time formats such as hh:mm:ss, total minutes, or decimal hours.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Importing or exporting data from Power BI to Excel where time duration translates into raw numerical seconds.
- Observed behavior
- The exported time data appears as a large integer representing total seconds (e.g., 11520) instead of a readable time duration.
Identify the cell containing your exported Power BI time number and ensure it is formatted as a General or Number value before applying conversion formulas.
Convert Total Seconds to hh:mm:ss Format
Use the TIME function combined with INT and MOD to extract hours, minutes, and seconds from the raw total seconds value.
This method breaks down the raw seconds into standard time units. By dividing the total seconds and using the INT function, we calculate the hours and minutes, while the MOD function captures the remaining seconds.
Click on a blank cell where you want the formatted time to appear (for example, cell B1 if your raw data is in A1).
Type the formula =TIME(INT(A1/3600),INT((A1/60)-INT(A1/3600)*60),MOD(A1,60)) into the formula bar and press Enter.
Right-click the cell containing the formula result, select Format Cells, navigate to the Custom category, type [h]:mm:ss or hh:mm:ss in the Type field, and click OK.
Calculate Total Minutes or Decimal Hours
Use basic arithmetic division to quickly convert total seconds into continuous minutes or decimal hours for numerical reporting.
Process Exported Data Easily with WPS Spreadsheet
WPS Spreadsheet fully supports Excel's advanced time formulas, including TIME, INT, and MOD. You can easily process your exported Power BI data, apply custom time formatting, and handle large datasets smoothly.
- 1. Open your dataset in WPS: Launch WPS Spreadsheet and open the file containing your exported Power BI time numbers.
- 2. Apply the conversion formula: Click a blank cell next to your data and input =TIME(INT(A1/3600),INT((A1/60)-INT(A1/3600)*60),MOD(A1,60)).
- 3. Customize the time format: Right-click the formula result, select Format Cells, navigate to Custom, and apply the [h]:mm:ss format to display the time correctly.
Frequently Asked Questions
Why does my converted time show as a decimal instead of hours and minutes?
If your formula result displays as a decimal (e.g., 0.1333), Excel or WPS Spreadsheet is showing the underlying serial number for that time. You need to right-click the cell, select 'Format Cells', and apply a Time or Custom format like hh:mm:ss.
What is the difference between hh:mm:ss and [h]:mm:ss formatting?
The hh:mm:ss format strictly reflects a time of day and resets to 0 after 24 hours (a 25-hour duration will display as 01:00:00). The [h]:mm:ss format allows hours to exceed 24, making it ideal for displaying cumulative total durations.
Do I need to convert the exported seconds into text first?
No, the exported value from Power BI is already a numeric representation of total seconds. You can apply mathematical formulas directly to it without using any text conversion functions.
