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...

[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]2016-02-2413:40:31[/TD]
[TD="align: center"]2/24/16 1:40 PM[/TD]
[/TR]
[TR]
[TD="align: center"]2016-02-2413:36:05[/TD]
[TD][TABLE="width: 135"]
<tbody>[TR]
[TD="align: center"]2/24/16 1:36 PM[/TD]
[/TR]
</tbody>[/TABLE]

​

[/TD]
[/TR]
[TR]
[TD="align: center"]2016-02-2413:40:08[/TD]
[TD="align: center"]2/24/16 1:40 PM[/TD]
[/TR]
[TR]
[TD="align: center"]2016-02-2413:40:09[/TD]
[TD="align: center"]2/24/16 1:40 PM[/TD]
[/TR]
</tbody>[/TABLE]

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.