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.

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.

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.

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.