how do I compute Julian dates?

[jrpope]

I have been trying to compute julian dates in VBA and hve only had limited success. When I change years, this becomes a big problem. The success I've had is with computing the julian date off of the date in number format. This works well but I can't seem to get the year right when the year changes.


For those not familiar with julian dates they are 4 digit numbers signifying YDDD. examples - 1 Jan 2002 = 2001 and 19 Jul 2002 = 2200. January first is always day 1 and the days are consecutively numbered through the rest of the year. (365 days for a normal year and 366 for leap year).


Can anybody help?

 

[raymondo]

I hate to be picky but your date is not a Julian date. The Julian date is defined as the number of days starting from 0 at 12 noon 1 JAN -4712 (4713 BC) in the Julian proleptic calendar which was favoured by astronomers for many centuries.
Your date appears to be one of the many variants of the Gregorian day-count representations.
Enough of the soap box!
If my interpretation is correct, your date consists of the last digit of the year followed by the day count from December 31 of the previous year.
There are a plethora of date functions in VB that will allow you determine the bits you need - DateDiff, DateSerial, Year etc etc.
One such might be

Dim lngDigit, lngYear, lngDayDiff, lngGD As Long

lngYear = Year(yourDate)
lngDigit = (lngYear Mod 10) * 1000
lngDayDiff = DateDiff("d", CDate("01/01/" & CStr(lngYear)), yourDate) + 1
lngGD = lngDigit + lngDayDiff

Hope this helps and sorry for the lecture! Raymondo
raymondo@rossar.net

 

[MichaelRed]

Hmmmmmmmmmmmmm,

While I am aware that there are some (several?) variations in the thought process of "Julian Date", and in general have no serioue issues with an indvidual's adoption of any of the variations, I have not heard of this one (at least as much as I can rember). It seems -to be- to be entirely unsuitable, as there can (surely WILL) be some confusion over the year part in a relatively SHORT ORDER -as within the next several years!

Still, it even seems someehat shortsited to even generate such an unweidely variable, since MS and others have presented us with a plethora of dating services, after all what kink of a date can't you get from DateSerial, DateDiff, DateAdd, Year, Month, Day, Hour, Second, Minute ...

Oh well just to provide some mental fodder:

? Right(Str(Year(Date)), 1) & Trim(Str(dateDiff("d", DateSerial(Year(Date), 1, 0), Date)))
2200


MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[raymondo]

As a onetime Astrophysicist I can assure that the Julian Date is defined as above. But I agree with you, it really doesn't matter (which rather begs the question why did I even bother to state it - let's just say a personal foible!)
I must admit also that I'm stumped as to what you could do with the date in the original question that existing VB functions wouldn't allow you to do!
Ah well, 'tis a strange world. Raymondo
raymondo@rossar.net

 

[raskew]

There are indeed multiple interpretations of Julian date.
Have no doubt that Raymondo's version is techically correct.
However, in developing functions that dealt with holidates
over a span of years, I found that the easiest solution was
to use the stored Access date, e.g.

dteHold = #07/20/2002#
'show it as a string
JulDate = LTrim(Str(Int(CDbl(dteHold))))
? juldate
37457
'show it as a double
? cdbl(dteHold)
37457
'show it as a single
? csng(dteHold)
37457

Used in this manner in combination with the many
Access date-related functions, there's almost nothing
you can't accomplish.

 

[jrpope]

First I want to thank you for your help and I'm trying all three options to see which works best for my needs. I normally wouldn't even screw with something this obscure but the database I'm writing is for the military and as much as I've tried not to use it, they want it and have been using Julian dates for as long as I've been in.

Thanks for your help

 

[NotSoVisual]

Raskew... I have been using Julian dates as described by above for many years (coversion of different dating systems. Basically to get from one to another you convert to Julian Day numbers. Apart from that I was amazed by your contribution and would like you put a cmdbutton on a form with THIS 'code' (such as it is)

Private Sub Command1_Click()

Dim DteHold As Date
Dim JulDate As String

DteHold = #12/28/1899# ' Yields -2 in the message box
'DteHold = #12/29/1899# ' Yields -1 in the message box
'DteHold = #12/30/1899# ' ?????
'DteHold = #12/31/1899# ' Yields 1 in the message box
'DteHold = #1/1/1900# ' Yields 2 in the message box
'DteHold = #1/2/1900# ' Yields 1 in the message box
'DteHold = #2/28/1900# ' Yields 1 in the message box
'DteHold = #2/29/1900# ' ????
'DteHold = #7/20/2002# ' Yields 37457 in the message box

'show it as a string
JulDate = LTrim(Str(Int(CDbl(DteHold))))
MsgBox JulDate

End Sub

You need to run the code several times...each time UNrem a line AND look at the code!! and Run it. Pay attention to the first line with ????
Please comment 1) Wht the Code CHANGES when you unREM that line and 2) why the changed code gives the 'correct' answer?
and Doesn't the unREMing of this line suggest a a great way to validate a date? i.e stick user input between # # and trap any error!