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

[Lannutslp]

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,
--
George Nicholson

Remove 'Junk' from return address.


"Lannutslp" <[email protected]> wrote in message
news:[email protected]...
>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.

[Lannutslp]

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" <[email protected]> wrote in message
> news:[email protected]...
> >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]

ah, those darn centuries! :-)

--
George Nicholson

Remove 'Junk' from return address.


"Lannutslp" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> >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.
>>
>>
>>