Reading Date Formats e.g 20050801 (yyyy/mm/dd)
 

When I import date, the dates appear in the following format:

20050801 (yyyy/mm/dd)

This is August 1st 2005.

However, when I format cells and go to custom and tell Exel that the the
format is yyyy/mm/dd it returns the value #######################.

How do it translate this date format (20050801) into the correct date
format, that Exel recognizes as a date, so I can begin to work and calculate
with it?

Thanks in advance...

Nathan

One way ..

Assuming the "dates" are in col A, A1 down

Select col A
Click Data Text to columns
Click Next Next

In step 3 of the wizard:
Check "Date" and select "YMD" from the droplist

Click Finish
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Nathan" wrote in message
...
When I import date, the dates appear in the following format:

20050801 (yyyy/mm/dd)

This is August 1st 2005.

However, when I format cells and go to custom and tell Exel that the the
format is yyyy/mm/dd it returns the value #######################.

How do it translate this date format (20050801) into the correct date
format, that Exel recognizes as a date, so I can begin to work and
calculate
with it?

Thanks in advance...

Nathan

Hi Nathan,

Add an extra column with this formula:

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

Fill down as far as you need.

You can always replace the original dates with the new ones by copying and
Paste Special, check Values.

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel


"Nathan" wrote in message
...
When I import date, the dates appear in the following format:

20050801 (yyyy/mm/dd)

This is August 1st 2005.

However, when I format cells and go to custom and tell Exel that the the
format is yyyy/mm/dd it returns the value #######################.

How do it translate this date format (20050801) into the correct date
format, that Exel recognizes as a date, so I can begin to work and
calculate
with it?

Thanks in advance...

Nathan

check
http://excelforum.com/showthread.php?t=373459

Mangesh


"Nathan" wrote in message
...
When I import date, the dates appear in the following format:

20050801 (yyyy/mm/dd)

This is August 1st 2005.

However, when I format cells and go to custom and tell Exel that the the
format is yyyy/mm/dd it returns the value #######################.

How do it translate this date format (20050801) into the correct date
format, that Exel recognizes as a date, so I can begin to work and
calculate
with it?

Thanks in advance...

Nathan

And you can format col A to taste (Date type) thereafter ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----