The Calculating Easter post on John Walkenbach’s new Spreadsheet Page blog got me thinking. He presented a formula for calculating the date of Easter in a given year:

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

Chip Pearson’s web site also has a couple formulas for calculating Easter:

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

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

Chip also offers a UDF (VBA User Defined Function) which is valid through 2099, but breaks thereafter because it oversimplifies calculation of leap years.

Public Function EasterDate(Yr As Integer) As Date
  'Chip Pearson from unknown
  'see http://cpearson.com/excel/holidays.htm#Easter
  Dim d As Integer
  d = (((255 - 11 * (Yr Mod 19)) - 21) Mod 30) + 21
  EasterDate = DateSerial(Yr, 3, 1) + d + (d > 48) + 6 - ((Yr + Yr \ 4 + _
      d + (d > 48) + 1) Mod 7)
End Function
 

In addition, I saved a couple UDFs from the newsgroup a few years back. These were written by Norman Harker based on an extensive discussion of the topic on Claus Tondering’s web site.

Function EASTER(year As Single) As Date
    '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
    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 = DateSerial(year, EM, ED) - Adj1904
End Function

Function ORTHODOXEASTER(year As Single) As Date
    'Based on Claus Tondering algorithm interpretation.
    'See http://www.tondering.dk/claus/cal/calendar29.html
    'Norman Harker 11-Jul-2004
    Dim G 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: Dim JulAdj As Integer
    G = year Mod 19
    I = (19 * G + 15) Mod 30
    J = (year + year \ 4 + I) 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
    JulAdj = 10 + (year \ 100) - 16 - (year \ 400) + 4
    ORTHODOXEASTER = DateSerial(year, EM, ED) - Adj1904 + JulAdj
End Function
 

The worksheet formulas are interesting, because they are compact and don’t require VBA. Chip’s two worksheet formulas agree with each other, which is not surprising, because they look somewhat similar, but they disagree with John’s worksheet formula and with Chip and Norman’s UDFs, which agree. Neither John nor Chip admit ownership of these respective calculations, so the error comes from the originators of one or the other relationship. Since John’s formula agrees with the Norman’s UDF, which is based on a very rigorous explanation by Claus, I would guess that Chip’s source is mistaken on the worksheet formulas. The only difference in the calculated dates for Easter between 1900 and 2199 is that Chip’s calculation is a week earlier in 2079. The same error occurs in 2744. I doubt that anyone reading this page will be aware of the problem in 2079 (I’ll be 119 years old!) or even alive in 2744.

In Gradients, Fills, and Shadows, Oh My, I commented on the bar chart John used to show the occurrence of Easter on a given date over the span of 1900 to 2199, and on the discrepancy between John’s and Chip’s formulas. What I’m more interested in is the difference between the standard (“Roman”) and Orthodox (“Greek”) determination of Easter. Both are based on misinterpretations of the Jewish lunar calendar, and the Greek calculation has the additional discrepancy between the Julian and Gregorian date systems. I won’t go into these issues, because Claus Tondering covers them in great detail. (Another detailed source is The Determination of Easter, on both the Julian and the Gregorian Calendars.) But I had a few interesting observations which may incidentally illustrate some useful charting techniques.

I started with the line chart I made previously to compare John’s and Chip’s Easter calculations. You can see that the two curves are offset, but the overlapping of the curves makes it hard to follow them.

I made a column chart of the data, then made it into a panel chart as follows. I moved the Greek series to the secondary axis, and made sure I had both primary and secondary category axes. I set the primary value axis for a minimum of 0, a maximum of 28, and a spacing of 2. I set the secondary value axis for a minimum of -14, a maximum of 14, and a spacing of 2. I set both category axes to cross their respective value axes at a value of zero.. I used custom number formats for the axis tick labels: primary [>14]" ";0, secondary "0;;0".

In Choice of Category Axis Order I showed this same data in simple line charts.

This display arrangement (as time series) makes it easier to see that the range of Roman dates begins almost two weeks earlier than the range of Greek dates, and ends two weeks later. The following chart shows the distributions from the column chart above, offset so their centers coincide.

The shapes of the distributions are similar, but not exact, and from experience I know that the difference is not always the same. Below I’ve plotted a histogram of the misalignment between Roman and Greek Easter. Almost half the time, Greek Easter is the week after Roman Easter. Another quarter of the time (a bit more actually), the two holidays coincide. Nearly a quarter of the time, Greek Easter is a month later, occasionally four weeks but mostly five weeks later than Roman Easter. There is never a difference of two or three weeks: both calculations put Easter right after a full moon, so either both calculations use the same full moon (0 or 1 week difference), or the Greek calculation uses the next full moon (4 or 5 weeks difference). The one-week differential is an offset in the Greek calculation to ensure that Easter follows Passover.

I suppose I could have shown this distribution with a (gasp!) pie chart, although the pie hides the absence of 2 or 3 week offsets, while the histogram above retains the empty categories.

Related Posts:

• Choice of Category Axis Order
• Gradients, Fills, and Shadows, Oh My
• UDF to Calculate an Arbitrary Formula
• Programming Excel 2007 and Excel 2010 AutoShapes with VBA (Guest Post)
• Count Bold Cells in a Range
• Seasonal Adjustment – Preliminary Approaches
• File Name Functions
• Physics Lesson
• Connect Two XY Series with Arrows (VBA)
• Time Trials of Approaches to Measure Minimum and Maximum Chart Values