Excel string format by time

Question

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

Answer 1

There is more than one Excel function that will do what you want, since your time line is a combination of date and time. To obtain the desired result, you can combine the functions proposed by ASmith.

following the formula, he calculates the time value, which you can then format as time using the format “13:30”.

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

Part of the formula MID (...) of the formula retrieves the time consisting of the character following the first space in the input line, through "M" in "AM" or "PM". The TIMEVALUE function returns the Excel value of the extracted time string, which can then be formatted.

Answer 2

Assuming you always have PST or some other 3-hour time zone at the end, you can get the time and date by simply deleting the last 4 characters, i.e.

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

If you want the time or date separately, you can use MOD and INT, respectively, that is, for time

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

and for date

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

In all cases, the formatted result cell as the time / date corresponding to

Answer 3

You can use the TIME time, and then select the time format.

 TIME(HOUR(A1), MINUTE(A1), SECOND(A1))