Function Problems

How to Convert Text Such as 12:56 to Time in Excel

Maira Mehtab Sep 22, 2026 869 views

Question details

The user needs to convert text strings resembling times (e.g., "12:56") into recognized time values in Excel, overcoming limitations with standard cell formatting.

Product: Microsoft Excel / WPS Spreadsheet
Device & OS: not provided
Scenario: Cleaning or formatting imported spreadsheet data where time values are mistakenly recognized as plain text.
Observed behavior: Excel treats the value "12:56" as text, preventing standard cell formatting or simple TIMEVALUE functions from converting it correctly, especially if the text contains hidden characters or the duration exceeds 24 hours.

Before you start

Verify if your text cells contain hidden spaces or non-breaking characters by double-clicking the cell before attempting the conversion.

Solution 1Recommended

Use the TIMEVALUE Function for Standard Times

Convert a standard text time string (under 24 hours) into a decimal time value using a built-in formula.

The TIMEVALUE function evaluates a text string formatted as a time and returns a decimal number representing the time of day. This method is highly effective for standard times under 24 hours.

Select an empty cell

Click on an empty cell next to the column containing your text data (for example, B1).

Enter the TIMEVALUE formula

Type the formula =TIMEVALUE(A1) (assuming A1 contains the text "12:56") and press Enter.

Access cell formatting options

Select the cell with the new decimal result, right-click it, and choose 'Format Cells' from the context menu.

Apply time formatting

Under the 'Number' tab, select the 'Time' category and choose your preferred time format to display the decimal as a readable time.

Handle Hidden Characters: If the formula returns a #VALUE! error, hidden spaces may be preventing the conversion. Use the TRIM function inside the formula, such as =TIMEVALUE(TRIM(A1)), to clean the text first.

Solution 2

Parse Hours and Minutes for Durations Exceeding 24 Hours

Use text parsing formulas when dealing with durations like "36:00", which TIMEVALUE incorrectly reads as a time of day.

Manage Data Efficiently

Convert and Format Text to Time Easily in WPS Spreadsheet

WPS Spreadsheet provides powerful data formatting tools and supports all standard Excel functions. It allows you to convert text to time values seamlessly and handles large data sets with ease.

1. Open your dataset: Launch WPS Spreadsheet and open the file containing your text-based time values.
2. Apply the time formula: In an adjacent column, type =TIMEVALUE(A1) or use the custom parsing formula for durations over 24 hours.
3. Format the output: Right-click the result cells, select 'Format Cells', and apply the standard Time format or a Custom [h]:mm format.

Fully compatible with Microsoft Excel formulas including TIMEVALUE, LEFT, and RIGHTIntuitive Format Cells dialog for custom duration formattingBuilt-in text cleaning tools to effortlessly remove hidden spacesFree, lightweight, and user-friendly interface

microsoft office alternative - wps office

Frequently Asked Questions

Why does the TIMEVALUE function return a #VALUE! error?

This error usually occurs if the text string contains unrecognized characters, invisible spaces, or is not typed in a standard time format. You can fix this by nesting the TRIM function, using =TIMEVALUE(TRIM(A1)), to strip away accidental spaces.

How do I add up converted time durations that exceed 24 hours?

After converting your text to time and summing the cells using the =SUM() function, right-click the total cell, select 'Format Cells', go to 'Custom', and enter [h]:mm. The square brackets prevent the spreadsheet from resetting the count at 24 hours.

Can I convert text to time without using formulas?

Yes. Select the column with your text times, navigate to the 'Data' tab, click 'Text to Columns', choose 'Delimited', click next until the final step, and ensure the column data format is set to 'General'. This forces the spreadsheet to re-evaluate the text as actual time data.

Excel & Data Problems