Adding Space between number and converting the field into Date/Time

[justujoo]

Hello Guys

I've tried and tried ... Searched and searched but couldn't find a solution.. So asking here to the experts...

A	B
2016-02-2413:40:31	2/24/16 1:40 PM
2016-02-2413:36:05	2/24/16 1:36 PM <tbody> </tbody> ​
2016-02-2413:40:08	2/24/16 1:40 PM
2016-02-2413:40:09	2/24/16 1:40 PM

<tbody>
</tbody>

So I need covert A into B, where A is Plain String and when I put a space after 10th character (after -24) it automatically converts into Date/Time Format and thats what I want but enter space in more than 10K records manually is not practically possible so I need some auto process, some formula....

Please help me guys

Regards
KH

 

[Max1616]

I might have over killed here but try this:
=DATE(YEAR(SUBSTITUTE(LEFT(A1,10)&" "&RIGHT(A1,8),"-","/")),MONTH(SUBSTITUTE(LEFT(A1,10)&" "&RIGHT(A1,8),"-","/")),DAY(SUBSTITUTE(LEFT(A1,10)&" "&RIGHT(A1,8),"-","/")))+TIME(HOUR(SUBSTITUTE(LEFT(A1,10)&" "&RIGHT(A1,8),"-","/")),MINUTE(SUBSTITUTE(LEFT(A1,10)&" "&RIGHT(A1,8),"-","/")),SECOND(SUBSTITUTE(LEFT(A1,10)&" "&RIGHT(A1,8),"-","/")))

 

[justujoo]

nope......
Not Working Boss

 

[justujoo]

nope......
Not Working Boss

My... Bad....

It Worked.....

Thank you Max

 

[justujoo]

I might have over killed here but try this:
=DATE(YEAR(SUBSTITUTE(LEFT(A1,10)&" "&RIGHT(A1,8),"-","/")),MONTH(SUBSTITUTE(LEFT(A1,10)&" "&RIGHT(A1,8),"-","/")),DAY(SUBSTITUTE(LEFT(A1,10)&" "&RIGHT(A1,8),"-","/")))+TIME(HOUR(SUBSTITUTE(LEFT(A1,10)&" "&RIGHT(A1,8),"-","/")),MINUTE(SUBSTITUTE(LEFT(A1,10)&" "&RIGHT(A1,8),"-","/")),SECOND(SUBSTITUTE(LEFT(A1,10)&" "&RIGHT(A1,8),"-","/")))

It Worked.....

Thank you Max

 

[jtakw]

Hi,

Would these shorter formulas also work?

Excel 2010
	A	B	C	D	E
1	2016-02-2413:40:31	2/24/2016 13:40		2/24/2016 13:40	2/24/2016 13:40
2	2016-02-2413:36:05	2/24/2016 13:36		2/24/2016 13:36	2/24/2016 13:36
3	2016-02-2413:40:08	2/24/2016 13:40		2/24/2016 13:40	2/24/2016 13:40
4	2016-02-2413:40:09	2/24/2016 13:40		2/24/2016 13:40	2/24/2016 13:40
Sheet1

Cell Formulas
Range	Formula
D1	=TEXT(LEFT(A1,SEARCH(":",A1)-3)&" "&RIGHT(A1,SEARCH(":",A1)-5),"m/dd/yyyy hh:mm")
E1	=TEXT(LEFT(A1,10)&" "&RIGHT(A1,8),"m/dd/yyyy hh:mm")

D1 or E1 formula copied down.