Excel Easter Date Calculations

How to use Excel formulas and user defined function to calculate the date of Easter. Is it in March or April this year? Which day of the month does Easter fall on?

The Great Easter Day Hunt

Thanks to Excel MVP, Jerry Latham, who contributed this tutorial for calculating Easter dates.

Each year, Easter falls on the first Sunday after the Paschal full moon. That is the first full moon on or after March 21st -- that is the approximate date of the northern spring equinox (vernal equinox).

• Note: The Paschal full moon is the ecclesiastical full moon for the the northern hemisphere. Read more on the Wikipedia Paschal full moon page.
• Read more about Easter date calculations on the Wikipedia page, Date of Easter.

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.

List of Easter Dates

Here is a list of upcoming Easter dates, calculated in Excel.

The dates were calculated using the User Defined Function, CalculateEaster , shown in the United States Naval Observatory Algorithm section, further down on this page.

• Note: For actual dates of Easter 20 years in the past, for this year and in the future, see List of dates for Easter, on the Wikipedia website.

Excel Date Forum Challenge

A portion of the search leads us to Herber's Excel Forum, which 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.

Winning Formula

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)

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

MDY: If your regional settings have month/day/year format, use this version of the formula instead:

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

Related Dates

• Good Friday: To calculate the date of Good Friday, subtract 2 from the Easter Date.
• Easter Monday: To calculate the date of Easter Monday, add 1 to the Easter Date.

Other Formulas

Other formulas presented during that same period that provided the desired results were noted by George Simms, a participant in the contest:

NOTE: In the following formulas, J is a named cell containing the year.

1st Norbert Hetterich

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

2nd Thomas Jansen

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

3rd Roger Friederich

• =FLOOR(DATE(J,3,MOD(18.37*MOD(J,19)-6,29)),7)+29

Variations

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:

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

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.

Formula Limitations

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.

• =ROUND(DATE(A2,4,1)/7+MOD(19*MOD(A2,19)-7,30)*14%,0)*7-6

Appears accurate from 1900 through 2203. After 2203 it disagrees with other results almost routinely.

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

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.

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

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.

So in the final analysis, the worksheet formulas cannot be used reliably to determine dates of Easter after 2203, with two failures occurring as early as 2079

Longer Algorithms

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

Performance Test Results

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 https://support.microsoft.com/kb/172338 .

Laurent Longre Algorithm

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

Laurent Longre Algorithm UDF Code

The Laurent Longre algorithm as an Excel UDF - you can copy this VBA code to your workbook, to use for an Easter date calculation:

'=========================================
Function EASTER_by_LLongre(Yr As Integer) As Variant
'attributed to Laurent Longre
'published at
'https://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

Longre Code Notes:

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

United States Naval Observatory Algorithm

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

Speed: The average time to execute was 7.8114 microseconds (7.8114E-06 seconds).

'=========================================
Public Function CalculateEaster(anyYear As Variant) As Variant
'Algorithm Source:
' https://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 As Integer: Dim d 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 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

'=========================================

Astronomical Society of South Australia Algorithm

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

'=========================================
Public Function EasterDate(y) As Variant
'published with permission from ASSA https://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
'=========================================

Norman Harker adaptation of Claus Tondering Algorithm

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 https://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

'=========================================

Excel Easter Calculation Workbook

• To see the code, and test the formulas, you can download the Excel Easter Calculation sample workbook. The file is in Excel's xlsm format, and is zipped. There are macros in the workbook, so enable the macros if you want to test the code.

Excel Function Tutorials

Excel VLOOKUP Function

Excel INDEX / MATCH Functions

Excel Count Functions

Excel INDIRECT Function

Leap Year Calculations