Date(Year,Month,Day) not returnign correct date

J

jlclyde

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
 
J

jlclyde

Hi
Try this =DATE(RIGHT(A1,4),LEFT(A1,2),MID(A1,3,2))
HTH






- Show quoted text -

I learned something. Unless I write it exactly like you have it does
nto work. I am wondering why it does not work when you have in the
Year() , Month() and Day()? I am trying to show how the date function
works, that is why I was leaving in the functions for each part.
Thanks,
Jay
 
M

Mattlynn via OfficeKB.com

Hi
try this

=LEFT(A1,2)&"-"&MID(A1,3,2)&"-"&RIGHT(A1,4)

Regards
Matt
 
D

Dave Peterson

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.
 
M

Mattlynn via OfficeKB.com

Your data is not actually a date at this stage. Its just a numercal number

Hi
Try this =DATE(RIGHT(A1,4),LEFT(A1,2),MID(A1,3,2))
[quoted text clipped - 12 lines]
- Show quoted text -

I learned something. Unless I write it exactly like you have it does
nto work. I am wondering why it does not work when you have in the
Year() , Month() and Day()? I am trying to show how the date function
works, that is why I was leaving in the functions for each part.
Thanks,
Jay
 
J

jlclyde

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.

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
 
P

Per Jessen

Jay,

As the value in A1 is not a true date, you have to do it like this:

=DATE(RIGHT(A1,4),LEFT(A1;2),MID(A1,3,2))

Regards,
Per
 

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

Top