Need help with date & time formula

[Jane]

Can anyone help me create a formula to convert the following date and time to:

12102004 5:00 pm ------> 12/10/2004 5:00 pm


thanks

 

[JE McGimpsey]

One way:

=TEXT(LEFT(A1,8),"00\/00\/0000")+MID(A1,10,255)

 

[Peo Sjoblom]

Select the column, do data>text to columns
click fixed width, click next, remove the line between 5.00 and pm by double
clicking it, click finish
That will give you one column with the date and one with the time
now select the column with the date, repeat but do not select any delimiter
just click next twice, under column data format select date and MDY, click
finish

Now in a third column do A1+B1 and format it as dd/mm/yy hh:mm


If you need a formula, this might work (not tested extensively, it should
work as long as the days and the years are 2 respectively 4 digits, months
can be one or two digits for Jan-Sep)

=DATE(RIGHT(LEFT(A2,FIND(" ",A2)-1),4),MID(LEFT(A2,FIND("
",A2)-1),LEN(LEFT(A2,FIND(" ",A2)-1))-5,2),LEFT(LEFT(A2,FIND("
",A2)-1),LEN(LEFT(A2,FIND(" ",A2)-1))-6))+TRIM(MID(A2,9,255))


Regards,

Peo Sjoblom

 

[Peo Sjoblom]

Doh!

Regards,

Peo Sjoblom

One way:

=TEXT(LEFT(A1,8),"00\/00\/0000")+MID(A1,10,255)

 

[Peo Sjoblom]

Change the formula to

=DATE(RIGHT(LEFT(A1,FIND(" ",A1)-1),4),MID(LEFT(A1,FIND("
",A1)-1),LEN(LEFT(A1,FIND(" ",A1)-1))-5,2),LEFT(LEFT(A1,FIND("
",A1)-1),LEN(LEFT(A1,FIND(" ",A1)-1))-6))+TRIM(MID(A1,FIND(" ",A1),255))

Regards,

Peo Sjoblom

 

[Jane]

Thank you!!!! that worked great!!!

Doh!

Regards,

Peo Sjoblom