Year conversion

[Linda]

Hello,

I just recently importat a database where the date field came in as a
number. When I convert it to a date/time field it appears with the year 2085
instead of 1985. Does anyone know a solution to having this show correctly???

thanks!--
Linda

 

[Jerry Whittle]

Please provide examples of what the data looked like as a number and how you
converted it.

 

[Jeff Boyce]

Linda

?subtract 100 from the year?

Is YOUR field a date/time datatype?

"When I convert it" ... how?/where?

More info, please...


Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Linda]

The number comes in as 67713. The file is huge and when I try to change the
data type to date/time is says it's too large. I have since put mm/dd/yy in
the format field and now it shows as a date but with all they years as 20XX
instead of 19XX.

I also copied the column into excel to see if I could change it there and it
also came in as the year 20XX

 

[Jerry Whittle]

Access stores 5/21/2085 as 67713.

Day 0 is 12/30/1899 and 5/21/2085 is 67713 days later.

You can play with this like so:
CDate(1)
CDate(67713)
CLng(#5/21/2085#)

Now the question becomes what date is 67713 suppose to be? I don't see any
way to get a date in 1985 out of it. Maybe 1967 as in 13 July 1967.

What kind of database did this come from?