Erlandsen Data Consulting

Calculate the date for Easter Sunday and Holidays

2001-03-14 Dates 0 105

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

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. You'll have to customize the function to include the specific holidays for your country.

Function DateIsHoliday(InputDate As Long) As Boolean
' returns True if InputDate is a Saturday/Sunday or a holiday (Norwegian)
Dim d As Integer, intYear As Integer, lngEasterSunday As Long, OK As Boolean
    OK = False
    If InputDate > 0 Then
        If Weekday(InputDate, vbMonday) >= 6 Then ' Saturday or Sunday
            OK = True
        End If
        If Not OK Then ' check if InputDate is a holiday
            intYear = Year(InputDate)
            d = (((255 - 11 * (intYear Mod 19)) - 21) Mod 30) + 21
            lngEasterSunday = DateSerial(intYear, 3, 1) + d + (d > 48) + 6 - ((intYear + intYear \ 4 + d + (d > 48) + 1) Mod 7)
            OK = True
            Select Case InputDate
                Case CDate("1.1." & intYear) ' 1. January
                'Case lngEasterSunday - 4 ' Wednesday before Easter
                Case lngEasterSunday - 3 ' Thursday before Easter
                Case lngEasterSunday - 2 ' Friday before Easter
                Case lngEasterSunday ' Easter Sunday
                Case lngEasterSunday + 1 ' Monday after Easter
                Case CDate("1.5." & intYear) ' 1. May
                Case CDate("17.5." & intYear) ' 17. May
                Case lngEasterSunday + 39 ' Ascension Day
                'Case lngEasterSunday + 48 ' Pentecost
                Case lngEasterSunday + 49 ' Pentecost
                Case lngEasterSunday + 50 ' Pentecost
                'Case CDate("24.12." & intYear) ' Christmas
                Case CDate("25.12." & intYear) ' Christmas
                Case CDate("26.12." & intYear) ' Christmas
                'Case CDate("31.12." & intYear) ' New Years Eve
                Case Else
                    OK = False
            End Select
        End If
    End If
    DateIsHoliday = OK
End Function