holiday dates

I want to be able to have excel determine last years holiday dates by looking at this years date which is entered in a cell. Example:

Cell a1 is where a person would enter Memorial day 2006
Cell b1 would display last years memorial date

I want to be able to do this with all the major and minor holidays
New Year
Easter
Memorial day
july 4th
labor day
thanksgiving
christmas
and possibly the jewish holidays as well.
Any ideas?? I'm lost

I assume you know how to get the dates of previous Christmas, New Year and
July 4th
The only really tricky is Eastern Sunday, fortunately there was a
competition in Germany

with the year in A1

=DOLLAR(("4/"&A1)/7+MOD(19*MOD(A1,19)-7,30)*14%,)*7-6

by Tomas Jansen, format as date



for Memorial day

=DATE(A1,5,31)-WEEKDAY(DATE(A1,5,31)-2)

for US Labor Day

=DATE(A1,9,8)-WEEKDAY(DATE(A1,9,6))

for US Thanksgiving

=DATE(A1,11,29)-WEEKDAY(DATE(A1,11,3))

--

Regards,

Peo Sjoblom

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


"bucci" <[email protected]> wrote in
message news:[email protected]...
>
> I want to be able to have excel determine last years holiday dates by
> looking at this years date which is entered in a cell. Example:
>
> Cell a1 is where a person would enter Memorial day 2006
> Cell b1 would display last years memorial date
>
> I want to be able to do this with all the major and minor holidays
> New Year
> Easter
> Memorial day
> july 4th
> labor day
> thanksgiving
> christmas
> and possibly the jewish holidays as well.
> Any ideas?? I'm lost
>
>
> --
> bucci
> ------------------------------------------------------------------------
> bucci's Profile:
> http://www.excelforum.com/member.php...o&userid=34889
> View this thread: http://www.excelforum.com/showthread...hreadid=546323
>

Great, thank you this works well

Any ideas on the Jewish holidays?

Yom Kippur
Chanukah
Passover

Here is a post from Ron Rosenfeld and it certainly looks pretty impossible
due to the calendar used for Jewish holidays:



"On Thu, 25 Dec 2003 08:28:05 +1100, "Norman Harker"
<[email protected]>
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 "






--

Regards,

Peo Sjoblom

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


"bucci" <[email protected]> wrote in
message news:[email protected]...
>
> Great, thank you this works well
>
> Any ideas on the Jewish holidays?
>
> Yom Kippur
> Chanukah
> Passover
>
>
> --
> bucci
> ------------------------------------------------------------------------
> bucci's Profile:
> http://www.excelforum.com/member.php...o&userid=34889
> View this thread: http://www.excelforum.com/showthread...hreadid=546323
>

Dear Dr Eisenberg,

I read your article. I have a question: I reas on the web (dr irv
bromberg):
"The Traditional Hebrew Calendar has constant month lengths, but
something has to vary to accomodate the non-integral mean length of the
molad (representing the mean lunar cycle), which equals 29 days 12
hours 44 minutes 1 part (each "part" equals 3 1/3 seconds = 18 parts
per minute), and this is accomplished by adjusting the lengths of the
two months after the previous month of Tishrei, that is Cheshvan and
Kislev, according to the following four rules:"

My understanding is that the real reason for the Dechiyot is the above,
and the four cases of when you will do it is just a secondary benefit.

If this is the case, and we disregard the secondary benefits, then it
is not clear to me. What could have happend if we do not do dechiyot
and every year will be shana kesidra, one month 30, the next 29, etc,
for the entire period of 19 years?

What is the REAL reason for the dechiyot?

Thanks,

Aaron
[email protected]
Peo Sjoblom wrote:
> Here is a post from Ron Rosenfeld and it certainly looks pretty impossible
> due to the calendar used for Jewish holidays:
>
>
>
> "On Thu, 25 Dec 2003 08:28:05 +1100, "Norman Harker"
> <[email protected]>
> 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 "
>
>
>
>
>
>
> --
>
> Regards,
>
> Peo Sjoblom
>
> Excel 95 - Excel 2007
> Northwest Excel Solutions
> www.nwexcelsolutions.com
> "It is a good thing to follow the first law of holes;
> if you are in one stop digging." Lord Healey
>
>
> "bucci" <[email protected]> wrote in
> message news:[email protected]...
> >
> > Great, thank you this works well
> >
> > Any ideas on the Jewish holidays?
> >
> > Yom Kippur
> > Chanukah
> > Passover
> >
> >
> > --
> > bucci
> > ------------------------------------------------------------------------
> > bucci's Profile:
> > http://www.excelforum.com/member.php...o&userid=34889
> > View this thread: http://www.excelforum.com/showthread...hreadid=546323
> >