Use the TIMEVALUE function to convert text to time - Date and Time Functions - Microsoft Excel Tutorials

Use the TIMEVALUE function to convert text to time using Microsoft Excel

In this example, a text string has to be converted to a valid time. Columns A and C contain different start and end times as part of a standardized text string. It is possible to extract the times and convert them to valid time values that can be used as the basis for calculations. To convert text into a valid time, use the TIMEVALUE function. This function returns the decimal number of the time represented by a text string. The decimal number is a value ranging from 0 to 0.99999999, representing the time from 0:00:00 (12:00:00 AM) to 23:59:59 (11:59:59 P.M.).

To extract and convert text to time:

1. Select cells B2:B10 and type the following formula: =TIMEVALUE(MID(A2,8,5)).
2. Press <-Ctrl+Enter->.
3. Select cells D2:D10 and type the following formula:
=TIMEVALUE(MID(C2,6,5)).
4. Press <-Ctrl+Enter->.
5. Select cells B2:B10 and D2:D10.
6. From the Format menu, select Cells.
7. Select the Number tab, click Time under Category, then select the 1:30:55 PM option in the Type box.
8. Click OK.

Use the TIMEVALUE function to convert text to time