Reply to thread

Message

[QUOTE="Peo Sjoblom, post: 3641453"]
Here is a post from Ron Rosenfeld and it certainly looks pretty impossible
due to the calendar used for Jewish holidays:

Norman,

It is NOT a simple algorithm.

The year is both lunar based and solar based.  In addition, there are a
number
of "special" rules which can delay the start of the year for either
astronomical or ceremonial reasons. For example, Tishri 1 must never be a
Sunday, Wednesday or Friday.

Good luck on translating this to an Excel formula!

Here is a discussion by Scott Lee who wrote a C routine.

Original Copyright info:
' $selId: jewish.c,v 2.0 1995/10/24 01:13:06 lees Exp $
' Copyright 1993-1995, Scott E. Lee, all rights reserved.
' Permission granted to use, copy, modify, distribute and sell so long as
' the above copyright and this permission statement are retained in all
' copies.  THERE IS NO WARRANTY - USE AT YOUR OWN RISK.

' CALENDAR OVERVIEW
'
'     The Jewish calendar is based on lunar as well as solar cycles.  A
'     month always starts on or near a new moon and has either 29 or 30
'     days (a lunar cycle is about 29 1/2 days).  Twelve of these
'     alternating 29-30 day months gives a year of 354 days, which is
'     about 11 1/4 days short of a solar year.
'
'     Since a month is defined to be a lunar cycle (new moon to new moon),
'     this 11 1/4 day difference cannot be overcome by adding days to a
'     month as with the Gregorian calendar, so an entire month is
'     periodically added to the year, making some years 13 months long.
'
'     For astronomical as well as ceremonial reasons, the start of a new
'     year may be delayed until a day or two after the new moon causing
'     years to vary in length.  Leap years can be from 383 to 385 days and
'     common years can be from 353 to 355 days.  These are the months of
'     the year and their possible lengths:
'
'                       COMMON YEAR          LEAP YEAR
'          1 Tishri    30   30   30         30   30   30
'          2 Heshvan   29   29   30         29   29   30 (variable)
'          3 Kislev    29   30   30         29   30   30 (variable)
'          4 Tevet     29   29   29         29   29   29
'          5 Shevat    30   30   30         30   30   30
'          6 Adar I    29   29   29         30   30   30 (variable)
'          7 Adar II   --   --   --         29   29   29 (optional)
'          8 Nisan     30   30   30         30   30   30
'          9 Iyyar     29   29   29         29   29   29
'         10 Sivan     30   30   30         30   30   30
'         11 Tammuz    29   29   29         29   29   29
'         12 Av        30   30   30         30   30   30
'         13 Elul      29   29   29         29   29   29
'                     ---  ---  ---        ---  ---  ---
'                     353  354  355        383  384  385
'
'     Note that the month names and other words that appear in this file
'     have multiple possible spellings in the Roman character set.  I have
'     chosen to use the spellings found in the Encyclopedia Judaica.
'
'     Adar II, the month added for leap years, is sometimes referred to as
'     the 13th month, but I have chosen to assign it the number 7 to keep
'     the months in chronological order.  This may not be consistent with
'     other numbering schemes.
'
'     Leap years occur in a fixed pattern of 19 years called the metonic
'     cycle.  The 3rd, 6th, 8th, 11th, 14th, 17th and 19th years of this
'     cycle are leap years.  The first metonic cycle starts with Jewish
'     year 1, or 3761/60 B.C.  This is believed to be the year of
'     creation.
'
'     To construct the calendar for a year, you must first find the length
'     of the year by determining the first day of the year (Tishri 1, or
'     Rosh Ha-Shanah) and the first day of the following year.  This
'     selects one of the six possible month length configurations listed
'     above.
'
'     Finding the first day of the year is the most difficult part.
'     Finding the date and time of the new moon (or molad) is the first
'     step.  For this purpose, the lunar cycle is assumed to be 29 days 12
'     hours and 793 halakim.  A halakim is 1/1080th of an hour or 3 1/3
'     seconds.  (This assumed value is only about 1/2 second less than the
'     value used by modern astronomers -- not bad for a number that was
'     determined so long ago.)  The first molad of year 1 occurred on
'     Sunday at 11:20:11 P.M.  This would actually be Monday, because the
'     Jewish day is considered to begin at sunset.
'
'     Since sunset varies, the day is assumed to begin at 6:00 P.M.  for
'     calendar calculation purposes.  So, the first molad was 5 hours 793
'     halakim after the start of Tishri 1, 0001 (which was Monday
'     September 7, 4761 B.C. by the Gregorian calendar).  All subsequent
'     molads can be calculated from this starting point by adding the
'     length of a lunar cycle.
'
'     Once the molad that starts a year is determined the actual start of
'     the year (Tishri 1) can be determined.  Tishri 1 will be the day of
'     the molad unless it is delayed by one of the following four rules
'     (called dehiyyot).  Each rule can delay the start of the year by one
'     day, and since rule #1 can combine with one of the other rules, it
'     can be delayed as much as two days.
'
'         1.  Tishri 1 must never be Sunday, Wednesday or Friday.  (This
'             is largely to prevent certain holidays from occurring on the
'             day before or after the Sabbath.)
'
'         2.  If the molad occurs on or after noon, Tishri 1 must be
'             delayed.
'
'         3.  If it is a common (not leap) year and the molad occurs on
'             Tuesday at or after 3:11:20 A.M., Tishri 1 must be delayed.
'
'         4.  If it is the year following a leap year and the molad occurs
'             on Monday at or after 9:32:43 and 1/3 sec, Tishri 1 must be
'             delayed.
'
' GLOSSARY
'
'     dehiyyot         The set of 4 rules that determine when the new year
'                      starts relative to the molad.
'
'     halakim          1/1080th of an hour or 3 1/3 seconds.
'
'     lunar cycle      The period of time between mean conjunctions of the
'                      sun and moon (new moon to new moon).  This is
'                      assumed to be 29 days 12 hours and 793 halakim for
'                      calendar purposes.
'
'     metonic cycle    A 19 year cycle which determines which years are
'                      leap years and which are common years.  The 3rd,
'                      6th, 8th, 11th, 14th, 17th and 19th years of this
'                      cycle are leap years.
'
'     molad            The date and time of the mean conjunction of the
'                      sun and moon (new moon).  This is the approximate
'                      beginning of a month.
'
'     Rosh Ha-Shanah   The first day of the Jewish year (Tishri 1).
'
'     Tishri           The first month of the Jewish year.
'
' ALGORITHMS
'
'     SERIAL DAY NUMBER TO JEWISH DATE
'
'     The simplest approach would be to use the rules stated above to find
'     the molad of Tishri before and after the given day number.  Then use
'     the molads to find Tishri 1 of the current and following years.
'     From this the length of the year can be determined and thus the
'     length of each month.  But this method is used as a last resort.
'
'     The first 59 days of the year are the same regardless of the length
'     of the year.  As a result, only the day number of the start of the
'     year is required.
'
'     Similarly, the last 6 months do not change from year to year.  And
'     since it can be determined whether the year is a leap year by simple
'     division, the lengths of Adar I and II can be easily calculated.  In
'     fact, all dates after the 3rd month are consistent from year to year
'     (once it is known whether it is a leap year).
'
'     This means that if the given day number falls in the 3rd month or on
'     the 30th day of the 2nd month the length of the year must be found,
'     but in no other case.
'
'     So, the approach used is to take the given day number and round it
'     to the closest molad of Tishri (first new moon of the year).  The
'     rounding is not really to the'closest* molad, but is such that if
'     the day number is before the middle of the 3rd month the molad at
'     the start of the year is found, otherwise the molad at the end of
'     the year is found.
'
'     Only if the day number is actually found to be in the ambiguous
'     period of 29 to 31 days is the other molad calculated.
'
'     JEWISH DATE TO SERIAL DAY NUMBER
'
'     The year number is used to find which 19 year metonic cycle contains
'     the date and which year within the cycle (this is a division and
'     modulus).  This also determines whether it is a leap year.
'
'     If the month is 1 or 2, the calculation is simple addition to the
'     first of the year.
'
'     If the month is 8 (Nisan) or greater, the calculation is simple
'     subtraction from beginning of the following year.
'
'     If the month is 4 to 7, it is considered whether it is a leap year
'     and then simple subtraction from the beginning of the following year
'     is used.
'
'     Only if it is the 3rd month is both the start and end of the year
'     required.
'
' TESTING
'
'     This algorithm has been tested in two ways.  First, 510 dates from a
'     table in "Jewish Calendar Mystery Dispelled" were calculated and
'     compared to the table.  Second, the calculation algorithm described
'     in "Jewish Calendar Mystery Dispelled" was coded and used to verify
'     all dates from the year 1 (3761 B.C.) to the year 13760 (10000
'     A.D.).
'
'     The source code of the verification program is included in this
'     package.
'
' REFERENCES
'
'     The Encyclopedia Judaica, the entry for "Calendar"
'
'     The Jewish Encyclopedia
'
'     Jewish Calendar Mystery Dispelled by George Zinberg, Vantage Press,
'     1963
'
'     The Comprehensive Hebrew Calendar by Arthur Spier, Behrman House
'
'     The Book of Calendars [note that this work contains many typos]

--ron "

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
[URL="http://www.nwexcelsolutions.com"]www.nwexcelsolutions.com[/URL]
"It is a good thing to follow the first law of holes;
if you are in one stop digging."  Lord Healey
[/QUOTE]

Verification