Microsoft Office Tutorials and References
In Depth Information
Using TIMEVALUE to Convert Text Times to Real Times
TIMEVALUE to Convert Text Times to Real Times
to Convert Text Times to Real Times
It is easy to end up with a column of text values that look like times. Similar
to using DATEVALUE, you can use the TIMEVALUE function to convert these
to real times.
There are a few examples of text that DATEVALUE cannot recognize.
One common example is when there is no space after the comma. For ex-
ample, “ January 21,2011 ” returns an error. To solve this particu-
lar problem, use Replace to change a comma to a comma space.
The TIMEVALUE function returns the decimal number of the time represented
by a text string. The decimal number is a value ranging from 0 to 0.
999988425925926, representing the times from 0:00:00 (12:00:00 a.m.) to 23:59:59
(11:59:59 p.m.). The argument time_textis a text string that represents a time
in any one of the Microsoft Excel time formats. For example, "6:45 PM" and
"18:45" are text strings within quotation marks that represent time. Date in-
formation in time_textis ignored.
The TIMEVALUE function is difficult to use because it is easy for a person
to enter the wrong formats. In Figure 11.41 , many people would interpret cell
A8 as meaning 45 minutes and 30 seconds. Excel, however, treats this as 45
hours and 30 minutes. This misinterpretation makes TIMEVALUE almost use-
less for a column of cells that contain a text representation of minute and