#ExcelPedroWave

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:

Public Function EasterUSNO(YYYY As Integer) As Long

    'The following algorithm for computing the date of Easter is based on the algorithm of Oudin (1940).
    'It is valid for any Gregorian year, Y.
    'All variables are integers and the remainders of all divisions are dropped (\ operator).
    
    Dim C As Integer
    Dim N As Integer
    Dim K As Integer
    Dim I As Integer
    Dim J As Integer
    Dim L As Integer
    Dim M As Integer
    Dim D As Integer
    
    C = YYYY \ 100                  'first 2 digits of year
    N = YYYY Mod 19                 'remainder of year / 19
    K = (C - 17) \ 25
    I = C - C \ 4 - (C - K) \ 3 + 19 * N + 15
    I = I Mod 30
    I = I - (I \ 28) * (1 - (I \ 28) * (29 \ (I + 1)) * ((21 - N) \ 11))
    J = YYYY + YYYY \ 4 + I + 2 - C + C \ 4
    J = J Mod 7
    L = I - J
    M = 3 + (L + 40) \ 44
    D = L + 28 - 31 * (M \ 4)
    
    EasterUSNO = DateSerial(YYYY, M, D)
    
End Function

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:

Public Function EasterDate(D As Integer, M As Integer, y As Integer) As Date

' EASTER DATE CALCULATION FOR YEARS 1583 TO 4099

' y is a 4 digit year 1583 to 4099
' d returns the day of the month of Easter
' m returns the month of Easter

' Easter Sunday is the Sunday following the Paschal Full Moon
' (PFM) date for the year

' This algorithm is an arithmetic interpretation of the 3 step
' Easter Dating Method developed by Ron Mallen 1985, as a vast
' improvement on the method described in the Common Prayer Book

' Because this algorithm is a direct translation of the
' official tables, it can be easily proved to be 100% correct

' This algorithm derives values by sequential inter-dependent
' calculations, so ... DO NOT MODIFY THE ORDER OF CALCULATIONS!

' The \ operator may be unfamiliar - it means integer division
' for example, 30 \ 7 = 4 (the remainder is ignored)

' All variables are integer data types

' It's free!  Please do not modify code or comments!
' ==========================================================

   Dim FirstDig, Remain19, temp    'intermediate results
   Dim tA, tB, tC, tD, tE          'table A to E results

   FirstDig = y \ 100              'first 2 digits of year
   Remain19 = y Mod 19             'remainder of year / 19

' calculate PFM date
   temp = (FirstDig - 15) \ 2 + 202 - 11 * Remain19
    
   Select Case FirstDig
      Case 21, 24, 25, 27 To 32, 34, 35, 38
         temp = temp - 1
      Case 33, 36, 37, 39, 40
         temp = temp - 2
   End Select
   temp = temp Mod 30

   tA = temp + 21
   If temp = 29 Then tA = tA - 1
   If (temp = 28 And Remain19 > 10) Then tA = tA - 1

'find the next Sunday
   tB = (tA - 19) Mod 7
    
   tC = (40 - FirstDig) Mod 4
   If tC = 3 Then tC = tC + 1
   If tC > 1 Then tC = tC + 1
        
   temp = y Mod 100
   tD = (temp + temp \ 4) Mod 7
    
   tE = ((20 - tB - tC - tD) Mod 7) + 1
   D = tA + tE

'return the date
   If D > 31 Then
      D = D - 31
      M = 4
   Else
      M = 3
   End If

   EasterDate = DateSerial(y, M, D)    'EASTER DATE CALCULATION FOR YEARS 1900 TO 4099 due to Excel limitations

End Function

Called with:
=EasterDate(1,1,A1)

A considerably shorter one its quoted on Cheap Pearson website:

Public Function EasterDate2(Yr As Integer) As Date

' Returns the correct result for the years between 1900 and 2099.

    Dim D As Integer
    D = (((255 - 11 * (Yr Mod 19)) - 21) Mod 30) + 21
    EasterDate2 = DateSerial(Yr, 3, 1) + D + (D > 48) + 6 - ((Yr + Yr \ 4 + D + (D > 48) + 1) Mod 7)
    
End Function

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.

Public Function EasterDate3(InputYear As Integer) As Date

' Returns the date for Easter Sunday, depends on Excel to function
' From a worksheet formula by Norbert Hetterich for the years between 1900 and 2203.
' This user defined function calculates the wrong date for the years:
' 1943, 1957, 1984, 2011, 2038, 2052, 2079, 2109, 2136, 2163 and 2190.

    EasterDate3 = DateSerial(InputYear, 5, Day(Minute(InputYear / 38) / 2 + 56))
    EasterDate3 = Application.WorksheetFunction.Floor(EasterDate3, 7) - 34

End Function

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:

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

Another version from Tomas Jansen:

=DOLLAR(("4/"&DAY(MINUTE(A1/38)/2+55)&"/"&A1)/7,)*7-6

I recommend using this version transformed into international date format:

=DOLLAR(DATE(A1,4,DAY(MINUTE(A1/38)/2+55))/7,)*7-6

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

=FLOOR("5/"&DAY(MINUTE(A1/38)/2+56)&"/"&A1,7)-34

I recommend using this version transformed into international date format:

=FLOOR(DATE(A1,5,DAY(MINUTE(A1/38)/2+56)),7)-34

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

=DATE(A1,IF(IF(MOD(IF((INT(A1/100)-20)<13,0,CHOOSE((INT(A1/100)-20)-12,-2,0,0,-2,-2,0,-2,-2))+IF((INT(A1/100)-20)<1,0,CHOOSE((INT(A1/100)-20),-1,0,0,-1,-1,0,-1,-1,-1,-1,-1,-1,0,-1,-1,0,0,-1,0,0))+INT(((INT(A1/100)-20)+5)/2)+202-11*MOD(A1,19),30)=29,-1,IF(AND(MOD(IF((INT(A1/100)-20)<13,0,CHOOSE((INT(A1/100)-20)-12,-2,0,0,-2,-2,0,-2,-2))+IF((INT(A1/100)-20)<1,0,CHOOSE((INT(A1/100)-20),-1,0,0,-1,-1,0,-1,-1,-1,-1,-1,-1,0,-1,-1,0,0,-1,0,0))+INT(((INT(A1/100)-20)+5)/2)+202-11*MOD(A1,19),30)=28,MOD(A1,19)>10),-1,0))+MOD(IF((INT(A1/100)-20)<13,0,CHOOSE((INT(A1/100)-20)-12,-2,0,0,-2,-2,0,-2,-2))+IF((INT(A1/100)-20)<1,0,CHOOSE((INT(A1/100)-20),-1,0,0,-1,-1,0,-1,-1,-1,-1,-1,-1,0,-1,-1,0,0,-1,0,0))+INT(((INT(A1/100)-20)+5)/2)+202-11*MOD(A1,19),30)+21+MOD((20-(MOD((IF(MOD(IF((INT(A1/100)-20)<13,0,CHOOSE((INT(A1/100)-20)-12,-2,0,0,-2,-2,0,-2,-2))+IF((INT(A1/100)-20)<1,0,CHOOSE((INT(A1/100)-20),-1,0,0,-1,-1,0,-1,-1,-1,-1,-1,-1,0,-1,-1,0,0,-1,0,0))+INT(((INT(A1/100)-20)+5)/2)+202-11*MOD(A1,19),30)=29,-1,IF(AND(MOD(IF((INT(A1/100)-20)<13,0,CHOOSE((INT(A1/100)-20)-12,-2,0,0,-2,-2,0,-2,-2))+IF((INT(A1/100)-20)<1,0,CHOOSE((INT(A1/100)-20),-1,0,0,-1,-1,0,-1,-1,-1,-1,-1,-1,0,-1,-1,0,0,-1,0,0))+INT(((INT(A1/100)-20)+5)/2)+202-11*MOD(A1,19),30)=28,MOD(A1,19)>10),-1,0))+MOD(IF((INT(A1/100)-20)<13,0,CHOOSE((INT(A1/100)-20)-12,-2,0,0,-2,-2,0,-2,-2))+IF((INT(A1/100)-20)<1,0,CHOOSE((INT(A1/100)-20),-1,0,0,-1,-1,0,-1,-1,-1,-1,-1,-1,0,-1,-1,0,0,-1,0,0))+INT(((INT(A1/100)-20)+5)/2)+202-11*MOD(A1,19),30)+21-19),7))-(IF(IF(MOD((40-(INT(A1/100)-20)),4)=3,1,0)+MOD((40-(INT(A1/100)-20)),4)>1,1,0)+IF(MOD((40-(INT(A1/100)-20)),4)=3,1,0)+MOD((40-(INT(A1/100)-20)),4))-(MOD((MOD(A1,100) + INT(MOD(A1,100) / 4)),7))),7)+1>31,4,3),IF(IF(MOD(IF((INT(A1/100)-20)<13,0,CHOOSE((INT(A1/100)-20)-12,-2,0,0,-2,-2,0,-2,-2))+IF((INT(A1/100)-20)<1,0,CHOOSE((INT(A1/100)-20),-1,0,0,-1,-1,0,-1,-1,-1,-1,-1,-1,0,-1,-1,0,0,-1,0,0))+INT(((INT(A1/100)-20)+5)/2)+202-11*MOD(A1,19),30)=29,-1,IF(AND(MOD(IF((INT(A1/100)-20)<13,0,CHOOSE((INT(A1/100)-20)-12,-2,0,0,-2,-2,0,-2,-2))+IF((INT(A1/100)-20)<1,0,CHOOSE((INT(A1/100)-20),-1,0,0,-1,-1,0,-1,-1,-1,-1,-1,-1,0,-1,-1,0,0,-1,0,0))+INT(((INT(A1/100)-20)+5)/2)+202-11*MOD(A1,19),30)=28,MOD(A1,19)>10),-1,0))+MOD(IF((INT(A1/100)-20)<13,0,CHOOSE((INT(A1/100)-20)-12,-2,0,0,-2,-2,0,-2,-2))+IF((INT(A1/100)-20)<1,0,CHOOSE((INT(A1/100)-20),-1,0,0,-1,-1,0,-1,-1,-1,-1,-1,-1,0,-1,-1,0,0,-1,0,0))+INT(((INT(A1/100)-20)+5)/2)+202-11*MOD(A1,19),30)+21+MOD((20-(MOD((IF(MOD(IF((INT(A1/100)-20)<13,0,CHOOSE((INT(A1/100)-20)-12,-2,0,0,-2,-2,0,-2,-2))+IF((INT(A1/100)-20)<1,0,CHOOSE((INT(A1/100)-20),-1,0,0,-1,-1,0,-1,-1,-1,-1,-1,-1,0,-1,-1,0,0,-1,0,0))+INT(((INT(A1/100)-20)+5)/2)+202-11*MOD(A1,19),30)=29,-1,IF(AND(MOD(IF((INT(A1/100)-20)<13,0,CHOOSE((INT(A1/100)-20)-12,-2,0,0,-2,-2,0,-2,-2))+IF((INT(A1/100)-20)<1,0,CHOOSE((INT(A1/100)-20),-1,0,0,-1,-1,0,-1,-1,-1,-1,-1,-1,0,-1,-1,0,0,-1,0,0))+INT(((INT(A1/100)-20)+5)/2)+202-11*MOD(A1,19),30)=28,MOD(A1,19)>10),-1,0))+MOD(IF((INT(A1/100)-20)<13,0,CHOOSE((INT(A1/100)-20)-12,-2,0,0,-2,-2,0,-2,-2))+IF((INT(A1/100)-20)<1,0,CHOOSE((INT(A1/100)-20),-1,0,0,-1,-1,0,-1,-1,-1,-1,-1,-1,0,-1,-1,0,0,-1,0,0))+INT(((INT(A1/100)-20)+5)/2)+202-11*MOD(A1,19),30)+21-19),7))-(IF(IF(MOD((40-(INT(A1/100)-20)),4)=3,1,0)+MOD((40-(INT(A1/100)-20)),4)>1,1,0)+IF(MOD((40-(INT(A1/100)-20)),4)=3,1,0)+MOD((40-(INT(A1/100)-20)),4))-(MOD((MOD(A1,100) + INT(MOD(A1,100) / 4)),7))),7)+1>31,-31,0)+IF(MOD(IF((INT(A1/100)-20)<13,0,CHOOSE((INT(A1/100)-20)-12,-2,0,0,-2,-2,0,-2,-2))+IF((INT(A1/100)-20)<1,0,CHOOSE((INT(A1/100)-20),-1,0,0,-1,-1,0,-1,-1,-1,-1,-1,-1,0,-1,-1,0,0,-1,0,0))+INT(((INT(A1/100)-20)+5)/2)+202-11*MOD(A1,19),30)=29,-1,IF(AND(MOD(IF((INT(A1/100)-20)<13,0,CHOOSE((INT(A1/100)-20)-12,-2,0,0,-2,-2,0,-2,-2))+IF((INT(A1/100)-20)<1,0,CHOOSE((INT(A1/100)-20),-1,0,0,-1,-1,0,-1,-1,-1,-1,-1,-1,0,-1,-1,0,0,-1,0,0))+INT(((INT(A1/100)-20)+5)/2)+202-11*MOD(A1,19),30)=28,MOD(A1,19)>10),-1,0))+MOD(IF((INT(A1/100)-20)<13,0,CHOOSE((INT(A1/100)-20)-12,-2,0,0,-2,-2,0,-2,-2))+IF((INT(A1/100)-20)<1,0,CHOOSE((INT(A1/100)-20),-1,0,0,-1,-1,0,-1,-1,-1,-1,-1,-1,0,-1,-1,0,0,-1,0,0))+INT(((INT(A1/100)-20)+5)/2)+202-11*MOD(A1,19),30)+21+MOD((20-(MOD((IF(MOD(IF((INT(A1/100)-20)<13,0,CHOOSE((INT(A1/100)-20)-12,-2,0,0,-2,-2,0,-2,-2))+IF((INT(A1/100)-20)<1,0,CHOOSE((INT(A1/100)-20),-1,0,0,-1,-1,0,-1,-1,-1,-1,-1,-1,0,-1,-1,0,0,-1,0,0))+INT(((INT(A1/100)-20)+5)/2)+202-11*MOD(A1,19),30)=29,-1,IF(AND(MOD(IF((INT(A1/100)-20)<13,0,CHOOSE((INT(A1/100)-20)-12,-2,0,0,-2,-2,0,-2,-2))+IF((INT(A1/100)-20)<1,0,CHOOSE((INT(A1/100)-20),-1,0,0,-1,-1,0,-1,-1,-1,-1,-1,-1,0,-1,-1,0,0,-1,0,0))+INT(((INT(A1/100)-20)+5)/2)+202-11*MOD(A1,19),30)=28,MOD(A1,19)>10),-1,0))+MOD(IF((INT(A1/100)-20)<13,0,CHOOSE((INT(A1/100)-20)-12,-2,0,0,-2,-2,0,-2,-2))+IF((INT(A1/100)-20)<1,0,CHOOSE((INT(A1/100)-20),-1,0,0,-1,-1,0,-1,-1,-1,-1,-1,-1,0,-1,-1,0,0,-1,0,0))+INT(((INT(A1/100)-20)+5)/2)+202-11*MOD(A1,19),30)+21-19),7))-(IF(IF(MOD((40-(INT(A1/100)-20)),4)=3,1,0)+MOD((40-(INT(A1/100)-20)),4)>1,1,0)+IF(MOD((40-(INT(A1/100)-20)),4)=3,1,0)+MOD((40-(INT(A1/100)-20)),4))-(MOD((MOD(A1,100) + INT(MOD(A1,100) / 4)),7))),7)+1)

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í.