Date and Time text into value

[namluke]

Hi, in column A i have a date and time in a text format.

Im looking for a formula to put them into a value

So in column B i would like just the date and column C i would like just the start time.

here is the format

Jan 5, 2015 5:00:00 PM - 5:59:59 PM

but i would like it like this

01/05/15 17:00

[James McEwan]

Hi, in column A i have a date and time in a text format.

Im looking for a formula to put them into a value

So in column B i would like just the date and column C i would like just the start time.

here is the format

Jan 5, 2015 5:00:00 PM - 5:59:59 PM

but i would like it like this

01/05/15 17:00

Please find attached.

EF - namluke 17 07 2015.xlsx

Regards,

J.

[Ace_XL]

Do this..

Select your range of data in Column A

Data -- Text to Columns -- Delimited by -- Other
In other type in: -
Next -- Date -- DMY
Finish

Your data will be split into two columns with date and start time in first column and - end time in the other
Ignore latter column

In a separate column use
=INT(A2)
for date

and
=MOD(A2)
for time

Format cells accordingly

[NeedForExcel]

Or, If you want a Formula..

In B2 - =DATE(MID(A2,FIND(",",A2)+2,4),MONTH(1&LEFT(A2,FIND(" ",A2)-1)),MID(A2,FIND(" ",A2)+1,(FIND(",",A2)-FIND(" ",A2)-1)))

In C2 - =TIME(MID(SUBSTITUTE(A2," ","|",3),FIND("|",SUBSTITUTE(A2," ","|",3))+1,FIND(":",SUBSTITUTE(A2," ","|",3))-1-FIND("|",SUBSTITUTE(A2," ","|",3)))+IF(ISNUMBER(FIND("AM",A2)),0,12),0,0)

And Format the B Column with mm-dd-yyyy;@

File Attached

[namluke]

Awesome Thank you James

[James McEwan]

Awesome Thank you James

No problem!

J.