How do I change date yyyymmdd to a Excel-supported date format?

[dan]

I have a column of imported data information of the format yyyymmdd, e.g.,
18400314. Excel does not recognize this date format. How do I change it so
that Excel will recognize it?
Many thanks for any help.

 

[David Biddulph]

Excel date format will not recognise dates before 1900. I would suggest
that you use Data/ Text to Columns or another method to produce separate
columns for year month and day, and manipulate them separately.

 

[Rick Rothstein \(MVP - VB\)]

Do you really have dates before 1900 as your example showed? If so, I don't
think Excel will be able to handle them. If on other hand, that was just a
mistaken year example, then try this procedure... select your column of
numbers, click Date/TextToColumns on Excel's menu bar, click OK twice to get
to Step 3 of 3 and select YMD from the Date drop-down in the upper right
corner of the dialog box, then click Finish. That should turn your numbers
into real dates.

Rick

 

[NoodNutt]

G'day Dan

The closest I could come to converting this into what resembles a date
format is this

Assume:

18400314 = A3

place this in B3 =RIGHT(A3,2) ' this will give you the Day = 14
place this in C3 =MID(A3,5,2) ' this will give you the Month = 03
place this in D3 =LEFT(A3,4) ' this will give you the year = 1840

To comine them into one

place this in E3 =B3&"/"&C3&"/"&D3 ' this will give you this:

14/03/1840

The only drawback is that this is text, not date.

One of the many MVP Guru's may provide something more suitable.

HTH
Mark.

 

[dan]

Thank you...it worked! I did have years in the 1800s, but I just added the
equivalent of 100 yrs to them to make them 1900s.

dan