Date format troubles
 

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!

Date format troubles
 

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

"jjackson97" wrote:

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!

Date format troubles
 

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" wrote:

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!

Date format troubles
 

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(R IGHT(A2,2)))

For that I am grateful! THANKS!

"Eduardo" wrote:

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

"jjackson97" wrote:

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!

Date format troubles
 

your welcome, have a great weekend

"jjackson97" wrote:

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(R IGHT(A2,2)))

For that I am grateful! THANKS!

"Eduardo" wrote:

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

"jjackson97" wrote:

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!

Date format troubles
 

A shorter method with less function calls...

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

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

--
Rick (MVP - Excel)


"jjackson97" wrote in message
...
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(R IGHT(A2,2)))

For that I am grateful! THANKS!

"Eduardo" wrote:

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

"jjackson97" wrote:

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!