MS Excel: TIMEVALUE function for converting text to recognized time format

The TIMEVALUE function in Excel is used to convert a time represented as a text string into a decimal number that Excel recognizes as a valid time. This is essential when you’re working with imported data, manual text entries, or systems that output time as text, and you need that information to behave like real time values in calculations or formatting.

Syntax

=TIMEVALUE(time_text)

Parameters:

Argument	Required	Description
time_text	Yes	A time value in text format, such as "14:30", "2:45 PM"

Only the time portion is interpreted—any date in the text will be ignored.

Examples

Example 1: Convert "2:30 PM" to a Decimal Time

=TIMEVALUE("2:30 PM")

Result: 0.604167

This represents 2:30 PM as a fraction of a 24-hour day.

Example 2: Use with Cell Reference

If A2 = "08:00 AM"

=TIMEVALUE(A2)

Result: 0.333333(Which is 8:00 AM or one-third of a day)

Example 3: Combine with TEXT for Cleanup

Sometimes text time comes with extra spaces or formatting issues.

=TIMEVALUE(TRIM(A2))

Ensures Excel correctly interprets the string as a time.

Example 4: Convert to Readable Time Format

After using TIMEVALUE, format the result as Time to see it displayed like 10:15 AM instead of a decimal.

Sample Table

Input (Text)	Formula	Decimal Result	Formatted Time
"06:00"	=TIMEVALUE("06:00")	0.25	06:00 AM
"12:45 PM"	=TIMEVALUE("12:45 PM")	0.53125	12:45 PM
"23:59"	=TIMEVALUE("23:59")	0.999305	11:59 PM

Important Notes

• If the text doesn’t resemble a valid time, Excel will return a #VALUE! error.

• The function ignores the date portion even if it’s included (e.g., "4/5/2025 14:30" → only time is considered).

• To view the decimal as a readable time, format the cell using Time format.

Related Functions

Function	Description
TIME()	Converts hours, minutes, and seconds into a time
TEXT()	Formats numbers as text
VALUE()	Converts text to number (general)
DATEVALUE()	Converts date text to a serial number
HOUR()	Extracts hour from time
MINUTE()	Extracts minute from time

Summary Table

Feature	Description
Function Name	TIMEVALUE
Purpose	Converts a time string to a time serial number
Argument Type	Text (must resemble a time)
Return Type	Decimal (Excel time)
Volatile	❌ No
Introduced In	Excel 2007 and later

Conclusion

The TIMEVALUE function is a vital tool when working with text-formatted time data in Excel. It ensures accurate time calculations, formatting, and compatibility—especially when importing data from external sources or text files.