Traducir el blog

How to make the Easter dates

Posted on septiembre 18, 2010 by Excel Pedro Wave

In attempting to solve a problem, sometimes there are computations that make us as "Easter Bunnies" and there are very difficult to solve, if not impossible, or resolution continues endlessly. These computations can be formulas, functions, mathematical and computer algorithms, or from daily living such, as reaching end of the month!

In computer programs, software programmers always snuck Easter eggs, which are light jokes, like the calculator that allows Google "once in a blue moon", but sometimes become heavy when they are a result of our frequent calculation bugs...

Those who know me already know that I like the complicated calculations, and that the moon influences me by my zodiac sign, and I have also posted on this blog several calendars with the days religious and pagan holidays. The title says it very clear, How to make the Easter dates is about how to obtain the ecclesiastical calendar computation, ie, the set of calculations necessary to determine the date of Easter and other movable feasts, which are deducted from Easter Sunday.

During the Renaissance the calculation tables for Easter were based on the golden number. When calculating the date of Easter is called Computus in latin and has served for religious, astronomers, mathematicians, programmers and mental calculators rack their brains until these days.

Question is how to make the Easter dates, based on Gregorian computus, available to local Churches all over the world so that simultaneous commemoration would be, following the Gregorian Reform of the Calendar and do not ever coincide with the Jewish Passover, which takes place regardless of the day of the week.

This has led me to the collection of some of the algorithms for calculation of Easter Sunday (see here) its implementation in various programming languages and it is based on the first full moon of the spring. In the journal Science Today is an C++ algorithm and others in the book Mapping time: the calendar and its history by E. G. Richards.

As Excel is the spreadsheet for excellence, many algorithms have been developed based on VBA and formulas to get the Easter Sunday' dates and even a contest to create the shortest possible formula to obtain between 1900 and 2078, sponsored by Hans Herber, an Excel master in Germany.

The following attached Excel files are my own collection of algorithms for calculating the Easter Sunday for Western churches:

EasterSundayCalculation.xls 
(Formulas and UDF for Excel 2003 to 2010)
EasterSundayCalculation.xlsx 
(Formulas for Excel 2007 and 2010)
EasterSundayCalculation.zip 
(Formulas for Excel 2007 and 2010)

To do the comparison always the year is entered as 4-digit (YYYY) in cell A1.

In row 1 we get the Easter Sunday for each formula and User Defined Functions - UDF.

In rows 10-2029 appears the calculation of Easter Sunday for years 1900 to 4099, respectively.

Known limitations of Excel forbid to calculate the dates for previous Gregorian years, from 1583 to 1899.

NOTE: Excel for Windows uses the 1900 date system and Excel for Macintosh uses the 1904 date system.

You can view and download Easter computations from the Microsoft OneDrive cloud. From here without leaving the blog:



VBA Functions:

The most recognized algorithm is from the United States Naval Observatory - USNO and it was created by J.-M. Oudin.

Gets the count of all the Sundays of Easter for the Gregorian calendar from 1583 until 4099, although the range begins in 1900 for Excel:
Write this in any cell:
=EasterUSNO(A1)

Another algorithm slightly longer is the one created by Greg Mallen according to studies and tables of Ronald W. Mallen:
Called with:
=EasterDate(1,1,A1)

A considerably shorter one its quoted on Cheap Pearson website:

Finally I've included a function that fails more than the original formula which is based and which was proposed by Norbert Hetterich for the contest mentioned above.

Excel Formulas:

The formulas that support these algorithms are found on the Web in English and German, in its original version, and are translated here to English to enjoy them.

Thomas Jansen asked this curious formula that works between years 1900 and 2203:

Another version from Tomas Jansen:

I recommend using this version transformed into international date format:

Norbert Hetterich suggested the following but it fails in the year 2079:

I recommend using this version transformed into international date format:

Finally, I've obtained the next megaformula based on Greg Mallen and Ronald W. Mallen algorithms:

This last Megaformula is written in Excel 2007 and 2010 versions because it is not possible to write so large formulas in Excel 2003.

Now just I need help understanding these calculations!
Please, post a comment if you can help me.

PD: What has this to do with Graphical User Interfaces? That the information and results provided must be accurate and repeatable, as Easter dates...

Traducción al español aquí.

2 Response to "How to make the Easter dates"

.
gravatar
Ben Schwartz Says....

Does the last mega formula, as you call it, work for all years?

.
gravatar
Excel Pedro Wave Says....

Hy Ben, if you have downloaded my example file, EasterSundayCalculation.xlsx, the megafórmula is in column H and you can check that is effective in the year range from 1900 to 4099, so as the algorithm in which derives, from Greg Mallen & Ronald W. Mallen, as you can see in the column I.

I am very grateful that you to be one of my blog readers.

Leave A Reply

Dime si te gusta lo que lees y, si no te gusta, dime por qué. Tengo habilitada la moderación de comentarios. Tu comentario se publicará pronto.

Mi lista de blogs