TIMEVALUE - can I use cell reference as argument?

[Steve Vincent]

I need to convert a time that I import as text into military time. The time
format is hh:mm:ssPM/AM (in other words, the times in text appear as
01:25:23AM , 02:34:11PM , 11:54:01AM , 03:51:13PM , etc. ).

When I try to use TIMEVALUE, I get the #VALUE! error. It appears that the
argument for TIMEVALUE must be in quotes. I have tried a number of methods
to get TIMEVALUE to recognize the text time, but it never works (I have tried
concatenating quote marks with the cell reference, etc.).

This should be easy (one would think!) -- how can I convert this fairly
straightforward text time into military time? I have searched this forum and
have not found this specific issue addressed. Thanks in advance for any help
you can offer.

Steve

 

[BoniM]

It's the lack of a space before the AM and PM that's messing you up.
If the date is in cell A2, this will fix that problem:
=TIMEVALUE(LEFT(A2,LEN(A2)-2)&" "&RIGHT(A2,2))
Adjust and copy as needed.

 

[bj]

the probelem may be the format it looks like there is no space between the 3
and the A in the 01:25:23AM
try somthing like
=timevalue(substitute(A1,"A"," A"))
you will probably need to embed this in an if statement saying whether you
have an A or P in the data
somehting like
=if(mid(A1,len(A1)-1,1)="A",timevalue(substitute(A1,"A","
A")),timevalue(substitute(A1,"P"," P"))

 

[Teethless mama]

=--SUBSTITUTE(SUBSTITUTE(A1,"AM"," AM"),"PM"," PM")