As Excel times are stored internally as numeric values, Excel doesn't generally recognise text representations of times as actual times.
Therefore the Timevalue function is provided to convert text representations of times into decimals that Excel can recognise as times.For more information on dates & times in Excel, see the Excel Dates & Times page.
The Excel Timevalue function converts a text representation of a time, into an Excel time.
I.e. the function converts a text string representing a time, into the decimal value that represents the time in Excel.
The syntax of the Timevalue function is:
where the time_text argument is a text string representing a time. Within this text string, the hours, minutes and seconds should be separated by colons.
If just two values (separated by a colon) are supplied (e.g. 02:54), this will be treated as hours and minutes, not minutes and seconds. If you want to represent 2 minutes and 54 seconds, this must be supplied with the hour specified as zero (e.g. "00:02:54").
Note also that, if the time_text argument contains a date and time, the Timevalue function ignores the date part of the text string.
These rules are illustrated in the examples below.
The following spreadsheet shows the Excel Timevalue function used to convert text representations of times into decimal values that can be understood as times in Excel.
Note that, in the above example spreadsheet:
If the results of your Timevalue function are displayed as a simple number (as in the example above), and you want Excel to display the results as times, this can be done by formatting the cells as times.
The easiest way to do this is to select the cell(s) to be formatted and then select the Time option from the drop-down menu in the ribbon. This is found in the 'Number' group on the Home tab (see below):
The resulting formatted spreadsheet is shown on the rightabove.
For further details on formatting in Excel, see the Excel Formatting page.
See the Microsoft Office website for further information and examples of the Excel Timevalue function.
If you get an error from the Excel Timevalue function, this is likely to be the #VALUE! error:
|#VALUE!||-||Occurs if the supplied time_text cannot be recognised as a valid Excel time.|