The TIMEVALUE function

The TIMEVALUE function is a date and time function which converts a time value stored as text into a valid time value. Proper formatting is needed to allow the function to work as intended. To see the default formatting of time in Excel, you can use the NOW function.

This is the format of the TIMEVALUE function:

=TIMEVALUE(TEXT STRING)

Here is an example of the TIMEVALUE function:

=TIMEVALUE(“3:30:24”) = 3:30:24am 

TIMEVALUE function returning a valid time format from a non-formatted value

This function is valuable when treating exported information from an external data source. Exports can often be formatted as text by default causing their values to not perform as expected in Excel. Using the TIMEVALUE function can help alleviate the issue.

An example of a functional implementation of this would be extracting and formatting time values from a third-party report which only provides a single field with both the date and time included. The date of occurrence may not be vital to the specific report being built, but the time is.

ex: The standard output follows this formatting “January 13, 2020 – 15:36:23 …”

=TIMEVALUE(MID(A2,SEARCH(“-“,A2)+2,8))

* Note that the SEARCH takes a +2 as we want to return starting two characters to the right of the ‘-‘, and the number 8 represents the maximum string length of a time value *