Calculate Holidays

2001-03-14    Dates    0    74

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
```