How to use Excel formulas and user defined function to calculate the date of Easter
Thanks to Excel MVP, Jerry Latham, who contributed this tutorial for calculating Easter dates.
As Chip Pearson noted on his website, finding the day of Easter Sunday is a trivial effort but finding the date of a specific Easter Sunday is not such a trivial effort.
A portion of the search leads us to Herber's Excel Forum, which apparently sponsored a contest as referenced in the Excel newsgroups. The rules for that contest were to come up with the shortest Excel worksheet formula that would return the date of Easter Sunday for years between 1900-2078.
The winner was apparently Norbert Hetterich of Germany with this formula (assumes the year is in cell A1, and day/month/year date format in regional settings)
Other formulas presented during that same period that provided the desired results were noted by George Simms, a participant in the contest:
1st Norbert Hetterich
2nd Thomas Jansen
3rd Roger Friederich
The most interesting. Prasad DV
Note: in the above formulas J is a named cell containing the year to evaluate.
The Norbert Hetterich formula is presented on Chip Pearson's site with a minor modification to format the result in typical U.S. date format as:
Another interesting formula is attributed to Gerhardt Somitsch (presented where cell A2 contains a year value):
=DATE(A2,3,29.56+0.979*MOD(204-11*MOD(A2,19),30) - WEEKDAY(DATE(A2,3,28.56+0.979*MOD(204-11*MOD(A2,19),30))))
The good thing about these various formulas is that they do work for the near future (at least through 2078), and that's all most of us need at this point in time. However, for dates beyond 2078 and earlier than 1900, we must look elsewhere.
Before moving on to the other solutions, let us look at the apparent limitations of the various worksheet function solutions. It may very well be that these worksheet functions meet your needs and you don't need to look further.
These limitations have either been stated at some point in their development, as with the contest limits mentioned earlier, or by inspection when compared to other methods of determining the date of Easter.
Appears accurate from 1900 through 2203. After 2203 it disagrees with other results almost routinely.
This is the Thomas Jansen formula and is reputed to be accurate from 1900-2078. No argument there. In point of fact it appears to be accurate through 2203 as with the previous formula.
=DATE(A2,3,29.56+0.979*MOD(204-11*MOD(A2,19),30) - WEEKDAY(DATE(A2,3,28.56+0.979*MOD(204-11*MOD(A2,19),30))))
This is the Gerhardt Somitsch formula and is accurate from 1900-2203. But after 2203 its results are erratic also.
This is an adaptation of the winning contest formula, =FLOOR(DAY(MINUTE(J/38)/2+56)&"/5/"&J,7)-34
that is presented on the Chip Pearson website. Reputed to be accurate from 1900-2078, and so they are. However, they fail immediately in 2079, then return accurate results through 2203, and then begin to become erratic as with the previous two formulas.
The most authoritative sources for algorithms suitable to be used as an Excel User Defined Function (UDF) would appear to be the work done by J.M. Oudin who tackled the inaccuracies in the formulas original derived by C F Gauß. It is Oudin's work that is now used extensively to determine the dates of Easter, even by the U.S. Naval Observatory.
This algorithm has been turned into an Excel UDF more than once - and was the one I elected to implement as an Excel UDF. The restriction on the USNO algorithm is that it is intended to be used for years after 1899.
The Astronomical Society of South Australia (ASSU), has quite an extensive section dealing with Easter and how to determine its date. They provide a method involving the use of tables to accurately determine its date for any year from 1583 through 4099.
I have adapted their BASIC formula for use as an Excel UDF also. The code is provided later, after the code for the UDF based on the USNO published algorithm. I've also contacted the ASSU and provided them with the code for the UDF to use at their discretion.
Additional algorithms have been attributed to Claus Tondering (page 26 and page 27) and Laurent Longre, who also publishes the MoreFunc.xll add-in for Excel. I cannot locate the original L. Longre code, through his current site.. However, as you'll see shortly, the version presented by Daniel.M in the groups.google.com link above is a significant piece of code.
I ran tests to obtain an average time to return a single result for all the algorithms presented. The test results should be considered as relative to one another, not absolutes. The actual time on a particular machine will vary based on CPU and system load. The tests were run on a system with an Intel i7 CPU running at 3.43GHz, with 12 Gb RAM using Excel 2003 on the 64-bit version of Windows 7.
The number of samples for the results for each was equal to the number of valid dates the algorithm could be used for. In other words, thousands for each one.
The timing was done using the function available from http://support.microsoft.com/kb/172338 .
Accuracy: This algorithm is definitely worthy of mention. It is the shortest of all to implement, and it is the fastest running of the group by roughly a factor of 2. It is in 100% agreement with both the U.S. Naval Observatory (USNO) and Astronomical Society of Southern Australia (ASSA) calculations from 1900 through 6553. After 6553, the algorithm returns the #INVALID! error.
Speed: The average time to execute was 4.85675 microseconds (4.85675E-06 seconds).
Note: For years prior to 1900, or for overflow errors, see suggestions below the code
The Laurent Longre algorithm as an Excel UDF:
'========================================= Function EASTER_by_LLongre(Yr As Integer) As Variant 'attributed to Laurent Longre 'published at 'http://groups.google.com/group/microsoft.public.excel _ .worksheet.functions/msg/bae33ace3ce95b0c ' 'changed function type from As Long to As Variant to permit returning 'error message for invalid Excel years - JLatham 9/11/2011 ' Dim Century As Integer Dim Sunday As Integer Dim Epact As Integer Dim Golden As Integer Dim LeapDayCorrection As Integer Dim SynchWithMoon As Integer Dim N As Integer 'added by JLatham 9/11/2011 If Not IsDate("1/1/" & Yr) Or Yr > 6553 Then EASTER_by_LLongre = "Year Limit Error" Exit Function End If Golden = (Yr Mod 19) + 1 Century = Yr \ 100 + 1 LeapDayCorrection = 3 * Century \ 4 - 12 SynchWithMoon = (8 * Century + 5) \ 25 - 5 Sunday = 5 * Yr \ 4 - LeapDayCorrection - 10 Epact = (11 * Golden + 20 + SynchWithMoon - LeapDayCorrection) Mod 30 If Epact < 0 Then Epact = Epact + 30 If (Epact = 25 And Golden > 11) Or Epact = 24 Then Epact = Epact + 1 N = 44 - Epact If N < 21 Then N = N + 30 N = N + 7 - ((Sunday + N) Mod 7) EASTER_by_LLongre = DateSerial(Yr, 3, N) End Function
Thanks to Oscar Gunther for the following three suggestions:
1. If you encounter an overflow error, substitute "CLng(Yr)" for "Yr" in the line of code starting with "Sunday = 5...."
2. To handle years from 1583 to 9999, you can remove the JLatham date check, and replace the last line with the following code:
If Yr < 1900 Then EASTER_by_LLongre = CStr(DateSerial(Yr, 3, N)) Else EASTER_by_LLongre = DateSerial(Yr, 3, N) End If
3. To get dates for Easter in the Julian calendar (326 to 1582), use the ASSA table methodology (not their code, which is Gregorian only) and for dates after 1582 use the USNO or modified Longre code
Accuracy: I have taken the view that this is an accurate algorithm for all years after 1899 through the Excel year limit of 9999. It is in agreement with the previously present Laurent Longre algorithm through the year 6553 and with the alternative method of determining the date of Easter presented by the ASSU (which follows this code).
'========================================= Public Function CalculateEaster(anyYear As Variant) As Variant 'Algorithm Source: ' http://aa.usno.navy.mil/faq/docs/easter.php 'requires integer math (use \ for division, not /) ' 'Valid for dates considered valid by Excel after 1899. ' 'This adaptation for VBA use by 'Jerry Latham 'This routine may be reused so long as: ' acknowledgment of the source is made in the code segment, and ' no 'value added' charge is applied based on the inclusion of ' this process. ' Dim C, d, N, k, I, J, L, m, y As Integer y = Val(anyYear) If Not IsDate("1/1/" & y) Or y < 1900 Then CalculateEaster = "Year Limit Error" Exit Function End If C = y \ 100 N = y - 19 * (y \ 19) k = (C - 17) \ 25 I = C - C \ 4 - (C - k) \ 3 + 19 * N + 15 I = I - 30 * (I \ 30) I = I - (I \ 28) * (1 - (I \ 28) * (29 \ (I + 1)) * ((21 - N) \ 11)) J = y + y \ 4 + I + 2 - C + C \ 4 J = J - 7 * (J \ 7) L = I - J m = 3 + (L + 40) \ 44 d = L + 28 - 31 * (m \ 4) CalculateEaster = DateSerial(y, m, d) End Function '=========================================
Accuracy: The algorithm is presented to be accurate for all years from 1583 through 4099 (for Western churches only). For years 1900 through 4099 it provides the same results as the USNO algorithm and since it uses a different methodology, the two act as good validations for the other.
Speed: The average time to execute was 6.8833 microseconds (6.8833E-06 seconds). Original BASIC code, explanation, and supporting documentation on the Astronomical Society of South Australia website: Finding Easter Sunday Dates With a Calculator
'========================================= Function EasterDate(y) As Variant 'published with permission from ASSA http://www.assa.org.au ' 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! ' - [My apologies - JLatham] ' ========================================================== Dim FirstDig, Remain19, temp 'intermediate results Dim tA, tB, tC, tD, tE 'table A to E results 'code modifications by JLatham *********** Dim d As Integer ' added by JLatham * Dim m As Integer ' added by JLatham * Dim Adj1904 As Integer '* ' * 'If y < 1583 Or y > 4099 Then '* ' EasterDate = "Invalid Year Entered" '* ' Exit Function '* 'End If '* '***************************************** 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 'modified by JLatham on a concept presented by Norman Harker 'to permit automatic date adjustment for systems using 'the 1904 date system vs the 1900 date system. If ActiveWorkbook.Date1904 = True Then Adj1904 = 365 * 4 + 2 ' 1,462 days End If EasterDate = DateSerial(y, m, d) - Adj1904 End Function '=========================================
Accuracy: This implementation of Claus Tondering's work provides results for the years 1900 through 9999 that are validated by the results of the USNO algorithm for all years. The implementation even includes an adjustment for systems that use the 1904 date system instead of the 1900 date system, a nice touch. While it is the slowest of the algorithms, its accuracy and flexibility make it a winner in my book.
Speed: The average time to execute was 9.47763 microseconds (9.47763E-06 seconds). It is currently published on the Peltier Tech Blog, and was apparently originally published in the Excel Functions newsgroup.
'========================================= Function EASTER_by_NHarker(year As Single) As Variant 'Based on Claus Tondering algorithm interpretation. 'See http://www.tondering.dk/claus/cal/calendar29.html 'Norman Harker 10-Jul-2004 ' Dim G As Integer: Dim C As Integer: Dim H As Integer Dim I As Integer: Dim J As Integer: Dim L As Integer Dim EM As Integer: Dim ED As Integer Dim Adj1904 As Integer If Not IsDate("1/1/" & year) Then EASTER_by_NHarker = "Excel Year Limit Error" Exit Function End If G = year Mod 19 C = year \ 100 H = (C - C \ 4 - (8 * C + 13) \ 25 + 19 * G + 15) Mod 30 I = H - (H \ 28) * (1 - (29 \ (H + 1)) * ((21 - G) \ 11)) J = (year + year \ 4 + I + 2 - C + C \ 4) Mod 7 L = I - J EM = 3 + (L + 40) \ 44 ED = L + 28 - (31 * (EM \ 4)) If ActiveWorkbook.Date1904 = True Then Adj1904 = 365 * 4 + 2 End If EASTER_by_NHarker = DateSerial(year, EM, ED) - Adj1904 End Function '=========================================
To see the code, and test the formulas, you can download the Excel Easter Calculation sample workbook. The file is in Excel 2003 format, and is zipped. There are macros in the workbook, so enable the macros if you want to test the code.
Last updated: October 17, 2016 7:16 PM
Contextures RSS Feed