Convert 20200917 10:17:00 to date format

[plettieri]

Hi All

I did some research on this conversion but still did not find solution on board for this...


needed in "date format" with year month day time. like this...... 2020 09 17 10:17:00

any help appreciated

plettieri

 

[Fluff]

How about

+Fluff New.xlsm
	H	I
15	20200917 10:17:00	17/09/2020 10:17
Master

Cell Formulas
Range	Formula
I15	I15	=DATE(LEFT(H15,4),MID(H15,5,2),MID(H15,7,2))+TIMEVALUE(RIGHT(H15,8))

 

[MARK858]

Along the same principal as Fluff has posted, just another way. Custom format C1 as yyyy mm dd hh:mm

Book1
	A	B	C
1	20200917 10:17:01		2020 09 17 10:17:01
Sheet1

Cell Formulas
Range	Formula
C1	C1	=(LEFT(A1,4)&"-"&MID(A1,5,2)&"-"&MID(A1,7,2)&" "&RIGHT(A1,8))+0

 

[plettieri]

Thanks Mr. Fluff

much appreciated

plettieri

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[plettieri]

Thanks Mark858...yours and Mr. Fluff work perfectly

plettieri

 

[Dave Patton]

Custom format to your preference

T202009b.xlsm
	A	B
2	20200917 10:17:00	17-Sep-20 10:17:00
3b

Cell Formulas
Range	Formula
B2	B2	=--TEXT(LEFT(A2,8),"0000-00-00")+TIMEVALUE(RIGHT(A2,8))

 

[Dave Patton]

With custom format

T202009b.xlsm
	A	B
1	20200917 10:17:00	17-Sep-20 10:17:00
2	20200917 10:17:00	2020 09 17 10:17:00
3
3b

Cell Formulas
Range	Formula
B1:B2	B1	=--TEXT(LEFT(A1,8),"0000-00-00")+TIMEVALUE(RIGHT(A1,8))

 

[Dave Patton]

Thanks for your feedback.
Minor edit is included below

T202009b.xlsm
	A	B
2	20200917 10:17:00	2020 09 17 10:17:00
3b

Cell Formulas
Range	Formula
B2	B2	=--(TEXT(LEFT(A2,8),"0000-00-00")+RIGHT(A2,8))