converting a date to number of days since January 1, 0000 A.D. 1

[tbt102]

Hi all,

Has anyone implemented code that converts a date from month day, ccyy (may 20, 2003) to the number of days since January 1, 0000 A.D.?

Any help and/or sample code would be appreciated.

Thanks.

 

[johnwm]

The earliest date that can be handled by DateAdd and DateDiff is Jan 1st 100 AD, but that date is the date as given by the current (Gregorian) calendar. Since this calendar wasn't introduced until sometime between 1582 and 1918, depending on where you lived at the time, any dates beforehand were measured on the Julian calendar, and can't be related directly to current dates.

For a fuller explanation of calendar dates, look up Gregorian calendar on Google. You could start from :

http://scienceworld.wolfram.com/astronomy/GregorianCalendar.html

In summary, Jan 1st 0 A.D is a bit difficult to pin down!


________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'People who live in windowed environments shouldn't cast pointers.'

 

[Cresby]

<PEDANT> the year zero doesn't exist</PEDANT>

The monk who did the research in the middle ages (pre 1066) was not a mathetician. He did not recognise 0 as a number. This is why the millenium celebrations should have started Dec 31 2000. Now the techie stuff

leap years every 4 years
except years divisible by 100 which are not leap years
unless the year is divisible by 400 when it is a leap year.

Now I do forget which year the Gregorian calendar was adopted but different countries and regions/religions therein had a different take on whether they used the it or the Julian. AND I can't remember the difference but there were many moanings and cases in law about the lost 17 (or 14 days) But at least you have enough clues to go looking for the facts. The nubers are finite (apart from year dot) but there is a question about 50 of your years unless you go with Pope Gregory's edict (whatever that was) AND that is no Papal Bull. I always thought that Gregorian as a game of chants!

Now have you considered the leap seconds they throw at us whenever they feel like it? It is all to normalise the 365.25 (v approx) with the height of the sun at midday on the 21st June (or is it 23rd?) - Dare I mention precession? Moon's gravity? blah blah blah..........

 

[ADoozer]

check out

thread222-368305

good luck!

If somethings hard to do, its not worth doing - Homer Simpson
------------------------------------------------------------------------
A General Guide To Excel in VB FAQ222-3383
File Formats Galore @

http://www.wotsit.org

 

[johnwm]

Thanks for that reference ADoozer - it didn't come up on my search. However as I said earlier DateDiff doesn't go back before 100AD and the calendar has been messed with since then!


________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'People who live in windowed environments shouldn't cast pointers.'

 

[ADoozer]

luckily i had the thread number written down (for whatever reason )

i just posted it because theres a few more date related links at the bottom of the thread

If somethings hard to do, its not worth doing - Homer Simpson
------------------------------------------------------------------------
A General Guide To Excel in VB FAQ222-3383
File Formats Galore @

http://www.wotsit.org

 

[MichaelRed]

Per some 'optimizstion' of the search engine, the results are limited to threads which are ~ less than one year old. Older threads still exist and can be 'un-earthed' in some manners (e.g. knowing the thread # thinnggyyy).




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[chiph]

Cresby is correct - in order to know the date, you also have to know where you are. The UK didn't accept the Gregorian calendar until much later than most of Europe, mostly because of political reasons (England had recently become Protestant). The US didn't adopt some of the changes because of resentment against the British (recent events at the time -- 1776, war for independence, etc).

Chip H.

 

[NotSoVisual]

Are you still looking for a Routine?

 

[tbtcust]

hi NotSoVisual. still looking. I never got this resolved. Thanks in advance for any help.

 

[DrJavaJoe]

It's been 595 days since the original post I sure hope he found a solution by now, but I'm curious what you have, so let us have it.


List of Tsunami Relief Donations Sites

Two strings walk into a bar. The first string says to the bartender: 'Bartender, I'll have a beer. u.5n$x5t?*&4ru!2[sACC~ErJ'. The second string says: 'Pardon my friend, he isn't NULL terminated'.

 

[NotSoVisual]

The following code will give the Julian Day Number (the count of days since noon of 1/1/4712BC of a Julian or Gregorian date. It can easily turned into a function (Note the similarity of the THEN and ELSE portions and test in the middle.) So what you need to do is find the JDN of your your initial date and use it as a minus in the function and you are home.

IF (JGflag ="J" THEN
(DAY + INT(367 * (MONTH + ((INT((14 - MONTH) / 12)) * 12) - 2) / 12) + INT(1461 * (YEAR + 4800 - (INT((14 - MONTH) / 12))) / 4) - 32113)

Else

(DAY + INT(367 * (MONTH + ((INT((14 - MONTH) / 12)) * 12) - 2) / 12) + INT(1461 * (YEAR + 4800 - (INT((14 - MONTH) / 12))) / 4) - 32113) - (INT(3 * INT((YEAR + 100 - (INT((14- MONTH) / 12))) / 100) / 4) - 2)))

end if

NOTE.. Then and Else are each a single line of code.

Try it and let me know. I have used it for years and have checked it on Astronomy tables listing JDN in 10 day intervals from -2500 to 500. I think you can perform a similar test by checking VB dates since 1900. Good luck.


Dr Joe..This is day 596 and now he has it.

 

[MichaelRed]

NotSoVisual .

I don't even get close.

"DAY", "MONTH" and "YEAR" are reserved words (functions) in VB(A). They extract the relevant portions of a Formatted Date, as in:

? Day(Date)
 6 

? Month(Date)
 1 

? Year(Date)
 2005 
[code]

representing the breakdown of today's date as the sixth day of the first month of the current year. So the insertion of the "date" as an argument for each of these terms is attempted as a "first approximation" of your meaning.

Obviously, I'm not "getting it", perhaps you would like to stretch your memory, enhance my understanding with a brief explination of the meaning of the various terms and the input arguments you use to get these reliable results?







MichaelRed

 

[tbtcust]

hi NotSoVisual. Your code worked like a charm. I had to tinker with the parentheses a bit. See the code below for details. Once that was fix I got what I was looking for. I code a routine that tested the results for Jan of this year and all of last year and they were perfect. Thanks.

Do you happen to have code that changes the results of the Gregorian calculation to mm/dd/yy? Thanks again.


Dim jd As Double
Dim year As Long
Dim month As Integer
Dim day As Integer
Dim InitialDate As Double
day = 4
month = 1
year = 2005
InitialDate = 1753185

JGflag = "J"
If JGflag = "J" Then
jd = (day + Int(367 * (month + ((Int((14 - month) / 12)) * 12) - 2) / 12) _
+ Int(1461 * (year + 4800 - (Int((14 - month) / 12))) / 4) - InitialDate)
Else
jd = (day + Int(367 * (month + ((Int((14 - month) / 12)) * 12) - 2) / 12) _
+ Int(1461 * (year + 4800 - (Int((14 - month) / 12))) / 4) - InitialDate) _
- (Int(3 * Int((year + 100 - (Int((14 - month) / 12))) / 100) / 4) - 2)
End If
Text3.Text = jd

 

[NotSoVisual]

I am sorry. I had converted function to use in an excel sheet and had cell names where I now have MONTH DAY & YEAR Simply change these to be appropriate variable names (iMONTH, iDAY & iYEAR is ok). Depending on if you are using Julian or Gregorian dates make your flag. so it will go something like:

IF (JGflag ="J" THEN
JDN= (IDAY + INT(367 * (IMONTH + ((INT((14 - IMONTH) / 12)) * 12) - 2) / 12) + INT(1461 * (IYEAR + 4800 - (INT((14 - IMONTH) / 12))) / 4) - 32113) -MyStartPoint

Else

JDN=(IDAY + INT(367 * (IMONTH + ((INT((14 - IMONTH) / 12)) * 12) - 2) / 12) + INT(1461 * (IYEAR + 4800 - (INT((14 - IMONTH) / 12))) / 4) - 32113) - (INT(3 * INT((IYEAR + 100 - (INT((14- IMONTH) / 12))) / 100) / 4) - 2))) -MyStartPoint

end if

Assume you are working with “Gregorian dates” (as if theye were translated backwards from whereever and whenever they were adopted and you iput into this formula.

iMonth,iDay,IYear SAMPLE INPUT
1/1/1 results in a JDN for that date = 1721426

1/6/2004 results in a JDN for that date = 2453333

12/31/1899 results in a JDN for that date = 2415020

I stuck in a variable for you to fill in as a constant depending on where you want to start from in your numbering.
If you like Gates wanted to start on 1/1/1900 you would need to set MyStartPoint to 2415020 (ie. the JDN of the day before.

It is interesting to note that an input of Jan 50 xxxx will give the correct JDN depending upon the year. I use this 'feature' to do date arith. I will post up the conversion back if you like (and if this works for you)

 

[zarkon4]

And now some physics professor is wanting to change from the gregorian to his new system.

http://www.newscientist.com/article.ns?id=dn6835

 

[tbtcust]

Hi NotSoVisual,

I'd like you to post up the conversion back when you have a chance. The one I have does not work well. Thanks.

 

[NotSoVisual]

tbt103...Here is what I use for both functions. (I am in the middle of changig systems and am more messed up than usual) I have never had a problem with either but also do not work with time (h:m:s)in my dates.

Public Function DateFor(JDN, JGMode)

Dim JG, M, Y

Dim DV As String

Dim D, G, Q, R, S, T, U, V As Double 'JD changed to JDN


JG = UCase(Left(JGMode, 1)): If JG <> "J" Then JG = "G"

If JG = "J" Then G = 0 Else G = 1

Q = G * Int((JDN / 36524.25) - 51.12264)
R = JDN + G + Q - Int(Q / 4)
S = R + 1524
T = Int((S / 365.25) - 0.3343)
U = Int(T * 365.25)
V = Int((S - U) / 30.61)

' Compute the raw, numerical calendar date elements
D = S - U - Int(V * 30.61)
M = (V - 1) + 12 * (V > 13.5)
Y = T - (M < 2.5) - 4716

' Here you have numerical values of D, M and Y
‘ Must convert into Some Date Format of your choice

' Day of the month (1 to 31)
D = Trim(D)

' Determine English month abbreviation (Jan to Dec)
M = " " & _
Mid("JanFebMarAprMayJunJulAugSepOctNovDec", 3 * (M - 1) + 1, 3)
M = M & " "

' Determine the year in BC|AD format
If Y < 0 Then
Y = Trim(1 - Y) & " BC"
Else
Y = Trim(Y) & " AD"
End If

' Finally, return the computed standard date string in
' the format "1 Jan 2000 BC|AD"
DateFor = D & M & Y

End Function



Public Function Change2JDN(ByVal D, M, Y)
Dim k As Integer
JDN = 0
k = Int((14 - M) / 12)
JDN = D + Int(367 * (M + (k * 12) - 2) / 12) + Int(1461 * (Y + 4800 - k) / 4) - 32113
If Greg = False Then Exit Function
JDN = JDN - (Int(3 * Int((Y + 100 - k) / 100) / 4) - 2)

End Function


Hope this helps. This code is not mine and I do not know whose it is but it has served me for many years.

 

[tbtcust]

NotSoVisual,

the code works perfectly. Thanks so much for your help.

 

[NotSoVisual]

Tbt103..Now that it is over I am interested, what will you use it for?