CODEWITHSUNDEEP
exceltime
Ryan
Ryan

Reputation: 11776

Excel string to time format

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)?

Upvotes: 8

Views: 27720

Answers (3)

daino3
daino3

Reputation: 4566

You can use time TIME and then choose your time format.

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

Upvotes: 0

barry houdini
barry houdini

Reputation: 46381

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

Upvotes: 2

chuff
chuff

Reputation: 5876

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.

Upvotes: 7

Related Questions