Easter Sunday

If you want to calculate the correct date for Easter Sunday between the years 1900 and 2078 without depending on any Excel built-in worksheet functions you can use the function below:

Function EasterSunday(InputYear As Integer) As Long
' Returns the date for Easter Sunday, does not depend on Excel
Dim d As Integer
    d = (((255 - 11 * (InputYear Mod 19)) - 21) Mod 30) + 21
    EasterSunday = DateSerial(InputYear, 3, 1) + d + (d > 48) + 6 - _
        ((InputYear + InputYear \ 4 + d + (d > 48) + 1) Mod 7)
End Function

When you have this date you are also able to calculate other variable dates regarding Easter etc.

Calculate the date for Easter Sunday with a worksheet formula

Hans W. Herber held a competition on his website http://www.herber.de to find the shortest worksheetfunction that could calculate the date for Easter Sunday between the years 1900 and 2078. The competition ended March 31st. 1999. The example formulas below assumes that cell A1 contains the year the formula uses to return the date for Easter Sunday.

The winning formula (in English):
=FLOOR(DAY(MINUTE(A1/38)/2+56)&"/5/"&A1,7)-34
(submitted by Norbert Hetterich from Germany).

You could translate this worksheet function into a user defined function like this:

Function FDOE(InputYear As Integer) As Long
' Returns the date for Easter Sunday, depends on Excel to function
' From a worksheet formula by Norbert Hetterich
    FDOE = DateSerial(InputYear, 5, Day(Minute(InputYear / 38) / 2 + 56))    
    FDOE = Application.WorksheetFunction.Floor(FDOE, 7) - 34
End Function

Note! The userdefined function above calculates the wrong date for the years 1943, 1957, 1984, 2011, 2038 and 2052.

If you translate the winning formula to Norwegian, this is the result:
=AVRUND.GJELDENDE.MULTIPLUM.NED(DAG(MINUTT(A1/38)/2+56)&"/5/"&A1;7)-34

The first runner up is this formula (in English):
=DOLLAR((DAY(MINUTE(A1/38)/2+55)&".4."&A1)/7,)*7-6
(submitted by Thomas Jansen also from Germany).

The first runner up formula translates better to Norwegian:
=VALUTA((DAG(MINUTT(A1/38)/2+55)&".4."&A1)/7;)*7-6

Calculate holidays

The function below will return True if the input date is a defined holiday. The function can include or exclude Saturdays and Sundays. You'll have to customize the function to include the specific holidays for your country. This function uses the function EasterSunday found above in this document.

Function IsHoliday(lngDate As Long, InclSaturdays As Boolean, _
    InclSundays As Boolean) As Boolean
' returns True if lngDate is a Norwegian holiday 
' (optionally included Saturdays/Sundays)
' benytter funksjonen EasterSunday
Dim InputYear As Integer, ES As Long, OK As Boolean
    If lngDate <= 0 Then lngDate = Date
    InputYear = Year(lngDate)
    ES = EasterSunday(InputYear)
    OK = True
    Select Case lngDate
        Case DateSerial(InputYear, 1, 1) ' 1. Nyttårsdag
        'Case ES - 4 ' Onsdag før påske
        Case ES - 3 ' Skjærtorsdag
        Case ES - 2 ' Langfredag
        Case ES ' 1. Påskedag
        Case ES + 1 ' 2. Påskedag
        Case DateSerial(InputYear, 5, 1) ' 1. mai
        Case DateSerial(InputYear, 5, 17) ' 17. mai
        Case ES + 39 ' Kristi Himmelfartsdag
        'Case ES + 48 ' Pinseaften
        Case ES + 49 ' 1. Pinsedag
        Case ES + 50 ' 2. Pinsedag
        'Case DateSerial(InputYear, 12, 24) ' Julaften
        Case DateSerial(InputYear, 12, 25) ' 1. Juledag
        Case DateSerial(InputYear, 12, 26) ' 2. Juledag
        'Case DateSerial(InputYear, 12, 31) ' Nyttårsaften
        Case Else
            OK = False
            If InclSaturdays Then
                If WeekDay(lngDate, vbMonday) = 6 Then
                    OK = True
                End If
            End If
            If InclSundays Then
                If WeekDay(lngDate, vbMonday) = 7 Then
                    OK = True
                End If
            End If
    End Select
    IsHoliday = OK
End Function

Document last updated 2001-03-14 12:44:38 Printerfriendly version

User comments:

Philip Clarke from UK wrote (2004-05-26 23:12:01 CET):

Re: Easter
Ole,

Thanks for your response.

So, it appears that the DAY worksheet function can return a different value from the DAYSERIAL function in VBA (via the userdefined function), even when the input year is the same.

Strange, don't you think?

Ole P. from Norway wrote (2004-05-20 12:04:02 CET):

Re: Easter
The DAY-part calculation of the formula by Hetterich and the FDOE userdefined function will sometimes return a value that is different (by 1).
The FLOOR-part calculation will most of the times "correct" this difference and return the correct date, except for the years 1943, 1957, 1984, 2011, 2038 and 2052.

Philip (maisonclarke@ntlworld.com) from UK wrote (2004-05-19 15:39:58 CET):

Easter
Do you know why the date of Easter calculated by the userdefined function gives a different date to the worksheet formula for certain years? Both appear to be the same formula by Hetterich, expressed in a different way!

PS I love the site! I have found it very useful on a number of occasions.