Date format troubles

[jjackson97]

I have an Excel worksheet that has dates entered as General format like the
following: 19970103

I am trying to convert these to dates (MM/DD/YYYY), but everything I try
does not work.

Any help is greatly appreciated!

 

[Eduardo]

Hi
in one column enter the formula as follow
=DATE(VALUE(LEFT(A1,4)),VALUE(MID(A1,1,1)),VALUE(RIGHT(A1,1)))

 

[mc]

I am assuming 01 is your month, so it would be

=DATE(LEFT(A1,4),LEFT(RIGHT(A1,4),2),RIGHT(A1,2))

if 01 is your day, then

=DATE(LEFT(A1,4),RIGHT(A1,2),LEFT(RIGHT(A1,4),2))

Then format the date to what you want.

 

[jjackson97]

Very close. I left off that some of the data was not with leading 0's for
the date an month. So there was stuff like 19951112, which got messy with
your formula. However you got me in the right direction to tweak it to:
=DATE(VALUE(LEFT(A2,4)),VALUE(MID(A2,5,2)),VALUE(RIGHT(A2,2)))

For that I am grateful! THANKS!

 

[Eduardo]

your welcome, have a great weekend

Very close. I left off that some of the data was not with leading 0's for
the date an month. So there was stuff like 19951112, which got messy with
your formula. However you got me in the right direction to tweak it to:
=DATE(VALUE(LEFT(A2,4)),VALUE(MID(A2,5,2)),VALUE(RIGHT(A2,2)))

For that I am grateful! THANKS!

 

[Rick Rothstein]

A shorter method with less function calls...

=--TEXT(A2,"0000-00-00")

You can format the cell in the date format you want.