Converting a Julian style date to "short date" format

K

Kevin Newman

I am trying to convert a field with a date in this format "107120" which is
equal to 04/30/2007. I need to be able to do this in MSAccess2003 VBA. Anyone
got a solution?

Kevin
 
J

John W. Vinson

I am trying to convert a field with a date in this format "107120" which is
equal to 04/30/2007. I need to be able to do this in MSAccess2003 VBA. Anyone
got a solution?

Kevin
Click to expand...

I take it that 107 is the year (years since 1900) and 120 the day number? Try

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

if the julian date field is a Number field named jdate. If it's Text try the
same thing using Val([jdate]) in place of [jdate].

John W. Vinson [MVP]
 
J

Jeff Boyce

Kevin

Take a look at the DateSerial() function and the DateAdd() function.

I can't puzzle out how "107120" translates to "4/30/2007" ... but I suspect
the "...120" portion is days-for-the-year". The following expression
resolved to 4/30/2007:

DateAdd("d",120,#12/31/2006#)

You could use the Left(), Mid(), and/or Right() functions to grab the parts
you need.

Have you tried looking on-line (?Google, ?mvps.org) for routines others have
already worked out?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
K

Kevin Newman

John W. Vinson said:
I am trying to convert a field with a date in this format "107120" which is
equal to 04/30/2007. I need to be able to do this in MSAccess2003 VBA. Anyone
got a solution?

Kevin
Click to expand...

I take it that 107 is the year (years since 1900) and 120 the day number? Try

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

if the julian date field is a Number field named jdate. If it's Text try the
same thing using Val([jdate]) in place of [jdate].

John W. Vinson [MVP]
Click to expand...

Thanks John. That worked beautifully.
 
K

Kevin Newman

Jeff Boyce said:
Kevin

Take a look at the DateSerial() function and the DateAdd() function.

I can't puzzle out how "107120" translates to "4/30/2007" ... but I suspect
the "...120" portion is days-for-the-year". The following expression
resolved to 4/30/2007:

DateAdd("d",120,#12/31/2006#)

You could use the Left(), Mid(), and/or Right() functions to grab the parts
you need.

Have you tried looking on-line (?Google, ?mvps.org) for routines others have
already worked out?

Regards

Jeff Boyce
Microsoft Office/Access MVP
Click to expand...

Thanks for the help Jeff
 
Top