Convert Text Value to Time Value in excel

When you have pasted data from an external source (e.g. web pages are horrific for this) into a worksheet and numbers, dates and/or times come in as textual representations rather than true numbers, dates and/or times usually the quickest method is to select the column and choose Data ► Text to Columns ► Fixed Width ► Finish. This forces Excel to reevaluate the text values and should revert the pseudo-numbers into their true numerical values.

If this occurs with formulas pasted into cells that are formatted as text or custom, first set the column to a General format then use Ctrl+H to Find what: = and Replace with: = and again Excel will reevaluate each cell's contents, replacing the text that looks like a formula with an operational formula.

One particular circumstance that might be worthy to note in a general sense is the use of the non-breaking space character (ASCII 160 or 0×A0) common in web pages so that table cells of datetimes will not wrap to a second line. If you have what appears to be a space that you simply cannot get rid of, use Ctrl+H to replace the non-breaking spaces with conventional spaces (or nothing). A non-breaking space can be generated on a keyboard by holding down one of the Alt keys while tapping 0·1·6·0 on the numpad (with Num Lock ON) then releasing the Alt key.

If you have a text value like 8:00 in, for example, cell J42, then put

=TIMEVALUE(J42)

into cell K42 and you will get an Excel date/time value of eight hours.  It will display as 0.33333333 by default (because 8 hours is ⅓ of a day), but you can format it to display as a time value.  And, depending on your needs, you may want to copy column K and paste values over column J, so you get rid of the text values and keep only the date/time values.

TIMEVALUE() does not handle negative values.  If there's a possibility that your data will include negative times, like -8:00 (perhaps because you have time sheet corrections or other adjustments to previously entered data), you need a formula that handles the - explicitly:

=IF(LEFT(J42,1)="-", -TIMEVALUE(RIGHT(J42,LEN(J42)-1)), TIMEVALUE(J42))

For a time like 11:42:47.294 you can use this formula:

=TIME(MID(B13,SEARCH(":",B13,1)-2,2),MID(B13,SEARCH(":",B13,6)-2,2),MID(B13,SEARCH(":",B13,6)+1,6))