Converting text to date/time

[Brandon2013]

I am trying to convert text in a cell from: 03/08/2014 2330 to a date that looks like this: 03/08/2014 23:30. Then I also need to extract just the time only and put that in a new cell.
I can do it in a few steps by using Data -> Text To Columns, formatting the date and time separately, and then putting them back together again.

Is there a 1-step way to do the original text to date/time conversion? Ideally a formula?

Thanks

[cffndncr]

Assume your text cell is A1

To get the time-only cell, use this formula:

=TIME(LEFT(RIGHT(A1,4),2),RIGHT(A1,2),0)

Then to get the date, use this formula:

=LEFT(A1,FIND(" ",A1))&LEFT(RIGHT(A1,4),2)&":"&RIGHT(A1,2)

Note: This will still be a text value. As far as I know, Excel can't handle combined date/time formats. If you just want to leave it as the date, shorten the formula to =LEFT(A1,FIND(" ",A1))

[Tony Valko]

Try this...

A1 = 03/08/2014 2330

Enter this formula in B1:

=--TEXT(RIGHT(A1,4),"00\:00")

Format as Time

Enter this formula in C1:

=LEFT(A1,10)+B1

Format as mm/dd/yyyy h:mm AM/PM

[AlKey]

Or you can actually convert it to a real date and time

=VALUE(TEXT(LEFT(A2,10),"m/dd/yyyy")&" "&LEFT(RIGHT(A2,4),2)&":"&RIGHT(A2,2))

Format cell Custom: m/dd/yyyy h:mm

	A	B
1		Format cell Custom: m/dd/yyyy h:mm
2	03/08/2014 2330	3/08/2014 23:30

[Brandon2013]

Thank you everyone! The solutions worked.

[AlKey]

You're welcome and thank you for the feedback!

[Tony Valko]

You're welcome. We appreciate the feedback!