Fresh Excel Tips

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.

This tip is compatible with Excel 97, 2000, 2003 and 2007.

1. How to use the TEXT function to extract parts of a date in Excel
2. Using the UPPER function to change text from lower - uppercase
3. How to use the DOLLAR function to convert number to currency
4. Use the LEFT, MID, and RIGHT functions to extract date parts
5. How to Use the FIXED function to round and convert numbers