Date Functions

[Dieter]

(01-16-2015 08:40 AM)Dieter Wrote:  Here is a first suggestion for the N2J routine. It handles dates between 1 Jan 4713 BC and 31 Dec 9999. Input between 5 and 14 Oct 1582 (undefined) is rejected.

Finally for the record:
Here is the VBA function I use in Excel (where the built-in functions have a very limited working range and may even return wrong results):

Code:


Const LastJulian = 2299160   ' last Julian date = 04 Oct 1582
Const FirstGregorian = LastJulian + 11  ' first Gregorian date = 15 Oct 1582

Function Date2JD(d, m, y)

If m < 3 Then m = m + 12: y = y - 1
JD = (1461 * (y + 4716)) \ 4 + (153 * (m + 1)) \ 5 + d - 1524

If JD > LastJulian Then
   If JD < FirstGregorian Then
      JD = -1   ' error marker - undefined date within transition period
   Else
      JD = JD + 2 - y \ 100 + y \ 400
   End If
End If

Date2JD = JD

End Function

In VBA, the backslash "\" stands for integer division, like IDIV or INT÷ on some HP calculators or DIV in some programming languages. Undefined dates (during the Julian/Gregorian transition) or dates before 4713 B.C. return negative results. An additional line of code could call an error handler.

Please note that the Julian/Gregorian transition point can be individually adjusted by the constants in the initial two lines. LastJulian is the Julian day number of the last date of the Julian calendar (here: 4 Oct 1582 = JD 2299160), while FirstGregorian represents the first date according to the Gregorian calendar (here: +11 days = 15 Oct 1582). So if you want to reflect the situation in Great Britain and its colonies where they switched from Wed, 2 Sep to Thu, 14 Sep 1752, simply set LastJulian=2361221 and FirstGregorian=LastJulian+12.

Dieter