ERLANDSEN DATA CONSULTING Excel & VBA Tips

# 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