Excel TIMEVALUE Function

Microsoft Excel TIMEVALUE Function

Basic Description

The Excel Timevalue function converts a text representation of a time, into an Excel time.

- ie. 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 are supplied (eg. 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 (eg. "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 several examples of the Excel Timevalue function.

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: A B 1 0.099305556 - represents the time 02:23:00 2 0.599305556 - represents the time 14:23:00 3 0.599305556 - represents the time 14:23:00 4 0.099305556 - represents the time 02:23:00 5 0.001655093 - represents the time 00:02:23 6 0.099305556 - represents the time 02:23:00

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. These values can be displayed as times by changing the cell formatting. To do this:

Highlight the cell(s) to be formatted Right click with the mouse Select the Format Cells ... option and ensure the Number tab is selected Under the Category heading, select the option Time. Select a date format from the list on the right and click OK The resulting formatted spreadsheet is shown on the right. For more details on formatting in Excel, go to the Excel Formatting page.

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

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.