Converting Date to Julian

[Sam]

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

 

[Dirk Goldgar]

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")

 

[John W. Vinson]

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.

 

[Sam]

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!!!

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 -

 

[John W. Vinson]

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!