Crystal
Reports Training by Ken Hamady, MS, Reporting and Training Nationwide
Free
Crystal Reports formula examples from
KenHamady.com
To
learn
the techniques used in these formulas get:
How to find Good
Friday (or Easter) for any year:
This is Mikes
original formula with full historical comments and a detailed
description of what each step is doing. It will work just
as it is, or you can use the short version.
Both should give the exact same
result.
// FUNCTION:
@GoodFridayHoliday
// LANGUAGE:
Crystal Reports 8.5 / Crystal syntax
// REQUIRES:
Functions; @yr
// RETURNS:
dateVar: date of good friday
// DESCRIPTION:
// Calculates
the date of Good Friday (Friday before Easter)
// CHANGE LOG:
// Written
6/28/03 by Mike Cook
numberVar yr :=
{@yr}; // The year for which to determine the date of Good Friday.
// The following calculation of the Date for Easter is based on Oudin's
algorithm for
// determining the date of Easter and follows the algorithm given in
the New Scientist
// magazine, issue No. 228 (Vol. 9) page 828 (March 1961). It was
originally coded in
// Multics PL/1 by Dennis Capps in 9/80 to fix bug a in the easter
calculation in a
// program called calendar originally written by Tom Van Vleck of MIT
in 1972. It was
// converted to Crystal and modified to return the date of Good Friday
by Mike Cook in
// 2003. The comments are mostly from the original PL/1.It should
be valid for any date
// from 1583 to 4099, so we may have a year 4K issue.
Local numberVar a := yr Mod 19; // Find
position of year in 19-year Lunar Cycle,
// called the Golden Number.
Local numberVar b := yr \ 100; // b is century
number
Local numberVar c := yr Mod 100; // c is year number
within century
Local numberVar d := b \ 4;
Local numberVar e := b Mod 4; // d, e
and i, k are used in leap year adjustments.
Local numberVar i := c \ 4;
Local numberVar k := c Mod 4;
// The next step computes a correction factor used in the following step
// which computes the number of days between the spring equinox
// and the first full moon thereafter. The correction factor is
needed
// to keep the approximation in line with the observed behavior of the
moon.
// It moves the full moon date back by one day eight times in every
2500 years,
// in century years three apart, with four years at the end of the
cycle.
// The constant 13 corrects the correction for the fact that this
// cycle was decreed to start in the year 1800.
Local numberVar g := (8 * b + 13) \ 25;
// Now the number of days after the equinox (21 March, by definition)
that
// we find the next full moon. This is a number between 0 and 29.
// The term 19*a advances the full moon 19 days for each year of the
// Lunar Cycle, for a total of 361 days in the 19 years. The
other 4.24 days
// are made up when a returns to zero on the next cycle. Thus, the
// full moon dates repeat every 19 years. The term b-d advances
the
// date by one day for three out of every four century years, the
// years which are not leap years although divisible by 4.
// The term g is the correction factor calculated above, and 15
// adjusts this whole calculation to the actual conditions at that
// date on which the scheme began, probably in Oct of 1582.
Local numberVar h := ((19 * a) + b - d - g + 15) Mod 30;
// Now we are interested in how many days we have to wait after the
// full moon until we get a Sunday (which has to be definitely after
// the full moon). The following step calculates a number l which
is
// one less than the number of days. Every ordinary year ends on
the
// same day of the week on which it started; a leap year ends on
the
// day of the week following the one on which it started. Thus, if
// it is known on what day of the week a date occurred in any year
// it is possible to calculate its day of the week in another year
// by marching through the week one day for each regular year and
// two for each leap year.
// The term k is the number of ordinary
years
// since the last leap year; each such year brings the date of the
// full moon one day closer to Sunday, and so reduces the number of
// days to be waited (unless it goes negative, but modular arithmetic
// theory makes -1 := 6 where the modulus is 7).
// The term i is the number of leap years
so far in the current century.
// each leap year has with it three ordinary years, and each such group
// advances the day of the week by 5 days. But in modulo 7
arithmetic
// subtracting 5 days is equivalent to adding 2 days. So we add
// two days for each group of four years in the current century.
// Since a century consists of 25 groups
of four years, it advances
// the day of the week by 124 or 125 days depending on whether the
// century year is an ordinary or leap year. The remainders when
// these numbers are divided by seven are 5 and 6 respectively.
// The term e is the number of ordinary century years since the
// last leap century year. As with the groups of four years, we
// add two days for each rather than subtract 5 for each.
// Every fourth century year is a leap
year; therefore,
// each group of four centuries advances the day of the week by
// 3*5+6 := 21 days, or 0 in modulo 7 arithmetic, and no
// term is necessary for time before the last leap century year.
// The constant term 32 adjusts the calculation for the day of the
// week of the equinox when the scheme was put into effect. It
also
// is larger than necessary by 28 in order to assure that the
// subtractions of k and h never reduce the dividend below 0.
// Thus, mod(2*e + 2*i - k + 32, 7) gives
one less than the number
// of days between the equinox and its following Sunday. But we
need to
// calculate the number of days after the full moon. The term h,
// calculated in the previous step, gives the number of days after
// the equinox that the full moon occurs. Each of those days
brings
// the full moon closer to the actual Sunday of Easter,
// so it reduces the number of days after the full moon until Easter.
// (Again, if h > 6, modular arithmetic theory readjusts the result
to
// another cycle of 0 to 6, and here the constant 32 keeps the dividend
> 0.)
Local numberVar l := ((2 * e) + (2 * i) - k + 32 - h) Mod 7;
// The calendar set up by Pope Gregory XIII and his advisor, the
astronomer
// Clavius, provided for official full moon dates as well as matching
// the equinoxes and solstices with their nominal dates. But,
since
// the period of the moon is not an exact number of days, some fudging
// was needed here as elsewhere in the calendar system. Some of
the
// periods between successive full moons in the Lunar Cycle are 30 days,
// some 29 days. Clavius then arranged the periods carefully so
// that if a full moon fell on 20 March (the day before the equinox),
// the period following it would be of 29 days. The effect of this
// arrangement is that Easter can never occur later than 25 April.
// The above calculations assume uniform 30-day lunar periods. In
rare
// cases (e.g., 1954 and 1981) one of these 29-day lunar periods causes
// the full moon to fall on a Saturday where a 30-day period would put
// it on a Sunday. The following step calculates the fudge factor
for
// this situation. The result m is 0 if no fudging is necessary,
or
// 1 if fudging is required.
Local numberVar m := (a + (11*h) + (19*l)) \ 433;
// Now we have calculated the number of days which will elapse between
// 21 march and Easter: h + (l + 1) - 7*m. The last two steps
// turn this into a month and day. In the first expression, the
constant
// 90 assures that the the quotient will be at least 3 (= March).
// If the elapsed days exceed 9, then the quotient will be 4 (= April).
// In the second expression, if month := 3 then 33*month + 19 := 118
and the
// remainder of that part of the expression is 22; when month :=
3,
// l + h - 7*m < 10, so 22 < day <= 31.
// If month := 4, 33*month := 132, and since h + l - 7*m > 9, the
whole
// expression satisfies 5*32 := 160 < expr. The remainder is
greater
// than 0 and less than 26.
// The following converts the number of days to Easter to the number of
days
// to Good Friday: h + (l + 1) - 7*m -2. (2 days before Easter)
Local numberVar days_to_good_friday := h + l - (7*m) - 2;
Local numberVar mo := (days_to_good_friday + 90) \ 25;
Local numberVar da := (days_to_good_friday + (33 * mo) + 19) Mod 32;
cdate ( yr, mo,
da) // Returns the date of Good Friday