I have dates in a field as listed below
Unformatted Date Formatted Date
931216 Format needed is 12/16/1993
960901 Format needed is 09/01/1996
1040813 Format needed is 08/13/2004
1011001 Format needed is 08/13/2001

When the year in the unformatted date is less than the year 2000 it
has a 2 digit year and if the year is greater than 1999 then there is
a 3 digit year starting with 1 and then a 2 digit year.

Could someone please help with a formula or code to convert this date
to the MM/DD/YYYY format?


Thanks
Sam

"Sam" <(E-Mail Removed)> wrote in message
news:84f67ee1-e8ec-4733-927f-(E-Mail Removed)...
>I have dates in a field as listed below
> Unformatted Date Formatted Date
> 931216 Format needed is 12/16/1993
> 960901 Format needed is 09/01/1996
> 1040813 Format needed is 08/13/2004
> 1011001 Format needed is 08/13/2001
>
> When the year in the unformatted date is less than the year 2000 it
> has a 2 digit year and if the year is greater than 1999 then there is
> a 3 digit year starting with 1 and then a 2 digit year.
>
> Could someone please help with a formula or code to convert this date
> to the MM/DD/YYYY format?


It's US Gregorian, not Julian, format that you're wanting to convert to.
This formula will convert the unformatted input to a Date/Time value:

CDate(Format((19000000 + CLng([UnformattedDate])), "0000-00-00"))

This formula will convert it to a string in your desired output format:

Format(Format((19000000 + CLng([UnformattedDate]), "0000-00-00")),
"mm/dd/yyyy")


--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)

On Tue, 5 Jan 2010 14:17:09 -0800 (PST), Sam <(E-Mail Removed)> wrote:

>I have dates in a field as listed below
>Unformatted Date Formatted Date
>931216 Format needed is 12/16/1993
>960901 Format needed is 09/01/1996
>1040813 Format needed is 08/13/2004
>1011001 Format needed is 08/13/2001
>
>When the year in the unformatted date is less than the year 2000 it
>has a 2 digit year and if the year is greater than 1999 then there is
>a 3 digit year starting with 1 and then a 2 digit year.
>
>Could someone please help with a formula or code to convert this date
>to the MM/DD/YYYY format?
>
>
>Thanks
>Sam

This is not really a "Julian Date" (a term which has a great many definitions)
but...

Try:

DateSerial(1900 + [unformatted date] \ 10000, [unformatted date] \ 100 MOD
100, [unformatted date] MOD 100)

This will give an Access Date/Time value (actually a double float count of
days since midnight, December 30, 1899); it can be formatted as you describe
or in any other desired date format.
--

John W. Vinson [MVP]

That worked but could I also get help with creating a module so i can
call it. I just found out I have a few hundred different date fields
to convert.

Thanks!!!


On Jan 5, 5:40*pm, John W. Vinson <jvinson@STOP_SPAM.WysardOfInfo.com>
wrote:
> On Tue, 5 Jan 2010 14:17:09 -0800 (PST), Sam <samcan...@gmail.com> wrote:
> >I have dates in a field as listed below
> >Unformatted Date * * * * * * * * * * * * * ** * Formatted Date
> >931216 * * * * * * * *Format needed is * * *12/16/1993
> >960901 * * * * * * * *Format needed is * * *09/01/1996
> >1040813 * * * * * * * Format needed is * * *08/13/2004
> >1011001 * * * * * * * Format needed is * * *08/13/2001
>
> >When the year in the unformatted date is less than the year 2000 it
> >has a 2 digit year and if the year is greater than 1999 then there is
> >a 3 digit year starting with 1 and then a 2 digit year.
>
> >Could someone please help with a formula or code to convert this date
> >to the MM/DD/YYYY format?
>
> >Thanks
> >Sam
>
> This is not really a "Julian Date" (a term which has a great many definitions)
> but...
>
> Try:
>
> DateSerial(1900 + [unformatted date] \ 10000, [unformatted date] \ 100 MOD
> 100, [unformatted date] MOD 100)
>
> This will give an Access Date/Time value (actually a double float count of
> days since midnight, December 30, 1899); it can be formatted as you describe
> or in any other desired date format.
> --
>
> * * * * * * *John W. Vinson [MVP]- Hide quoted text -
>
> - Show quoted text -

On Tue, 5 Jan 2010 16:05:20 -0800 (PST), Sam <(E-Mail Removed)> wrote:

>That worked but could I also get help with creating a module so i can
>call it. I just found out I have a few hundred different date fields
>to convert.

Public Function NumToDate(lngIn As Long) As Date
NumToDate = DateSerial(1900 + lngIn\10000, lngIN \ 100 MOD 100, lngIn MOD 100)
End Function


You'll probably want to add error handling to detect invalid input (numbers
less than 10000, greater than the 21st century, invalid dates such as 999999,
etc.) Of course Dirk's expression will work too - several ways to slice and
dice this!
--

John W. Vinson [MVP]