Calculate the date for Easter Sunday and Holidays
2001-03-14 Dates 0 199
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 FunctionWhen 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
See also how to calculate the date for Easter Sunday using Power Query.
See also how to determine if a date is a workday using Power Query.