Converting Date to Julian


S

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
 
Ad

Advertisements

D

Dirk Goldgar

Sam said:
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")
 
J

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.
 
S

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

Advertisements

J

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!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Julian Date Question (Another) 4
converting julian Date to Database date 5
printing report with date 3
Calculating a Julian Date 7
convert julian dates 2
Converting Text to Date 1
julian date 3
Date Formatting 7

Top