Text to Date Format Conversion

[BigBeachBananas]

I have dates that are in text format: 20071231 and I want to convert them to a short date type: 2017/12/31 (YYYY/MM/DD). What's the best way to do this?

 

[jtakw]

Hi,

A couple of ways, format result cell to display as you like:

Book3.xlsx
	A	B	C
1	20071231	12/31/2007	12/31/2007
Sheet1091

Cell Formulas
Range	Formula
B1	B1	=REPLACE(REPLACE(A1,7,0,"/"),5,0,"/")+0
C1	C1	=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))

 

[BigBeachBananas]

Hi,

A couple of ways, format result cell to display as you like:

Book3.xlsx
	A	B	C
1	20071231	12/31/2007	12/31/2007
Sheet1091

Cell Formulas
Range	Formula
B1	B1	=REPLACE(REPLACE(A1,7,0,"/"),5,0,"/")+0
C1	C1	=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))

Thanks!

 

[jtakw]

You're welcome, thanks for the feedback.