On Oct 16, 8:17*am, Dave Peterson wrote:
A1 isn't a date (yet). *It's just a number.

When you use =year(2000), this returns the year for the day 2,000 days after a
start date (usually 12/31/1899 in wintel land). *And 2000 days after that date
is June 22, 1905.

You could use:
=DATE(RIGHT(A1,4),LEFT(A1,2),MID(A1,3,2))
or
=--TEXT(A1,"00\-00\-0000")
And format the results as a date.

If your data is in a single column,
you could select the column
Data|Text to columns
Fixed width (but don't draw any lines)
choose Date (mdy)
and finish up the wizard.

jlclyde wrote:

In the AS400 it stores date as monthdayyear and does not seperate
them. *So when it spits it out into excel this is what you get.
10012000. *So the date shoudl be October first 2000. *When I use this
formula if 1001200 was in cell A1 it woudl be =Date(Right(Year(A1),
4),Month(Left(A1,2)),Day(Right(Left(A1,4),2))) *It returns the date
10-1-1905. *Is this a bug or did I do something wrong? *I have tried
using value and Int to straighten it out with no luck.

Thanks,
Jay

--

Dave Peterson

This makes a lot of sense now. I always had assumed that if you put
in Year, Month, or Day and included an integer it woudl just use that
number. Now I see that it is a number of days from 1-1-1900. So I no
longer need tyo use year, Month or Day unless I am using a date to
offset from.

Thanks for all the help,
Jay