date conversion

Is there an easy way to build a two-column table with gregorian dates in one column and Hebrew dates in the other? And then for each year, have it automatically update - so I don't have to re-type them all again? I want to say that Jan 1 equals XYX 3 and then have it calculate the remainder of the date equivalents. And then next year, I want to re-set the Jan 1 date to XYZ 9 and then have it calculate the remainder of the date equivalents.

Hi web-time!

We might be able to help if you could provide the algorithm for the
Hebrew calendar.

No promises!!

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia

Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
"web_time" wrote in message
...
Is there an easy way to build a two-column table with gregorian
dates in one column and Hebrew dates in the other? And then for each
year, have it automatically update - so I don't have to re-type them
all again? I want to say that Jan 1 equals XYX 3 and then have it
calculate the remainder of the date equivalents. And then next year,
I want to re-set the Jan 1 date to XYZ 9 and then have it calculate
the remainder of the date equivalents.

Norman,

Easier said than done. The hebrew calender is lunar (rather than solar) and therefore does not match up well. There are also leap months. There are many java and C scripts available, but they only convert one day at a time. I need the whole year---- and then an automated recalculation at the beginning of each year. I dont mind finding the January 1 conversion,I just have no desire to key inthe following 364 entries....

I'm afraid I wasn't much help - with your offer to help me...

Thanks

Hi Web-time!

An algorithm might help because, for example, we can calculate the
date for Easter which is lunar based.

Probably the best place to start will be the basis for Jewish New Year
and then the basis for the addition of the Leap month.

Maybe after Christmas I'll do a web search as I'm sure we'll get
something that can be used.

Seasons Greetings from a sunny Australia where Sydney is forecast for
+ 30 C.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia

Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
"Web_time" wrote in message
...
Norman,

Easier said than done. The hebrew calender is lunar (rather than
solar) and therefore does not match up well. There are also leap
months. There are many java and C scripts available, but they only
convert one day at a time. I need the whole year---- and then an
automated recalculation at the beginning of each year. I dont mind
finding the January 1 conversion,I just have no desire to key inthe
following 364 entries....

I'm afraid I wasn't much help - with your offer to help me...

Thanks

On Thu, 25 Dec 2003 08:28:05 +1100, "Norman Harker"
wrote:

An algorithm might help because, for example, we can calculate the
date for Easter which is lunar based.

Probably the best place to start will be the basis for Jewish New Year
and then the basis for the addition of the Leap month.

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

Aaaaaaaaah!!!

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia

Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
"Ron Rosenfeld" wrote in message
...
On Thu, 25 Dec 2003 08:28:05 +1100, "Norman Harker"

wrote:

An algorithm might help because, for example, we can calculate the
date for Easter which is lunar based.

Probably the best place to start will be the basis for Jewish New
Year
and then the basis for the addition of the Leap month.

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

On Thu, 25 Dec 2003 15:20:27 +1100, "Norman Harker"
wrote:

Aaaaaaaaah!!!

Was that the dawning of understanding or the screech of despair ggg?

Happy Holidays!






--ron

Sometimes, one implies the other.

gd&r

Ron Rosenfeld wrote:

On Thu, 25 Dec 2003 15:20:27 +1100, "Norman Harker"
wrote:

Aaaaaaaaah!!!

Was that the dawning of understanding or the screech of despair ggg?

Happy Holidays!

--ron

--

Dave Peterson

Hi Ron!

Despair!!

But there does seem to be a method of calculation albeit a very
complex one.


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia

Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

On Fri, 26 Dec 2003 01:13:19 +1100, "Norman Harker"
wrote:

Hi Ron!

Despair!!

But there does seem to be a method of calculation albeit a very
complex one.

That's for sure.

There are calculators on the web. Also, for many versions now, Calendar
Creator (I think it's now published by Broderbund) has included Hebrew date
logic.


--ron