The Excel TIMEVALUE function converts a time represented as text into a proper Excel time. For example, the formula =TIMEVALUE(“9:00 AM”) returns 0.375, the numeric representation of 9:00 AM in Excel’s time system. Numeric time values are more useful than text since they can be directly manipulated with formulas and pivot tables.

Get a valid time from a text string

Return value 

A valid Excel time as a decimal number

Syntax 

=TIMEVALUE (time_text)

Arguments 

  • time_text – A date and/or time in a text format recognized by Excel.

Uses  

Sometimes, times in Excel appear as text values that are not recognized properly as time. The TIME VALUE function is meant to parse a time that appears as a text value into a valid Excel time. A native excel time is more useful than text because it is a numeric value that can be formatted as time and directly manipulated in a formula.

The TIMEVALUE function takes just one argument, called time_text. If time_text is a cell address, the value in the cell must be text. If time_text is entered directly into the formula it must be enclosed in double quotes (“”). Time_text should be supplied in a text format that Excel can recognize, for example, “6:45 PM” or “18:45”. TIMEVALUE ignores dates if present in a text string. 

The TIMEVALUE function creates a time in serial number format from a date and/or time in an Excel text format. TIMEVALUE will return a decimal number between 0 and 0.99988426, representing 12:00:00 AM to 11:59:59 PM. Because the maximum value returned by TIMEVALUE is less than 1, hours will reset every 24 hours (like a clock).

Examples

The formulas below show the output from TIMEVALUE:

=timevalue(“12:00”) // returns 0.5

=timevalue(“12:00 PM”) // returns 0.5

=timevalue(“18:00”) // returns 0.75

To display the output from TIME VALUE as a formatted time, apply a time number format.

0
Would love your thoughts, please comment.x
()
x