Excel TIMEVALUE function

Summary

The Excel TIMEVALUE function converts a time represented as text into a valid Excel time. It is particularly useful for transforming times formatted as text into numeric values for calculations or data analysis.
Syntax
				
					=TIMEVALUE(time_text)
				
			
  • time_text: A time in text format that Excel can recognize.
Return value
A valid Excel time represented as a decimal number.

How to use

The TIMEVALUE function parses times that appear as text into valid Excel time values. It accepts time in text formats recognizable by Excel, such as “6:45 PM” or “18:45”. If the input time text is in a cell, it must be formatted as text. TIMEVALUE ignores date information in the time text and focuses solely on converting time.

The output from TIMEVALUE is a serial number representing the fraction of a 24-hour day.

Examples

Simple TIMEVALUE
Converting Text Time to Numeric Time:
				
					=TIMEVALUE("11:45 PM")
				
			

Turns the text “11:45 PM” into its numeric time equivalent in Excel.

TIMEVALUE for DateTime String
Extracting Time from a DateTime String:
				
					=TIMEVALUE("2021-12-01 07:30:00")
				
			
Extracts the time “07:30:00” from a complete date-time string and converts it into a numeric time value.
TIMEVALUE for Various Formats
Handling Time in Different Text Formats:
				
					=TIMEVALUE("13:15")
				
			
Converts the time “13:15” (in 24-hour format) into Excel’s numeric time format.

Additional Notes

  • The output of TIMEVALUE ranges from 0 (12:00:00 AM) to approximately 0.9999 (just before 11:59:59 PM).
  • TIMEVALUE returns a #VALUE! error if the time_text does not contain time formatted as text.
  • To accommodate a mix of valid and invalid times, adding zero to a cell (=A1+0) can coerce Excel to interpret the text as proper time.

Related Functions

Excel DATEVALUE function

The Excel DATEVALUE function converts text-formatted dates into serial numbers, enabling calculations and sorting with valid dates.

Excel VALUE function

The Excel VALUE function converts a text string that represents a number into a numeric value, crucial for calculations.

Excel NUMBERVALUE function

The Excel NUMBERVALUE function converts text to a number in a locale-independent manner, essential for processing textual numbers.

Content Navigation