Convert text to time

[logstarter]

Hi,

I have a column of time showing like "01-JAN-19 03:23:22.120000 AM", I would like to ask how I could convert this text to time.

Thank you for your help.

 

[Rick Rothstein]

I am not sure why my first formula did not work for you as it worked fine for me. Whatever, does this formula work for you...

=TIMEVALUE(LEFT(A1,18))

IGNORE THIS FORMULA... It does not account for PM time values.

 

[Tetra201]

Or even shorter (the result should be formatted as Time):

=MOD(A1,1)

 

[Dave Patton]

T202009c.xlsm
	A	B	C	D	E
1	01-JAN-19 03:23:22.120000 AM	03:23:22	03:23:22	03:23:22	03:23:22
2	01-JAN-19 03:23:22.120000 PM	15:23:22	15:23:22	15:23:22	15:23:22
3				note 1
4
1b

Cell Formulas
Range	Formula
B1:B2	B1	=--MID(A1,11,99)
C1:C2	C1	=MOD(A1,1)
D1:D2	D1	=--REPLACE(A1,1,10,"")
E1:E2	E1	=IF(RIGHT(A1,2)="AM", TIME(MID(A1,11,2), MID(A1,14,2), MID(A1,17,2)), TIME(MID(A1,11,2)+12, MID(A1,14,2), MID(A1,17,2)))

Each of the suggestions seems to yield the correct answer.
N.B. Format the number as time.
Note 1 Ignore D1:D2 see C1:C2.
What version of Excel are you using?
Did you try the suggestions?