Julian date to calendar date, advice on code appreciated.

[pwinkw]

Hi,

This is my first post, so please be patient if I messed it up.

The problem I have/had was converting a Julian date back into a normal calendar date and the following is what I came up with to do the job.

It's probably not very pretty from a programmers point of view, so any comments or suggestions will be very wellcome.

Patrck.

Sub CalDate()
Dim CalDate As Date 'Is the calander date preceding the Julian year eg 9365 is 31 Dec 2008
Dim JulianDate As Integer 'Julian date from the data base table, may be 4 or 5 characters long.
Dim JulianLength As Integer 'Length of Julian date, this will be 4 or 5 characters
Dim JulianDays As Integer '3 digits from R to L taken from the Julian date
Dim JulianYear As Integer '1 or 2 digits L to R from JulianDate to obtain the Year
Dim RecordDate As Date 'Reconstructed Calander date taken from the info in the Julian date.
Dim OffSet As Integer
Dim Increment As Integer
Dim strTest As String
Dim TestValues(3) As Integer
Dim TestCounter As Integer
TestValues(0) = "9345"
TestValues(1) = "10245"
TestValues(2) = "12110"
Increment = 0
For TestCounter = 0 To 2
strTest = StrConv(TestValues(TestCounter), vbLowerCase) ' need to get length of Julian date

JulianLength = Len(strTest)
If JulianLength = 4 Then OffSet = 1 Else OffSet = 2

JulianDate = TestValues(Increment)

JulianYear = Left$(JulianDate, OffSet)
JulianYear = JulianYear + 1999 ' all my Julian dates are after the year 2000
CalDate = DateSerial(JulianYear, 12, 31)
JulianDays = Right$(JulianDate, 3)
RecordDate = DateAdd("d", JulianDays, CalDate)

Debug.Print
Debug.Print RecordDate, " reconstructed calendar date"
Debug.Print
Debug.Print strTest, " original Julian date"
Debug.Print
Debug.Print JulianLength, " number of chacters in Julian date"

Increment = Increment + 1

Next TestCounter

End Sub

 

[pr2-eugin]

Hi pinkkw, What seems to be the problem?

 

[gemma-the-husky]

when people say julian date, i am never quite sure what they mean. I assume you mean a number representing a given number of days from a fixed date

anyway, access date 0 is 30/11/1899 (I think)

so - work out what your julian date 0 is, and simply add on whatever difference there is between julian date 0, and access date 0 - then you have the access date.

 

[mdlueck]

First, I believe what you are describing is actually an Ordinal date format, not Julian:

http://en.wikipedia.org/wiki/ISO_8601#Ordinal_dates

I have written the following code to convert from Ordinal format.

Public Function dateutils_ConvertFromYYYDDD(strYYYDDD As String) As String
  On Error GoTo Err_dateutils_ConvertFromYYYDDD

  Dim intDay As Integer
  Dim intYear As Integer

  'Test for "blank" dates
  If strYYYDDD = "0" Then
    dateutils_ConvertFromYYYDDD = ""
    Exit Function
  End If

  'Store the day of the year
  intDay = Right(strYYYDDD, 3)

  'Determine if year is 2 digits or 3 digits on the inbound date — ie. 99001 or 104001
  If Len(strYYYDDD) = 5 Then
    intYear = Left(strYYYDDD, 2) + 1900
  Else
    intYear = Left(strYYYDDD, 3) + 1900
  End If

  dateutils_ConvertFromYYYDDD = DateSerial(intYear, 1, intDay)

Exit_dateutils_ConvertFromYYYDDD:
  Exit Function

Err_dateutils_ConvertFromYYYDDD:
  Call errorhandler_Logger("Module: modshared_dateutils, Function: dateutils_ConvertFromYYYDDD()")
  dateutils_ConvertFromYYYDDD = ""
  Resume Exit_dateutils_ConvertFromYYYDDD

End Function

 

[pwinkw]

Thanks PR, Dave and Michael,
I think I've caused a bit of confusion. I did manage to write the code I needed to do the conversion and the reason I posted it was to see if you guys could point me to any errors that would come back to haunt me in the future?
The code I posted is 'stand alone', not as yet connected to any tables or such so it can't do any damage.
Michael, I don't know why it's called Julian date here, I never thought to investigate it before. The format we use is either yyddd or yddd. Thank you very much for posting your code, I will be able to learn from it.
Patrick.

 

[mdlueck]

Michael, I don't know why it's called Julian date here, I never thought to investigate it before. The format we use is either yyddd or yddd. .

After a couple of days "pulling my hair out" converting what I am told is a "Julian date" and always arriving at some 8000 B.C. date, I had to chuckle when I came across that Wikipedia posting:

This system is sometimes incorrectly referred to as "Julian Date"

Jjjjaaaaaa!!!!!

Yes yyddd or yddd is the Ordinal date encoding method.

P.S. There is another page with formulas for dealing with Ordinal dates:
http://en.wikipedia.org/wiki/Ordinal_date