Excel string to time format

Question

In Excel, what function would I use to convert the string "2012-12-19 12:08 PM PST" in cell A1 to a time format displayed as "13:08" (without using VBA)?

Answer 1

There is not a single Excel function that will do what you want, since your time string is a combination of both the date and the time. It is possible to combine functions, as suggested by ASmith, to get the desired result.

he following formula computes a time value that you can then format as a Time using the "13:30" format.

=TIMEVALUE(MID(A1,SEARCH(" ",A1)+1,SEARCH("M",A1)-SEARCH(" ",A1)))

The MID(...) portion of the formula extracts the time, which is made up of the character following the first space in the input string, through the "M" in "AM" or "PM". The TIMEVALUE function returns the Excel value of the extracted time string, which then can be formatted.

Answer 2

Assuming that you always have PST or some other 3 letter timezone at the end then you can get the time and date by simply removing the last 4 characters, i.e.

=LEFT(A1,LEN(A1)-4)+0

If you want time or date separately you can use MOD and INT respectively on that, i.e. for time

=MOD(LEFT(A1,LEN(A1)-4),1)

and for date

=INT(LEFT(A1,LEN(A1)-4))

In all cases format result cell as time/date as appropriate