Need help with date & time formula
 

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

One way:

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


In article ,
"Jane" wrote:

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

Select the column, do datatext 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


"Jane" wrote:

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

Doh!

Regards,

Peo Sjoblom

"JE McGimpsey" wrote:

One way:

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


In article ,
"Jane" wrote:

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

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

"Peo Sjoblom" wrote:

Select the column, do datatext 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


"Jane" wrote:

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

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

"Peo Sjoblom" wrote:

Doh!

Regards,

Peo Sjoblom

"JE McGimpsey" wrote:

One way:

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


In article ,
"Jane" wrote:

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