The Excel TIMEVALUE Function
Excel Dates & Times
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.Function Description
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:
TIMEVALUE( time_text )
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.
Interpretation of Time Text Strings
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.
Timevalue Function Examples
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.
|
Formulas:
|
Results:
|
Note that, in the above example spreadsheet:
- The value in cell A6 is a date and time, and so in this case, the Timevalue function has ignored the date part of the value, and just used the time portion.
- The cells in the results spreadsheet all have the general formatting and so display Excel's underlying decimal value for the times.
Formatting Cells to Display Times
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):
Results with time formatting:
| A | |
|---|---|
| 1 | 02:23:00 |
| 2 | 14:23:00 |
| 3 | 14:23:00 |
| 4 | 02:23:00 |
| 5 | 00:02:23 |
| 6 | 02:23:00 |
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.
Timevalue Function Error
If you get an error from the Excel Timevalue function, this is likely to be the #VALUE! error:
Common Error
| #VALUE! | - | Occurs if the supplied time_text cannot be recognised as a valid Excel time. |