Microsoft Excel: Convert Text To Minutes And Seconds

Problem: Someone in another department set up a spreadsheet with hundreds of time values. However, instead of using Excel time formats, they entered each cell as text, as shown in Fig. 506. How can you convert the text entries to real Excel times?

See all Microsoft Excel tips

Strategy: Use the TIME function. This function requires three arguments: hours, minutes, and seconds. The function will handle normal times, such as =TIME(1,23,40), which will return 1:23 a.m. with 40 seconds. It will also handle strange times, such as =TIME(0,123,40), to represent the 123 minutes and 40 seconds, as shown in the first cell above. You will enter the TIME function in a temporary column next to your data. If there is already data in column E, insert a new column E. Select the cells in the new column and format them with the proper custom number format. In this case, you need the [m]:ss format discussed in the preceding chapter, “Total Minutes that Exceed an Hour”. The challenge is then to create a formula that will parse the minutes and seconds from the text entry. You will use a series of nested functions.

• =FIND(“:”,D2) will find and tell you the location of the colon in the text entry. This function, less 1, can be used as the second parameter of the =LEFT function in the next step.

• =LEFT(D2,FIND(“:”,D2)–1) will return just the minutes portion of the entry in D2. This function can be used for the minutes parameter of the TIME function.

• =RIGHT(D2,2) will return just the seconds portion of the entry in D2. This parameter can be used as the seconds parameter of the TIME function.

1) As shown in Fig. 507, enter the following formula in cell E2: =TIM E(0,LEFT(D2,FIND(“:”,D2)–1),RIGHT(D2,2)). This will have Excel return a time with 0 hours, and the proper number of minutes and seconds from the text entry.

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

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. 509. Choose Values and then OK.

Summary: The TIME(Hours,Minutes,Seconds) function is very useful in converting 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 – Numeric; Edit – Paste Special

Functions Discussed: =TIME(); =LEFT(); =RIGHT()