The Excel TIMEVALUE function is Date & Time formula that returns a time serial number from a specific time that is stored as text. Excel keeps time and date values as numbers, and this function comes in handy when your times are stored as text strings, instead of Excel's time serial number format. In this guide, we’re going to show you how to use the TIMEVALUE function and also throw in some tips and error handling methods.
Supported versions
- All Excel versions
TIMEVALUE Function Syntax
TIMEVALUE(time_text)
Arguments
time_text | Text string that represents a valid time. |
TIMEVALUE Function Examples
=TIMEVALUE("16:30:45")
=TIMEVALUE("12/31/2020 16:30:45")
=TIMEVALUE("4:30:45 PM")
formulas return a serial number for the time 8:30:45 PM.
Tips
- Excel stores date and time as numbers. Excel assumes that the date 1/1/1900 is “1″, and increases this number by 1 for each day. In a similar way, the time values can be anywhere between 0 and 999999999. For example; 6:00 AM is equal to 25. Because of this, the TIMEVALUE function in Excel returns a number that is displayed as a time value.
- VBA has similar function that shares the same name. The TIMEVALUE function in VBA returns a Variant (Date) containing the time.
VBA:
MyTime = TimeValue("16:30:45")
- You can use the DATEVALUE function to return a date serial number from a specific time that is stored as a text string.
Issues
You will get a #VALUE! error under the following conditions:
- If the specified text is a serial number instead of a text representation of a time.
- If the time_text includes minute and second values larger than 59.