Erlandsen Data Consulting

Calculate the date for Easter Sunday and Holidays

2001-03-14 Dates 0 345

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

Function EasterSunday(InputYear As Long) As Long
' returns the date for Easter Sunday for years between 1900 - 2099
    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).

Update: With a small update this formula can return the date for Easter Sunday for all years between 1900 - 2203:

=LET(
  InputYear,A1,
  EasterSunday,FLOOR.MATH(DATE(InputYear,5,DAY(MINUTE(InputYear/38)/2+56)),7)-34,
  EasterSunday+IF(InputYear=2079,7,0)
)

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)
This formula return the date for Easter Sunday for all years between 1900 - 2203:
=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, Optional blnInclWeekends As Boolean = True) As Boolean
' returns True if InputDate is a holiday (Norwegian) or optionally a Saturday/Sunday
    If blnInclWeekends Then
        If Weekday(InputDate, vbMonday) >= 6 Then ' Saturday or Sunday
            DateIsHoliday = True
            Exit Function
        End If
    End If
    
    Dim OK As Boolean, lngYear As Long, lngEasterSunday As Long
    lngYear = Year(InputDate)
    lngEasterSunday = EasterSunday(lngYear) ' function above
    Select Case InputDate
        Case CDate("1.1." & lngYear) ' 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." & lngYear) ' 1. May
        Case CDate("17.5." & lngYear) ' 17. May
        Case lngEasterSunday + 39 ' Ascension Day
        'Case lngEasterSunday + 48 ' Pentecost
        Case lngEasterSunday + 49 ' Pentecost
        Case lngEasterSunday + 50 ' Pentecost
        'Case CDate("24.12." & lngYear) ' Christmas
        Case CDate("25.12." & lngYear) ' Christmas
        Case CDate("26.12." & lngYear) ' Christmas
        'Case CDate("31.12." & ilngYear) ' New Years Eve
        Case Else
            OK = False
    End Select
    
    DateIsHoliday = OK
End Function