A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

How do I change 40402 into 04/04/2002?



 
 
Thread Tools Display Modes
  #1  
Old March 30th, 2005, 09:41 PM
Lannutslp
external usenet poster
 
Posts: n/a
Default How do I change 40402 into 04/04/2002?

I downloaded a database from an AS/400 and now I need to change numbers to
dates. When I format it changes everything because it is reading the number
as a julian date.
  #2  
Old March 30th, 2005, 10:10 PM
George Nicholson
external usenet poster
 
Posts: n/a
Default

use DATE(year,month,day)

first, make it easy on yourslef and make sure all your data is 6 characters
long, with leading zeros.
ColumnB = Text(ImportedDataColumn,"000000")

MyDate = DATE(Right(ColumnB,2), Left(ColumnB,2),Mid(ColumnB,3,2))

HTH,
--
George Nicholson

Remove 'Junk' from return address.


"Lannutslp" wrote in message
...
I downloaded a database from an AS/400 and now I need to change numbers to
dates. When I format it changes everything because it is reading the
number
as a julian date.



  #3  
Old March 30th, 2005, 10:51 PM
Lannutslp
external usenet poster
 
Posts: n/a
Default

Thank you - I then needed to add 100 to the year to make it 2000.

"George Nicholson" wrote:

use DATE(year,month,day)

first, make it easy on yourslef and make sure all your data is 6 characters
long, with leading zeros.
ColumnB = Text(ImportedDataColumn,"000000")

MyDate = DATE(Right(ColumnB,2), Left(ColumnB,2),Mid(ColumnB,3,2))

HTH,
--
George Nicholson

Remove 'Junk' from return address.


"Lannutslp" wrote in message
...
I downloaded a database from an AS/400 and now I need to change numbers to
dates. When I format it changes everything because it is reading the
number
as a julian date.




  #4  
Old April 1st, 2005, 07:28 PM
George Nicholson
external usenet poster
 
Posts: n/a
Default

ah, those darn centuries! :-)

--
George Nicholson

Remove 'Junk' from return address.


"Lannutslp" wrote in message
...
Thank you - I then needed to add 100 to the year to make it 2000.

"George Nicholson" wrote:

use DATE(year,month,day)

first, make it easy on yourslef and make sure all your data is 6
characters
long, with leading zeros.
ColumnB = Text(ImportedDataColumn,"000000")

MyDate = DATE(Right(ColumnB,2), Left(ColumnB,2),Mid(ColumnB,3,2))

HTH,
--
George Nicholson

Remove 'Junk' from return address.


"Lannutslp" wrote in message
...
I downloaded a database from an AS/400 and now I need to change numbers
to
dates. When I format it changes everything because it is reading the
number
as a julian date.






 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Change value for multiple columns? MN Running & Setting Up Queries 8 October 22nd, 2004 03:11 PM
how do I change button color Vivacious-Lady Database Design 1 October 18th, 2004 05:21 PM
How can I Change recurrence settings with OUT updating details???? Shawn Anderson Calendar 3 September 20th, 2004 06:38 PM
How can I Change recurrence settings with OUT updating details???? Shawn Anderson General Discussion 3 September 20th, 2004 06:38 PM
change regional Settings To UK currency format from a shared hosting server in Germany... Edward General Discussion 1 September 19th, 2004 11:36 AM


All times are GMT +1. The time now is 08:01 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.