Holiday Functions

Often it is useful to return the date of a holiday for a given year, perhaps for a schedule application. Neither Excel nor VBA have any built in functions for working with holidays -- you have to create your own. Holidays can be thought of as being either Fixed or Floating. Fixed holidays are those that occur on the same day each year, such as Christmas. Floating holidays are those which occur on different days in different years. For example, Thanksgiving (in the US) occurs on the fourth Thursday of November. Therefore, we need a function that will calculate the fourth Thursday. We'll generalize that function, in both VBA and worksheet function form, to return the Nth day-of-week for any month and year.

Other floating holidays are a bit harder to calculate. For example, in the US, Memorial Day occurs on the last Monday of May. Depending on the year, this may be either the 4th or 5th Monday. So we need a function to calculate the number of Mondays in May. We'll generalize this to compute the number of any day-of-week in any month and year.

Finally, there is Easter, whose actual date is some bizarre result of the phases of the moon. I don't claim credit for the formulas for calculating Easter shown below, nor do I claim to understand why the work, but they do.



Fixed Holidays
For fixed holidays, such as Christmas, this is simple since the date of the holiday does not change from year to year. For example, use the following to return the date of Christmas in the current year:

=DATE(YEAR(NOW()),12,25)


Floating Holidays
Other holidays, however, are not assigned to a specific date. For example, Thanksgiving Day is defined to be the 4th Thursday of November. Therefore its exact date will change from year to year. For Thanksgiving, we have an explicit VBA function:

Public Function ThanksgivingDate(Yr As Integer) As Date
ThanksgivingDate = DateSerial(Yr, 11, 29 - _
WeekDay(DateSerial(Yr, 11, 1), vbFriday))
End Function

We can generalize this to holidays that are defined as the Nth Day of some month, such as Martin Luther King's birthday, celebrated on the 3rd Monday of January. The following function will return the Nth DayOfWeek for a given month and year:

Public Function NDow(Y As Integer, M As Integer, _
N As Integer, DOW As Integer) As Date

NDow = DateSerial(Y, M, (8 - WeekDay(DateSerial(Y, M, 1), _
(DOW + 1) Mod 8)) + ((N - 1) * 7))
End Function

To return the date of the 3rd Monday in January of 1998, use
=NDow (1998, 1, 3, 2)


The NDow function can also be written as a worksheet formula:
=DATE(Yr,Mon,1+((Nth-(DoW>=WEEKDAY(DATE(Yr,Mon,1))))*7)
+(DoW-WEEKDAY(DATE(Yr,Mon,1))))

Where Yr,Mon, and DoW are cell references or values indicating Year, Month, Nth, and Day-Of-Week.


This will work fine for most floating holidays. However, Memorial Day is celebrated on the Last Monday of May. To compute this date, we first need a function to tell us how many Mondays there are in the month.

Public Function DOWsInMonth(Yr As Integer, M As Integer, _
DOW As Integer) As Integer

On Error GoTo EndFunction

Dim I As Integer
Dim Lim As Integer
Lim = Day(DateSerial(Yr, M + 1, 0))
DOWsInMonth = 0
For I = 1 To Lim
If WeekDay(DateSerial(Yr, M, I)) = DOW Then
DOWsInMonth = DOWsInMonth + 1
End If
Next I

Exit Function
EndFunction:
DOWsInMonth = 0
End Function


Calling this function will tell us how many Mondays there are in May, 1998.
=DOWsInMonth(1998, 5, 2)

The DOWsInMonth can also be written as a worksheet function with the following array formula. Note that it will not work properly unless you press CTRL+SHIFT+ENTER instead of just ENTER when you first enter the formula and whenever you edit it later. If you do this properly, Excel will display the formula in the formula bar enclosed in curly braces {}.


=SUM((WEEKDAY(DATE(B3,C3,(ROW(INDIRECT
("1:"&DAY(DATE(B3,C3+1,0)))))))=D3)*1)

where B3 is the year, C3 is the month, and D3 is the day of week (1=Sunday, 2=Monday, ..., 7=Saturday)

There are 4 Mondays in May, 1998. The we can pass this into the NDOW function, to return 25-May-1998, the date of Memorial Day. Therefore, the formula to return the date of Memorial Day in 1999 would be

=NDow(1999,5,DowsInMonth(1999,5,2),2)

Many organizations recognize holiday dates on dates different than those of the actual date. Typically, this is done when the day of week of the holiday falls on a weekend and holidays are observed to make a three-day weekend. The general rule is that if the holiday falls on a Saturday, it is observed on the Friday before the holiday. If the holiday falls on a Sunday, it is observed on the following Monday. The following formula will return a Friday if the holiday falls on a Saturday, a Monday if the holiday falls on a Sunday, or the date itself if it falls on a weekday.=IF(WEEKDAY(A2, 1)=1,A2+1,IF(WEEKDAY(A2,1)=7,A2-1,A2)) where A2 is the date of the holiday. In VBA, you can use the following function:

Function Observed(TheDate As Date) As Date
If Weekday(TheDate, vbSunday) = 1 Then
Observed = TheDate + 1
ElseIf Weekday(TheDate, vbSunday) = 7 Then
Observed = TheDate - 1
Else
Observed = TheDate
End If
End Function

where TheDate is the date if the holiday.


Easter

Worksheet Formula For Easter
You can calculate the date of Easter with formula below. It is accurate for the years from 1900 to 2368. The formula is:

=FLOOR("5/"&DAY(MINUTE(YYYY/38)/2+56)&"/"&YYYY,7)-34

where YYYY is a four digit year between 1900 and 2368. The formula returns an incorrect for some years past 2369.


VBA Function For Easter
The date of Easter can be computed in VBA. The algorithm below is from the United States Naval Observatory, at Computing The Date Of Easter.

Public Function EasterUSNO(YYYY As Long) As Long
Dim C As Long
Dim N As Long
Dim K As Long
Dim I As Long
Dim J As Long
Dim L As Long
Dim M As Long
Dim D As Long

C = YYYY \ 100
N = YYYY - 19 * (YYYY \ 19)
K = (C - 17) \ 25
I = C - C \ 4 - (C - K) \ 3 + 19 * N + 15
I = I - 30 * (I \ 30)
I = I - (I \ 28) * (1 - (I \ 28) * (29 \ (I + 1)) * ((21 - N) \ 11))
J = YYYY + YYYY \ 4 + I + 2 - C + C \ 4
J = J - 7 * (J \ 7)
L = I - J
M = 3 + (L + 40) \ 44
D = L + 28 - 31 * (M \ 4)
EasterUSNO = DateSerial(YYYY, M, D)
End Function
You can call this from a worksheet cell with a formula like

=EasterUSNO(YYYY)

where YYYY is a four digit year.

http://www.cpearson.com/excel/holidays.htm
http://www.cpearson.com/excel/Easter.aspx