By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,327 Members | 1,805 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,327 IT Pros & Developers. It's quick & easy.

Converting Gregorian date to Julian date (Access 2003)

P: 2
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
Dec 8 '06 #1

✓ answered by mattmorris0531

An extremely easy way to do this is
Expand|Select|Wrap|Line Numbers
  1. Format(Date, "y")

Share this Question
Share on Google+
14 Replies


Expert 100+
P: 218
Hi

I worked on this a some time ago, but haven't looked at it in a while
Expand|Select|Wrap|Line Numbers
  1. Function julDay(theDate) As String 
  2. ' Param: date with year 
  3. ' Returns: Julian date version of passed date
  4.  
  5. Function julDay(theDate) As String 
  6. ' Arg: A date with year 
  7. ' Returns: Julian date version of passed date
  8. Dim monthLen() As Integer 
  9. Dim myMonth As Integer 
  10. Dim i                  As Integer 
  11. Dim numDays As Integer 
  12.  
  13. If Not IsDate(theDate) Then 
  14.     MsgBox "Invalid Date" 
  15.     julDay = "[ERROR]" 
  16. Else 
  17.     ReDim monthLen(1) 
  18.     monthLen(1) = 31 
  19.  
  20.     ReDim Preserve monthLen(2) 
  21.     If Year(theDate) Mod 4 = 0 Then 
  22.      monthLen(2) = 29 
  23.     Else 
  24.      monthLen(2) = 28 
  25.     End If 
  26.  
  27.     ReDim Preserve monthLen(3) 
  28.     monthLen(3) = 31 
  29.     ReDim Preserve monthLen(4) 
  30.     monthLen(4) = 30 
  31.     ReDim Preserve monthLen(5) 
  32.     monthLen(5) = 31 
  33.     ReDim Preserve monthLen(6) 
  34.     monthLen(6) = 30 
  35.     ReDim Preserve monthLen(7) 
  36.     monthLen(7) = 31 
  37.     ReDim Preserve monthLen(8) 
  38.     monthLen(8) = 31 
  39.     ReDim Preserve monthLen(9) 
  40.     monthLen(9) = 30 
  41.     ReDim Preserve monthLen(10) 
  42.     monthLen(10) = 31 
  43.     ReDim Preserve monthLen(11) 
  44.     monthLen(11) = 30 
  45.     myMonth = Month(theDate) 
  46.     For i = 1 To myMonth - 1 
  47.         numDays = numDays + monthLen(i) 
  48.     Next i 
  49.  
  50.     numDays = numDays + Val(Format$(theDate, "dd")) 
  51.     julDay = Format$(theDate, "yy") & Format$(numDays, "000") 
  52. End If 
  53. End Function 
  54.  
Credit to Pete Cresswell

hth

Steve
Dec 8 '06 #2

Expert 100+
P: 218
Paul


A couple of other suggestions:-
Expand|Select|Wrap|Line Numbers
  1. datepart("y", now) + (year(now) - 1900) * 1000 
Or:-

Expand|Select|Wrap|Line Numbers
  1. juliandate = Right(DatePart("yyyy",[greg_date]),2) & 
  2. Format(DateDiff("d",CDate("01" & "/" & "01" & " /" & 
  3. DatePart("yyyy",[greg_date])),[greg_date])+1,"000") 
No guarantees, tho'

HTH

Steve
Dec 8 '06 #3

NeoPa
Expert Mod 15k+
P: 31,661
I'm not sure what the Julian version is exactly, but the day in year number can be got by :
Expand|Select|Wrap|Line Numbers
  1. datVar+1-cdate("1/1/" & year(datVar))
where datVar is the date variable.
Dec 8 '06 #4

P: 2
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
Dec 11 '06 #5

Expert 5K+
P: 8,434
I'm not sure what the Julian version is exactly, but the day in year number can be got by :
Expand|Select|Wrap|Line Numbers
  1. 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.
Expand|Select|Wrap|Line Numbers
  1. Public Function Julian(ByVal datVal As Date) As String
  2.   Julian = DatePart("yyyy", datVal) & DatePart("y", datVal)
  3.   ' Could also use this...
  4.   ' Julian = Year(datVal) & DatePart("y", datVal)
  5. 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.
Feb 15 '07 #6

NeoPa
Expert Mod 15k+
P: 31,661
It seems to me that DatePart() would be preferable.
It's what I would have chosen if I'd thought about it first ;)
Feb 15 '07 #7

Expert 5K+
P: 8,434
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.
Feb 15 '07 #8

NeoPa
Expert Mod 15k+
P: 31,661
Use DateAdd("d",Julian bit,#31 Dec Year-1#)
Feb 15 '07 #9

Expert 5K+
P: 8,434
Use DateAdd("d",Julian bit,#31 Dec Year-1#)
I like it! :)

Thanks.
Feb 15 '07 #10

NeoPa
Expert Mod 15k+
P: 31,661
Thank you Killer.
It's always nice to get appreciation from a connoisseur :)
Feb 15 '07 #11

P: 29
I'm not sure what the Julian version is exactly, but the day in year number can be got by :
Expand|Select|Wrap|Line Numbers
  1. 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
Jun 26 '07 #12

NeoPa
Expert Mod 15k+
P: 31,661
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 ;)
Jun 28 '07 #13

P: 1
An extremely easy way to do this is
Expand|Select|Wrap|Line Numbers
  1. Format(Date, "y")
May 21 '10 #14

NeoPa
Expert Mod 15k+
P: 31,661
I think you're right Matt. It seems we all overlooked this. I must admit I rarely ever use Julian dates myself.
May 23 '10 #15

Post your reply

Sign in to post your reply or Sign up for a free account.