Microsoft Excel: Convert Text To Hours, Minutes, And Seconds

Someone in another department set up a spreadsheet with 100’s of time values. Instead of using Excel time formats, they entered each cell as text with hours, minutes, seconds, and AM or PM (Fig. 510). How can you make the text entries to Excel times?

Strategy:

1) Use the =TIMEVALUE() function. As shown in Fig. 511, this function requires one argument – a text value that looks like a valid time.

2) Do not be alarmed when you hit Enter to accept the formula. Excel will normally display the result as the decimal portion of one day, as shown in Fig. 512.

3) Select the cell. Hit Ctrl+1 to display the Format Cells dialog box. As shown in Fig. 513, on the Number tab, choose an appropriate format from the time section.

As shown in Fig. 514, the result of the formula will now look like a real time.

4) Double-click the Fill handle in cell E1 to copy the formula down to all of the rows with data in column D, as shown in Fig. 515. The Fill handle is the black square dot in the lower right corner of the cell pointer.

Gotcha: Beware; the TIMEVALUE function cannot convert an invalid time. As shown in cell D3 in Fig. 515, someone entered a time with 61 seconds. Although the TIME function could handle 61 seconds, the TIMEVALUE function cannot. Scan through the results looking for #VALUE! errors before changing the formulas to values.

Additional Details: Before you can delete column D, you need to change the times in column E from formulas to values. Highlight the cells in column E. Use Ctrl+C to Copy. Without changing the selection, use Edit – Paste Special to display the Paste Special dialog, as shown in Fig. 516. Choose Values and then OK.

Summary: The TimeValue function can convert text entries to real times. It is critical to have times and dates entered as real Excel times and dates instead of text if you want to do any math with the entries.

Commands Discussed: Format – Cells – Number; Edit – Paste Special

Functions Discussed: =TIMEVALUE()

Microsoft Excel: Convert Text To Hours, Minutes, And Seconds

Share to Facebook