convert from julian date

K

Ken F

I have a text file that I am importing into Access and two of the fields are
Julian dates (downloaded from mainframe). I have imported these as strings
into an Access table and would like to convert these to regular dates.
Following is an example:

"2006015" convert to date format 1/15/2006.

I understand I can convert the Julian to a number and then INT(2006015/1000)
to get the year but what about the days?

I would like to do this in a simple query on the fly. I understand how to
convert from regular dates to Julian but what about the other way... Thanks
for you help

Ken
 
J

John Spencer

How about the following?

DateSerial(Left("2006015",4),1,Right("2006015",3))
That returns 1/15/2006

And yes it works for other months. for example:
DateSerial(Left("2006215",4),1,Right("2006215",3))
returns 8/3/2006
 
D

Duane Hookom

Consider using the DateSerial() function:
DateSerial(Int(2006130/1000), 1, 2006130 mod 1000)
 
K

Klatuu

Here is a formula that will convert your text julian dates to gregorian
Access dates. Assume x= "2006015", it will return 1/15/2006

dateadd("d",cint(right(x,3))-1, dateserial(cint(left(x,4)),1,1))
 

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

Top