First Published 7 July 2017               Last Updated 31 Oct 2024

The function below calculates Easter Sunday for any year between 1900 and 2099.
Apparently, that is the first Sunday after the first ecclesiastical full moon that occurs on or after March 21.

And there was me thinking they just made it up every year ....

CODE:

Public Function GetEasterSunday(Yr As Integer) As Date

'Code from http://www.cpearson.com/excel/Easter.aspx
'Accurate for all years from 1900 to 2099

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

End Function

That's all there is to it!

The code is adapted from a VBA function written by Excel guru, Chip Pearson .

Its 'only' guaranteed to be correct between 1900 and 2099. Outside that range, the dates returned are not Sundays!
This is because the function doesn't allow for the fact that years such as 1900 and 2100 which are divisible by 100 but not 400 are NOT leap years.

However, if you need to know Easter dates outside that range, Chip's site also has another function taken from the United States Naval Observatory (USNO).
I have tested the function below for all years between 1583 and 4100 and it appears to be correct in all cases.

CODE:

Public Function EasterUSNO(YYYY As Long) As Long

'Code from http://www.cpearson.com/excel/Easter.aspx

Dim C As Long
Dim N As Long
Dim K As Long
Dim I As Long
Dim J As Long
Dim L As Long
Dim M As Long
Dim D As Long

    C = YYYY \ 100
    N = YYYY - 19 * (YYYY \ 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 = YYYY + YYYY \ 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)

    EasterUSNO = DateSerial(YYYY, M, D)

End Function

NOTE: The calendar system we use today, the Gregorian calendar, was first introduced in 1582.
To make up for the inaccuracies of its predecessor, the Julian calendar, 11 days were 'lost'. See Gregorian Calendar Reform: Why Are Some Dates Missing?

As a result, the above function is incorrect for all years before 1583.

If you've never checked Chip's site, I strongly recommend you do so.
   http://www.cpearson.com/Excel/MainPage.aspx

Sadly Chip Pearson died in 2018 but his website is still being maintained.
It also has code to calculate other holiday dates (mainly USA such as Thanksgiving).

Also many other 'goodies' such as Programming The VBA Editor using the VBA reference library: Microsoft Visual Basic For Applications Extensibility 5.3.



UPDATE 31 Oct 2024

Fellow MVP, Gustav Brock, also has his own VBA function to calculate Easter Sunday based on calculations by the Astronomical Society of South Australia.
Gustav's version is available as part of the module DateCalc.bas at his very comprehensive VBA.Date GitHub code repository.

The results from Gustav's code exactly match those from the EasterSundayUSNO function for all years between 1583 and 4099.



Feedback

      Please use the contact form below to let me know whether you found this article interesting/useful or if you have any questions/comments.

      Please also consider making a donation towards the costs of maintaining this website. Thank you



Colin Riddington             Mendip Data Systems            Last Updated 31 Oct 2024

Return to Code Samples Page

Return to Top