I need to add space to convert DATE to TIME format

W

Wannano

A2: 4/03 6:36pm
B2: FORMULA HERE

In B2, I need to convert A2 to 4/3/07 6:36 pm - putting a space between the
time and PM.

Thanks!
 
P

Peo Sjoblom

One way

=LEFT(A2,FIND(" ",A2))+SUBSTITUTE(MID(A2,FIND(" ",A2)+1,255),"pm"," pm")

format custom as mm/dd/yy hh:mm AM/PM
 
Z

Zone

Do you mean you want B2 to have the same information as A2, with a
different format? If so, put =A2 in B2. Select cell B2, then Format
from the menubar, then Cells. On the Number tab, select the Custom
category. In the Type box, type in
m/d/yy hh:mm AM/PM
(leaving a space before AM/PM). I hope I understood your question
correctly. James
 
W

Wannano

yes. Got it.

Thanks all!
--
Texas Wannano


Zone said:
Do you mean you want B2 to have the same information as A2, with a
different format? If so, put =A2 in B2. Select cell B2, then Format
from the menubar, then Cells. On the Number tab, select the Custom
category. In the Type box, type in
m/d/yy hh:mm AM/PM
(leaving a space before AM/PM). I hope I understood your question
correctly. James
Click to expand...
 
W

Wannano

That worked. Thanks!
--
Texas Wannano


Peo Sjoblom said:
One way

=LEFT(A2,FIND(" ",A2))+SUBSTITUTE(MID(A2,FIND(" ",A2)+1,255),"pm"," pm")

format custom as mm/dd/yy hh:mm AM/PM
Click to expand...
 
W

Wannano

I need to incorporate an OR function...the suffix can be either AM or
PM...the formula did not work where there was an am in the cell. There are
too many line to do it individually.

Advice please.

Thank you!
 
P

Peo Sjoblom

Sure

=LEFT(A2,FIND(" ",A2))+SUBSTITUTE(SUBSTITUTE(MID(A2,FIND("
",A2)+1,255),"pm"," pm"),"am"," am")

should take care of am as well
 
W

Wannano

THANKS A MILLION!!!
--
Texas Wannano


Peo Sjoblom said:
Sure

=LEFT(A2,FIND(" ",A2))+SUBSTITUTE(SUBSTITUTE(MID(A2,FIND("
",A2)+1,255),"pm"," pm"),"am"," am")

should take care of am as well
Click to expand...
 
Top