Converting Gregorian date to Julian date (Access 2003)

[pdavis06]

I know that there is a thread about this, but I was unable to add to it; I just joined. The problem is that I do not want to change the date format for the entire database output, merely for a header on a report and for a field on that report.

I thought that I could just go into the expression builder and paste one of the codes that were posted in the thread about this conversion-you'll know that did not work.

So, I went to the new module and tried to enter the following information into the Immediate Window, but it did not work.

? Right(Year(#1/20/2003#),1) &
Format(DateDiff("d",#1/1/2003#,#1/20/2003#)+1,"000")
3020

I followed the direction to enable the 'Require Variable Declaration' in the 'Tools' menu of the Visual Basic Editor and then copied the following into the 'Procedure Window':

FUNCTION: CDate2Julian()

PURPOSE: Convert a date to a Julian day. The function works with dates based on the Gregorian (modern) calendar.

ARGUMENTS:
MyDate: A valid Microsoft Access date.

RETURNS: A three digit Julian day as a string.

End function

However, the entire type in the procedure window changes to red font color and it is invalid in the immediate window as well...

Bottom line, can one of you programmers confirm that it is not known how to get the Julian date from a 'Expression' in a field of a query that this new Report will draw data from?

Thanks much, Paul Davis

[cyberdwarf]

Hi

I worked on this a some time ago, but haven't looked at it in a while

Function julDay(theDate) As String 
' Param: date with year 
' Returns: Julian date version of passed date
 
Function julDay(theDate) As String 
' Arg: A date with year 
' Returns: Julian date version of passed date
Dim monthLen() As Integer 
Dim myMonth As Integer 
Dim i                  As Integer 
Dim numDays As Integer 
 
If Not IsDate(theDate) Then 
    MsgBox "Invalid Date" 
    julDay = "[ERROR]" 
Else 
    ReDim monthLen(1) 
    monthLen(1) = 31 
 
    ReDim Preserve monthLen(2) 
    If Year(theDate) Mod 4 = 0 Then 
     monthLen(2) = 29 
    Else 
     monthLen(2) = 28 
    End If 
 
    ReDim Preserve monthLen(3) 
    monthLen(3) = 31 
    ReDim Preserve monthLen(4) 
    monthLen(4) = 30 
    ReDim Preserve monthLen(5) 
    monthLen(5) = 31 
    ReDim Preserve monthLen(6) 
    monthLen(6) = 30 
    ReDim Preserve monthLen(7) 
    monthLen(7) = 31 
    ReDim Preserve monthLen(8) 
    monthLen(8) = 31 
    ReDim Preserve monthLen(9) 
    monthLen(9) = 30 
    ReDim Preserve monthLen(10) 
    monthLen(10) = 31 
    ReDim Preserve monthLen(11) 
    monthLen(11) = 30 
    myMonth = Month(theDate) 
    For i = 1 To myMonth - 1 
        numDays = numDays + monthLen(i) 
    Next i 
 
    numDays = numDays + Val(Format$(theDate, "dd")) 
    julDay = Format$(theDate, "yy") & Format$(numDays, "000") 
End If 
End Function 
 

Credit to Pete Cresswell

hth

Steve

[cyberdwarf]

Paul


A couple of other suggestions:-

datepart("y", now) + (year(now) - 1900) * 1000 

Or:-

juliandate = Right(DatePart("yyyy",[greg_date]),2) & 
Format(DateDiff("d",CDate("01" & "/" & "01" & " /" & 
DatePart("yyyy",[greg_date])),[greg_date])+1,"000") 

No guarantees, tho'

HTH

Steve

[NeoPa]

I'm not sure what the Julian version is exactly, but the day in year number can be got by :

datVar+1-cdate("1/1/" & year(datVar))

where datVar is the date variable.

[pdavis06]

Hey Gang,

I know that one was a bear. Thank you all for the assistance, though I was unable to make it work. No worries, it was appreciated and I am sure it will help lay the foundational knowledge necessary to working with code in Access.

Have a good one, Paul

[Killer42]

I'm not sure what the Julian version is exactly, but the day in year number can be got by :

datVar+1-cdate("1/1/" & year(datVar))

where datVar is the date variable.

I realise this thread is long finished, but I have been looking into converting a date value to julian format today, and came across it while searching. After trying NeoPa's suggestion (which works, of course) I found that you can get the same value from DatePart - the "y" parameter gives you "Day of year".

So I wrote the following routine in VB6, which for 15 Feb 2007, returns "200746". Should also work in VBA.

Public Function Julian(ByVal datVal As Date) As String
  Julian = DatePart("yyyy", datVal) & DatePart("y", datVal)
  ' Could also use this...
  ' Julian = Year(datVal) & DatePart("y", datVal)
End Function

I suppose it's a bit of an open question as to which function is the more efficient, DatePart or CDate. Probably not a huge difference, in any case.

[NeoPa]

It seems to me that DatePart() would be preferable.
It's what I would have chosen if I'd thought about it first ;)

[Killer42]

How would one go about converting the other way? That is, Julian to Gregorian? I just realised I have an application which will need to do this shortly.

Obviously I'll find some way to do it, but it's likely to be rather kludgy (like counting through Julian dates until one matches, or something). I'd like to find a nice, clean method.

[NeoPa]

Use DateAdd("d",Julian bit,#31 Dec Year-1#)

[Killer42]

Use DateAdd("d",Julian bit,#31 Dec Year-1#)

I like it! :)

Thanks.

[NeoPa]

Thank you Killer.
It's always nice to get appreciation from a connoisseur :)

[shiznaw]

I'm not sure what the Julian version is exactly, but the day in year number can be got by :

datVar+1-cdate("1/1/" & year(datVar))

where datVar is the date variable.

what you just posted is, in actuality, the Julian Date. And had saved the rest of us, approximately 52 lines of coding otherwise.

Thank you, Neopa. No, seriously...Thanxs

Shiznaw
Univ of Utah

[NeoPa]

Not a problem Shiznaw.
I enjoy seeing shortcuts in code. Sometimes I have to forswear them because they make maintenance too complex, but I feel better if I see them anyway at least ;)

[mattmorris0531]

An extremely easy way to do this is

Format(Date, "y")

[NeoPa]

I think you're right Matt. It seems we all overlooked this. I must admit I rarely ever use Julian dates myself.