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.

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


"dan" <(E-Mail Removed)> wrote in message
news:24451C93-1C8D-4CE6-8023-(E-Mail Removed)...
>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.

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.

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

"Rick Rothstein (MVP - VB)" wrote:

> 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
>
>
> "dan" <(E-Mail Removed)> wrote in message
> news:24451C93-1C8D-4CE6-8023-(E-Mail Removed)...
> >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.
>
>