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

G

Guest

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
 
M

Max

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
 
N

Niek Otten

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Changing date to eliminate day in format mm/dd/yyyy 1
Formula bar shows mm/dd/yyyy. To show dd/mm/yyyy. How? 2
Split date from date time cells dd/mm/yyyy hh:mm. New cell dd/mm/y 2
Sumproduct and weekday from mm/dd/yyyy format? 3
Date Formats 1
Excel 2002: How to make the date format uniform ? 1
Forcing a date entry format of dd/mm/yyyy 1
reformat dates to yyyy/mm/dd 5

Top