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
A valid Excel time as a decimal number
time_text - A date and/or time in a text format recognized by Excel.
Sometimes, times in Excel appear as text values that are not recognized properly as time. The TIMEVALUE 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_textis 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).
The formulas below show the output from TIMEVALUE:
To display the output from TIMEVALUE as a formatted time, apply a time number format.
Notice that the TIMEVALUE formula in C15 fails with a #VALUE! error, because cell B15 already contains a valid time. This is a limitation of the TIMEVALUE function. If you have a mix of valid and invalid dates, you can use the simple formula below as an alternative:
The math operation of adding zero will cause Excel will try to coerce the value in A1 to a number. If Excel is able parse the text into a proper time it will return a valid time as a decimal number. If the time is already a valid Excel time, adding zero will have no effect, and generate no error.
TIMEVALUE will return a #VALUE error if time_text does not contain time formatted as text.
To get the date, we extract the first 10 characters of the value with LEFT:
LEFT(B5,10) // returns "2015-03-01"
The result is text, so to get Excel to interpret as a date, we wrap LEFT in DATEVALUE, which...
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...
The Excel VALUE function converts text that appears in a recognized format (i.e. a number, date, or time format) into a numeric value. Normally, the VALUE function is not needed in Excel, because Excel automatically converts text to numeric...
The Excel NUMBERVALUE function converts a number in text format to numeric value, using specified decimal and group separators. This function can be used to convert locale-specific values into locale-independent values.