I have series of times in a column that is formatted as 9:30a, 9:40a,
etc. How do I convert these to time, i.e., 9:30 AM, etc.?

thanks

Try a formula of

=TIME(LEFT(A1,FIND(":",A1)-1),MID(A1,FIND(":",A1)+1,2),0)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Edmund Wong" wrote in message
...
I have series of times in a column that is formatted as 9:30a, 9:40a,
etc. How do I convert these to time, i.e., 9:30 AM, etc.?

thanks

Another way:

Select that range of "times"
edit|replace
what: A
with: " AM" (no quotes--just spacebar, AM)
replace all

(and the same with P and PM????)



Edmund Wong wrote:

I have series of times in a column that is formatted as 9:30a, 9:40a,
etc. How do I convert these to time, i.e., 9:30 AM, etc.?

thanks

--

Dave Peterson

Is all of your data AM, or does some of your data look like:

9:30p

In which case:

=TIMEVALUE(IF(RIGHT(A1,1)="a",SUBSTITUTE(A1,"a"," am"),SUBSTITUTE(A1,"p","
pm")))

Then change the cell format to a TIME format.

"Edmund Wong" wrote:

I have series of times in a column that is formatted as 9:30a, 9:40a,
etc. How do I convert these to time, i.e., 9:30 AM, etc.?

thanks