Import 6 digit # for date How to format

[Bob Moore]

I am importing data into MS Access 2003 from an external source. the dat
format is numeric 0digit number. ie: 107017 is Jan 17, 2007. Any Idea how I
can get the date to display in access... I've tried the format function but
it won't let me format as a date

 

[Jerry Whittle]

Hi Bob,

What does a date from December look like? The number displayed doesn't seem
to be able to handle the 12th month without adding another digit Or the 017
will be something like a 365 for the end date of a non-Leap Year (AKA Julian
Date).

 

[Pat Hartman \(MVP\)]

As Jerry already mentioned, the posted sample doesn't look correct.
Usually, the format will be mmddyy or yymmdd or yyddd. It would be very
unusual for it to be myyddd. If the final format is the case, what is the
value for October 17th? Two things are at play here. How is a month higher
than 9 handled and is the three digit day field actually a year to date
number or is it the more normal month to date number in which case it
shouldn't be three digits.

To convert mmddyy to a date, use:
CDate(Mid(YourDate,3,2) & "/" & Right(YourDate,2) & "/" & Left(YourDate,2))
This just pulls out various positions in the six digit string and reorders
them concatenated with the date separator.

To convert yyddd to a date, use:
DateAdd("d", Right(YourDate,3) - 1, CDate("1/1/" & Left(YourDate,2))
This adds the number of days in the ddd part of the string to Jan 1st of the
year part of the string. 1 is subtracted from the ddd part because the date
it is being added to is the first of Jan rather than the 31st of Dec. You
could do that but the calculation then has to subtract 1 from 1/1/youryear
to get 12/31/prevyouryear.

 

[John W. Vinson]

On Thu, 15 Feb 2007 07:20:49 -0800, Bob Moore <Bob

I am importing data into MS Access 2003 from an external source. the dat
format is numeric 0digit number. ie: 107017 is Jan 17, 2007. Any Idea how I
can get the date to display in access... I've tried the format function but
it won't let me format as a date

ahhh... I'm guessing, from a previous thread, that this is a
Peoplesoft date with the first three digits being the number of years
since 1900, and the last three being the number of days since the
beginning of the year? I.e. would December 31, 1998 be 98365?

If so, try:

DateSerial(1900 + [yourdate] \ 1000, 1, [yourdate] MOD 1000)

John W. Vinson [MVP]