Developer Forum

Hi,
Is there a simple way to convert a string in the format yyjjj where yy is a
two digit year and jjj is a three digit day of year into the more usual
dd/mm/yy format?
Thanks
Peter

On Oct 18, 8:26 am, Peter Clancy


you could use mid(), left() and right() to get the parts separated and
then use DateSerial to sew them together to convert to a date that
Access recognizes.

Hi Peter,
I assume that you use just two digit for the year because you wanna indicate
just year in the 21st century so 00001 means 01/01/2000. If this is the case
you can do your conversion in this way:
dim tmp_year as long
dim tmp_day as long

''yyjjj is your date to be converted
tmp_year=clng("20" & left(yyjjj,2))-1
tmp_day=right(yyjjj,3)

cv_dt = format(cdate(clng(cdate("31/12/" & tmp_year))+tmp_day),"dd/mm/yyyy")

HTH Paolo

Paolo,
The answer came to me after I had posted. I pulled the yy out and appended
it to a string starting 01/01/ and then pulled the day of the year out and
stored it in an integer then did a dateadd to add the number of days to the
first of Jan date.

[code]

Dim JulianDate As String
Dim StartDate As String
Dim StartYear As String
Dim JDay As Integer

JulianDate = Mid(Buffer, 29, 5)
StartYear = "01/01/" & Left(JulianDate, 2)
JDay = Mid(JulianDate, 3, 3)
StartDate = Format(DateAdd("d", JDay - 1, CDate(StartYear)),
"dd-mmm-yyyy")

[/code]

Thanks for your reply.

Simplest way I know is to use dateSerial

DateSerial(Left([SomeField],2),1,Right(SomeField,3))

I might prefix this with IsNumeric to test the string. And I might be
paranoid to test the string for length to make sure it is five characters in
length, but if you are confident about your data then you can just use the
above. Note that it will error if SomeField is null.

IIF(IsNumeric([SomeField]),
DateSerial(Left([SomeField],2),1,Right(SomeField,3)),Null)

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.