How to quickly convert time to text or number of hours/minutes/seconds in Excel?

When managing data in Excel, it's common to work with time values formatted as hh:mm:ss or similar notations. However, for further analysis, reporting, or exporting purposes, you might need to convert these time values into plain text strings, their underlying number strings, or calculate the equivalent number of decimal hours, minutes, or seconds. Efficiently converting between these formats saves time, reduces manual effort, and ensures data consistency across calculations or summaries. This tutorial will introduce a variety of practical solutions to convert time values to text, numerical formats, or decimal representations in Excel, covering both built-in options and flexible methods for batch processing.

Convert time to text string

Convert time to number string

Convert time to number of hours/minutes/seconds

Paste Special & Multiply alternative method

Pivot Table for time data aggregation and conversion

Convert time to text string

To present the time values as text strings—for example, for exporting, reporting, or simply displaying the times without Excel’s time formatting—you can use the built-in TEXT formula. This approach is helpful when you want the cell values to remain unchanged regardless of any future cell formatting, and for concatenating times with other text data.

Select the cell where you want to display the converted text string. Enter the following formula (assuming your original time is in cell A1):

=TEXT(A1,"hh:mm:ss AM/PM")

After entering the formula, press Enter. To apply the same operation to multiple cells, drag the fill handle down along the adjacent cells.

Parameter notes: You can customize the format string ("hh:mm:ss AM/PM") to suit 24-hour or 12-hour formats, or display only certain components (such as "hh:mm").

Tip: This conversion is best when you want the time to remain visually unchanged regardless of how the cell is formatted later. Be aware that after conversion, the output is text and no longer suitable for direct time calculations.

Convert time to number string

Excel stores time values as fractions of a day, so "1" represents 24 hours, and "0.5" represents 12 hours. To display the underlying number string corresponding to a time value, simply change the cell’s formatting to General. This can be useful for understanding how Excel stores time internally, or prepping values for further calculations.

Select the time cells you want to convert to their numeric representation.

Go to the Home tab, locate the Number group, and select General from the format dropdown list. After changing the format, the cell will display the underlying numeric value of the time.

For example, "06:00:00" converts to 0.25, "18:00:00" to 0.75.

Tip: Since Excel expresses times as fractions, a full 24-hour day is 1. To convert these numeric strings into meaningful time units (such as hours, minutes, or seconds), see the following methods.

Caution: Changing cell format only affects display, not the actual stored value. When copying data for use outside Excel, remember that "General" formatted time values may appear as decimals.

Convert time to number of hours/minutes/seconds

If you need to perform calculations, aggregate durations, or export time as decimal hours/minutes/seconds, it’s necessary to convert time directly into those units. Below are several ways to achieve this, including formula methods and easy batch processing with Kutools for Excel.

Method 1: Convert time to number of hours/minutes/seconds with formula
This solution is suitable for quick conversions of a time value in a single cell.

Tip: If you have whole hour values and want the output as integers, use functions like ROUND(A1*24,0), INT(A1*1440), etc.

Precaution: Large time values that span over 24 hours (e.g., durations calculated as end-start time where end < start) may cause negative or unintended results due to how Excel handles time rollovers.

Method 2: Convert time to number of hours/minutes/seconds with Kutools for Excel

Paste Special & Multiply alternative method

This method is very useful when you want to quickly convert time values to their equivalent decimal hours, minutes, or seconds for multiple cells, especially if you want to avoid formulas or have static values for further use. It leverages Excel’s Paste Special with multiplication, which transforms the displayed time into a true decimal value in just a few steps.

How to use:

1. Enter the conversion factor (for example, 24 for hours, 1440 for minutes, or 86400 for seconds) in a blank cell.
2. Copy the conversion factor cell (Ctrl+C).
3. Select the range of time cells you want to convert.
4. Right-click the selection > choose Paste Special > in the dialog, select Multiply, then click OK.

For example, to convert time to decimal hours, enter 24 in a blank cell, copy it, then use Paste Special > Multiply on your time data. Each time value will be multiplied by 24 and displayed as a decimal hour value.

Advantages: Produces static results and does not rely on formulas; especially useful for preparing data for export or printing. Suitable for batch conversion of large datasets.

Limitations: The original time data will be overwritten. Consider making a backup if the original time format needs to be preserved.

Pivot Table for time data aggregation and conversion

If you need to summarize time data across different categories (e.g., employee work hours by project or machine operating times per day), a Pivot Table can help you both convert and aggregate time values efficiently. Although Pivot Tables display time values by default (in the hh:mm format), you can summarize and convert the output to hours/minutes/seconds as needed.

How to use:

1. Prepare your data with a column of time values (formatted as hh:mm:ss) and corresponding categories or groups.
2. Select your data range, go to Insert > PivotTable.
3. Drag the categorical field into Rows and the time field into Values.
4. The time values will be summed or averaged in time format. To show the total as decimal hours/minutes/seconds, right-click the total field > Value Field Settings > Summarize Value Field as SUM or AVERAGE, then apply a custom number format or multiply by 24 (hours), 1440 (minutes), or 86400 (seconds) in additional helper columns.

Using Pivot Tables for time aggregation is ideal when analyzing time data grouped by various dimensions (such as daily totals per employee, project, or machine) and when you want flexible summary formats. The results can be exported, formatted for presentation, or used for further data analysis.

Advantages: Highly suitable for grouping and aggregating large volumes of time data; built-in filtering and summary tools streamline reporting.

Limitations: For direct decimal conversion of PivotTable totals, you may occasionally need helper columns or calculated fields. Pivot Tables don’t modify original data but present it in a summarized and optionally converted layout.

Quickly and easily convert date to other date formatting in Excel

Have you ever tried to convert a date to day, month or year only? The formulas maybe hard to remember, but the Apply Date Formatting of Kutools for Excel can quickly convert a standard date to the date formatting as you need as below screenshot shown. Dont, waite, click for 30 days free trial!

Kutools for Excel: with more than 300 handy Excel add-ins, free to try with no limitation in 30 days.