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

[Guest]

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.

 

[George Nicholson]

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,

 

[Guest]

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

 

[George Nicholson]

ah, those darn centuries!