TIMEVALUE

TIMEVALUE is an Excel function that converts text time values into numeric format. This is especially useful when working with imported or manually entered time values that are represented as text.

What does the TIMEVALUE function do

Excel stores date and time as numbers: the integer part represents the date, the fractional part represents the time. The TIMEVALUE function helps convert text like “12:30” into the corresponding fractional number 0.520833, which Excel recognizes as 12 hours 30 minutes.

The result will be displayed as a number representing time as a fraction of 24 hours.

The value cannot equal one or be greater than one. For values of 24:00:00 and above, the time will be taken as the remainder of integer division by 24 hours, i.e., for 24:00:00 – 0, for 36:00:00 – 0.5.

Syntax

=TIMEVALUE(time_text)

• time_text — string containing time in a format that Excel can recognize, such as “14:45”, “7:15:30”.

Examples

• =TIMEVALUE(“12:00”) returns 0.5 — this is half a day, i.e., 12:00 noon.
• =TIMEVALUE(“6:30 AM”) returns 0.270833.
• =TIMEVALUE(“18:00:00”) returns 0.75.

Good to know

• If you see a #VALUE! error, Excel couldn’t recognize the time format. Make sure the text matches a valid pattern.
• You can use TIMEVALUE in combination with DATEVALUE to convert full date and time that are separated in text.
• The result is a decimal number that can be formatted as any standard time format.

Practical applications

• Converting data from external sources where time is stored as text
• Calculating intervals between events, work start and end times, meal times, etc.
• Automating reporting where correct time display is important
• Data cleanup and standardization when working with mixed data formats
• Integration with systems that export time values as text

After applying TIMEVALUE, you can format the cell as time to see the familiar display (e.g., 08:30), even though the cell will contain a fractional number.

Other Date and Time functions in Excel

TIME, YEAR, DATE, DATEVALUE, EDATE, DAY, WEEKDAY, YEARFRAC, EOMONTH, MONTH, MINUTE, WEEKNUM and WEEKNUM.ISO, WORKDAY, DATEDIF, TODAY, SECOND, NOW, HOUR, NETWORKDAYS

This post is also available in RU.