Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date(Year,Month,Day) not returnign correct date
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date(Year,Month,Day) not returnign correct date
Hi
Try this =DATE(RIGHT(A1,4),LEFT(A1,2),MID(A1,3,2)) HTH John "jlclyde" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date(Year,Month,Day) not returnign correct date
try modify yr formula to:
=Date(Right(A1,4),Left(A1,2),mid(A1,3,2)) HIH On 16 Paź, 14:50, 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date(Year,Month,Day) not returnign correct date
On Oct 16, 8:02*am, "John" wrote:
Hi Try this =DATE(RIGHT(A1,4),LEFT(A1,2),MID(A1,3,2)) HTH John"jlclyde" wrote in message ... 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- Hide quoted text - - 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date(Year,Month,Day) not returnign correct date
Hi
try this =LEFT(A1,2)&"-"&MID(A1,3,2)&"-"&RIGHT(A1,4) Regards Matt 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 -- Matt Lynn Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200910/1 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date(Year,Month,Day) not returnign correct date
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date(Year,Month,Day) not returnign correct date
Your data is not actually a date at this stage. Its just a numercal number
jlclyde wrote: 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 -- Matt Lynn Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200910/1 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date(Year,Month,Day) not returnign correct date
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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date(Year,Month,Day) not returnign correct date
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 "jlclyde" skrev i meddelelsen ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
count month when date is in day/month/year format | Excel Worksheet Functions | |||
Sorting a date by month, date and then year. | Excel Discussion (Misc queries) | |||
Tell me which "season" (Month/Day through Month/Day) a date(Month/Day/Year) falls in (any year)??? | Excel Discussion (Misc queries) | |||
pick up date, month and year from a date | Excel Discussion (Misc queries) | |||
Sort month/date/year data using month and date only | Excel Discussion (Misc queries) |