I need to add space to convert DATE to TIME format

[Guest]

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!

 

[Guest]

custom format B2 as

'mm/dd/yyyy hh:mm AM/PM' (no quotation marks)

HTH

 

[Guest]

'mm/dd/yyyy h:mm AM/PM' (no quotation marks)

custom format B2 as

'mm/dd/yyyy hh:mm AM/PM' (no quotation marks)

HTH

 

[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

 

[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

 

[Guest]

yes. Got it.

Thanks all!
--
Texas Wannano

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

 

[Guest]

That worked. Thanks!
--
Texas Wannano

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

 

[Guest]

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!

 

[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

 

[Guest]

THANKS A MILLION!!!
--
Texas Wannano

Sure

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

should take care of am as well