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.
Related Functions:
TIME
DATEVALUE

Basic 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:
 A
1=TIMEVALUE( "2:23 am" )
2=TIMEVALUE( "2:23 pm" )
3=TIMEVALUE( "14:23:00" )
4=TIMEVALUE( "2:23" )
5=TIMEVALUE( "00:02:23" )
6=TIMEVALUE( "01/01/2011 02:23" )
 Results:
 AB
10.099305556- represents the time 02:23:00
20.599305556- represents the time 14:23:00
30.599305556- represents the time 14:23:00
40.099305556- represents the time 02:23:00
50.001655093- represents the time 00:02:23
60.099305556- represents the time 02:23:00

Note that, in the above example spreadsheet:

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):

Excel Format Cells Drop Down Menu on Ribbon With Time Option Selected

Alternatively, if you have an older version of Excel, that does not have the ribbon, use the following steps to change the formatting of one or more cells:

 Results with time formatting:
 A
102:23:00
214:23:00
314:23:00
402:23:00
500:02:23
602:23:00
  • Highlight the cell(s) to be formatted;
  • Right click with the mouse;
  • Select the Format Cells ... option to bring up the 'Format Cells' dialog box.
  • Select the Number tab of the dialog box;
  • Under the Category heading, select the option Time. Select a time format from the list on the right and click OK.

For further details on formatting in Excel, see the Excel Formatting page.


Further information and examples of the Excel Timevalue function are provided on the Microsoft Office website.


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.