The date of Easter

Several years ago I looked for a formula which calculated the date of Easter. I needed it for creating a working calendar and Easter Monday is a holiday in my country. In fact it was no problem to find this formula. Just ask Google "excel easter formula". It has been working reliably and calculating the date for requested year since then. Due to my not fully understanding of this formula I was a bit dubious about the results. So for peace of my mind I checked them occasionaly. I have decided to dispel my doubts.

Gauss algorithm

I found Gauss algorithm on Wikipedia:

Select all

  [A2]: year
a [B2]: =MOD(A2,19)
b [C2]: =MOD(A2,4)
c [D2]: =MOD(A2;7)
k [E2]: =TRUNC(A2/100)
p [F2]: =TRUNC((13+8*E2)/25)
q [G2]: =TRUNC(E2/4)
M [H2]: =MOD(15-F2+E2-G2,30)
N [I2]: =MOD(4+E2-G2;7)
d [J2]: =MOD(19*B2+H2,30)
e [K2]: =MOD(2*C2+4*D2+6*J2+I2,7)

Ifs:
If d = 29 and e = 6, replace 26 April with 19 April

Select all

  [L2]: =AND(J2=29,K2=6)*-7

If d = 28, e = 6, and (11M + 11) mod 30 < 19, replace 25 April with 18 April

Select all

  [M2]: =AND(J2=28,K2=6,MOD(11*H2+11,30)<19)*-7

Formula for the date of Easter:

Select all

  [L2]: =DATE(A2,3,22)+J2+K2+L2+M2

Comparison

I compared results of the formula based on Gauss algorithm with results of the formula which I found on the Internet. Both formulas return the same results for years 1900-2203 but some results for next years are different. That's enough. I hope I will not be at work and need any working calendar in 2204.
Even if I still don't understand the logic of this formula, I know it returns the right results. I don't know who is the author but I want to thanks him for this great formula.

My crazy long formula

I tried to create a formula for the date of Easter with reference only to the cell A2 (year). Because of Ifs this formula is very very long (979 chars in the English localization) so it is absolutely unusable. But it works for Excel 2007 or newer ;):

Select all

  [Q2]: =DATE(A2,3,22)+MOD(19*MOD(A2,19)+MOD(15-TRUNC((13+8*TRUNC(A2/100))/25)+TRUNC(A2/100)-TRUNC(TRUNC(A2/100)/4),30),30)+MOD(2*MOD(A2,4)+4*MOD(A2,7)+6*MOD(19*MOD(A2,19)+MOD(15-TRUNC((13+8*TRUNC(A2/100))/25)+TRUNC(A2/100)-TRUNC(TRUNC(A2/100)/4),30),30)+MOD(4+TRUNC(A2/100)-TRUNC(TRUNC(A2/100)/4),7),7)+AND(MOD(19*MOD(A2,19)+MOD(15-TRUNC((13+8*TRUNC(A2/100))/25)+TRUNC(A2/100)-TRUNC(TRUNC(A2/100)/4),30),30)=29,MOD(2*MOD(A2,4)+4*MOD(A2,7)+6*MOD(19*MOD(A2,19)+MOD(15-TRUNC((13+8*TRUNC(A2/100))/25)+TRUNC(A2/100)-TRUNC(TRUNC(A2/100)/4),30),30)+MOD(4+TRUNC(A2/100)-TRUNC(TRUNC(A2/100)/4),7),7)=6)*-7+AND(MOD(19*MOD(A2,19)+MOD(15-TRUNC((13+8*TRUNC(A2/100))/25)+TRUNC(A2/100)-TRUNC(TRUNC(A2/100)/4),30),30)=28,MOD(2*MOD(A2,4)+4*MOD(A2,7)+6*MOD(19*MOD(A2,19)+MOD(15-TRUNC((13+8*TRUNC(A2/100))/25)+TRUNC(A2/100)-TRUNC(TRUNC(A2/100)/4),30),30)+MOD(4+TRUNC(A2/100)-TRUNC(TRUNC(A2/100)/4),7),7)=6,MOD(11*MOD(15-TRUNC((13+8*TRUNC(A2/100))/25)+TRUNC(A2/100)-TRUNC(TRUNC(A2/100)/4),30)+11,30)<19)*-7

It is possible to change this formula for years before 1900. This change is not complicated, but the formula is. A better way is writing a user defined function, for example like this:

Select all

Function EASTER_DATE(Year)
  On Error GoTo errorHandler
  
  Dim a As Long, b As Long, c As Long, k As Long, p As Long, q As Long, M As Long, N As Long, d As Long, e As Long
  a = Year Mod 19
  b = Year Mod 4
  c = Year Mod 7
  k = Year \ 100
  p = (13 + 8 * k) \ 25
  q = k \ 4
  M = (15 - p + k - q) Mod 30
  N = (4 + k - q) Mod 7
  d = (19 * a + M) Mod 30
  e = (2 * b + 4 * c + 6 * d + N) Mod 7
  
  Dim if1 As Long, if2 As Long
  If d = 29 And e = 6 Then if1 = -7
  If d = 28 And e = 6 And (11 * M + 11) Mod 30 < 19 Then if2 = -7
  
errorHandler:
  If Err.Number = 0 Then
    EASTER_DATE = Format(DateSerial(Year, 3, 22) + d + e + if1 + if2, "yyyy-mm-dd ddd")
  Else
    EASTER_DATE = CVErr(xlErrValue)
  End If
End Function

Select all

  [S2]: =EASTER_DATE(A2)

You can download the workbook here: Easter.xlsm 

Tweet