Requirement: Visual Basic Knowledge

File to download: Holiday.xlsm

Related Articles: Date Functions, WORKDAY and NETWORKDAYS functions, Calculation of Bond Payment Dates

Calculating the holidays is no easy task. This article addresses only the United States holidays because I am not familiar with the holiday structures of other countries. There are many rules of how they are calculated. Many of them are floating holidays. It means that those holidays do not fall on a certain day like New Year (1st of January) or Christmas (25th of December). For example, the Martin Luther King's birthday falls on the 3rd Monday of January and Presidents' Day falls the 3rd Monday in February, so the dates of those holidays will change every year. The most difficult holiday to calculate is the Easter as some companies choose the Good Friday as a company holiday. The reason is that the Easter date is set around the time of the March Equinox which is based on the moon.

Below is the summary of the rules.

- New Year: 1st of January
- Martin Luther King’s birthday: 3rd Monday of January
- Presidents’ Day: 3rd Monday in February
- Good Friday: 2 days before Easter Sunday. The Easter date is set around the time of the March Equinox which is based on the moon.
- Memorial Day: Last Monday of May
- Independence Day: 4th of July
- Labor Day: 1st Monday in September
- Columbus Day: 2nd Monday in October
- Veterans Day: 11th of November
- Thanksgiving Day: 4th Thursday of November
- Black Friday: The day after Thanksgiving
- Christmas Eve: 24th of December
- Christmas: 25th of December

It goes even more complicated that if the designated holidays fall on the weekend, they will be observed on either the preceding Friday or the following Monday. New Year, Independence Day, Veterans Day, and Christmas will have related observed holidays if any of them falls on Saturday or Sunday.

The rules for the observed holidays are as below:

- If the date of the holiday falls on Sunday, the observed holiday will be on the following Monday.
- If the date of the holiday falls on Saturday, the observed holiday will be on the preceding Friday.

As a result of the rules of observed holidays, it is possible that the New Year is observed on the 31st of December of prior year if the New Year falls on a Saturday.

Holidays are essential to many calculations. For example, if you want to use the NETWORKDAYS or WORKDAY function to calculate the number of working days for a project and the completion date of such project and you want to exclude the weekends and holidays, you must know the related holidays within the date range of your project. You can get a calendar and manually type in those holidays. It is certainly a pain to do so, especially if the project spans over several years.

Another example is what I encountered when I needed to calculate the “Bond Payment Dates”. There are bonds that accrue interest on the basis of Actual/360 or Actual/Actual. For example, if a bond has a payment date of 25th of each month, the accrual days for a period will be from 25th of this month to 24th of next month (The accrual end date is a day before the payment date). However, if the next payment date falls on a weekend or holiday, the next payment date will be on the day after such weekend or holiday. The number of accrual days will be extended as well to include the weekend or holiday. For example, December 25th of 2015 falls on Friday. So the payment date of a bond will be on December 28th (Monday) instead. So the interest accrual period of such bond will be from November 25th, 2015 to December 27th, 2015, which is 33 days. The next interest accrual period will be from December 28th, 2015 to January 24th, 2016, which is only 28 days. What makes it even more complicated is that we need to project such interest payments for 15 or 30 years for both waterfall and tax calculation purposes. If you don’t have a way to automate the holiday calculations, just imagine the amount of work you need to do so and if you are lucky enough to find such information that you can manually put in.

When I did the research on the internet, there was only limited information on how to calculate the holidays. There are a few websites show you how to create functions to calculate the holidays but none of them allow you to generate a list of holidays. In addition, the functions are not so handy because there are different ways to calculate different holidays. The user needs to know which function to be applied to calculate certain holidays. Luckily, there are two websites provide enough information for me to create my own visual basic program to tailor make what I need. The two websites are:

- Formulas and VB codes to calculate holidays (other than Easter) are from http://www.cpearson.com/excel/holidays.htm website.
- VB Codes to calculate Easter are from http://www.contextures.com/exceleastercalculation.html website.

The above illustration is the snapshot of the interface of the program. First of all, the user must enable the VB module to perform the calculations. Then the user needs to input the values for the fields “From Year” and “To Year” to tell the VB program what is the scope (year) of holidays to be generated. Secondly, the user has the flexibility to generate some non-federal holidays. The federal holidays are specific for government employees, banks, and bond markets. There are 10 federal holidays altogether:

- New Year
- Martin Luther King Day
- Presidents' Day
- Memorial Day
- Independence Day
- Labor Day
- Columbus Day
- Veterans Day
- Thanksgiving
- Christmas

However, there are many companies and schools choose other days as holidays. Many companies do not close on Martin Luther King Day, Presidents’ Day, Columbus Day, and Veterans Day. Instead, companies may choose to close on Good Friday, the day after Thanksgiving (Black Friday), and Christmas Eve. So the flexibility of this program should substantially widen its applicability. The user only needs to check all the boxes that his or her company elects to be holidays.

__Note 1__

The Christmas Eve does not have an “observed” day for itself if it falls on the weekend. There are no clear rules how the day can be calculated as observed day. For example, if the Christmas Eve falls on Saturday, Christmas will fall on Sunday. Christmas will be observed on Monday according to the observed rules. Then should the Christmas Eve to be observed on preceding Friday or the following Tuesday? As a result, the VB module will not calculate an observed day for the Christmas Eve. It normally becomes a floating holiday that employees are free to pick a day off by themselves.

__Note 2__

I set a validation rule for the Year Input. The Easter calculation is only good for the time period after year 1899. If a user put in a year before 1900 for either the “From Year” or “To Year” field, an error message will pop up and ask the user to correct the input.

__Note 3__

All the holiday calculations are based on the above holiday rules. If the US Government decides to modify some of the rules (which is highly unlikely) in the future, the program must be modified accordingly.

Below is the core visual basic (VB) module (CalcHolidays). It has a passing parameter of “InputYear” from the main module (see below).

Sub CalcHolidays(InputYear)

Dim NewYear As Date, MartinLutherKingDay As Date, PresidentsDay As Date, MemorialDay As Date, IndependenceDay As Date

Dim LaborDay As Date, ColumbusDay As Date, VeteransDay As Date, ThanksgivingDate As Date, ChristmasEve As Date, Christmas As Date

Dim NY_Observe As Integer, ID_Observe As Integer, VD_Observe As Integer, C_Observe As Integer

'New Year

NY_Observe = 0 'Initialize variable, 0 = Non-observed date, 1 = Observed date

NewYear = DateSerial(InputYear, 1, 1)

Call CalcObservedHoliday(NewYear, NY_Observe)

'Martin Luther King Day

MartinLutherKingDay = DateSerial(InputYear, 1, (8 - Weekday(DateSerial(InputYear, 1, 1), _

(2 + 1) Mod 8)) + ((3 - 1) * 7))

'Presidents' Day

PresidentsDay = DateSerial(InputYear, 2, (8 - Weekday(DateSerial(InputYear, 2, 1), _

(2 + 1) Mod 8)) + ((3 - 1) * 7))

'Good Friday

Dim C, d, N, k, i, J, L, m, y As Integer

y = Val(InputYear)

If Not IsDate("1/1/" & y) Or y < 1900 Then

CalculateEaster = "Year Limit Error"

End If

C = y \ 100

N = y - 19 * (y \ 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 = y + y \ 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)

CalculateEaster = DateSerial(y, m, d)

GoodFriday = CalculateEaster - 2

'Memorial Day

For i = 1 To 31 '31 days in May

If Weekday(DateSerial(InputYear, 5, i)) = 2 Then '2 = Monday

MemorialDay = DateSerial(InputYear, 5, i) 'the last Monday will overwrite all previous Mondays

End If

Next i

'Independence Day

ID_Observe = 0

IndependenceDay = DateSerial(InputYear, 7, 4)

Call CalcObservedHoliday(IndependenceDay, ID_Observe)

'Labor Day

LaborDay = DateSerial(InputYear, 9, (8 - Weekday(DateSerial(InputYear, 9, 1), _

(2 + 1) Mod 8)))

'Columbus Day

ColumbusDay = DateSerial(InputYear, 10, (8 - Weekday(DateSerial(InputYear, 10, 1), _

(2 + 1) Mod 8)) + ((2 - 1) * 7))

'Veterans Day

VD_Observe = 0

VeteransDay = DateSerial(InputYear, 11, 11)

Call CalcObservedHoliday(VeteransDay, VD_Observe)

'Thanksgiving

Thanksgiving = DateSerial(InputYear, 11, 29 - _

Weekday(DateSerial(InputYear, 11, 1), vbFriday))

'Black Friday

BlackFriday = Thanksgiving + 1

'Christmas

ChristmasEve = DateSerial(InputYear, 12, 24) 'Not to calculate the observed Christmas Eve if it falls on Saturday or Sunday

'Christmas

C_Observe = 0

Christmas = DateSerial(InputYear, 12, 25)

Call CalcObservedHoliday(Christmas, C_Observe)

'Write Calendar List

If Range("NY_Choice").Value = True Then

If NY_Observe = 0 Then

ActiveCell.Value = "New Year"

Else

ActiveCell.Value = "New Year (Observed)"

End If

ActiveCell.Offset(0, 1).Select

ActiveCell.Value = NewYear

ActiveCell.Offset(1, -1).Select

End If

If Range("MLKD_Choice").Value = True Then

ActiveCell.Value = "Martin Luther King Day"

ActiveCell.Offset(0, 1).Select

ActiveCell.Value = MartinLutherKingDay

ActiveCell.Offset(1, -1).Select

End If

If Range("PD_Choice").Value = True Then

ActiveCell.Value = "Presidents' Day"

ActiveCell.Offset(0, 1).Select

ActiveCell.Value = PresidentsDay

ActiveCell.Offset(1, -1).Select

End If

If Range("GF_Choice").Value = True Then

ActiveCell.Value = "Good Friday"

ActiveCell.Offset(0, 1).Select

ActiveCell.Value = GoodFriday

ActiveCell.Offset(1, -1).Select

End If

If Range("MD_Choice").Value = True Then

ActiveCell.Value = "Memorial Day"

ActiveCell.Offset(0, 1).Select

ActiveCell.Value = MemorialDay

ActiveCell.Offset(1, -1).Select

End If

If Range("ID_Choice").Value = True Then

If ID_Observe = 0 Then

ActiveCell.Value = "Independence Day"

Else

ActiveCell.Value = "Independence Day (Observed)"

End If

ActiveCell.Offset(0, 1).Select

ActiveCell.Value = IndependenceDay

ActiveCell.Offset(1, -1).Select

End If

If Range("LD_Choice").Value = True Then

ActiveCell.Value = "Labor Day"

ActiveCell.Offset(0, 1).Select

ActiveCell.Value = LaborDay

ActiveCell.Offset(1, -1).Select

End If

If Range("CD_Choice").Value = True Then

ActiveCell.Value = "Columbus Day"

ActiveCell.Offset(0, 1).Select

ActiveCell.Value = ColumbusDay

ActiveCell.Offset(1, -1).Select

End If

If Range("VD_Choice").Value = True Then

If VD_Observe = 0 Then

ActiveCell.Value = "Veterans Day"

Else

ActiveCell.Value = "Veterans Day (Observed)"

End If

ActiveCell.Offset(0, 1).Select

ActiveCell.Value = VeteransDay

ActiveCell.Offset(1, -1).Select

End If

If Range("T_Choice").Value = True Then

ActiveCell.Value = "Thanksgiving"

ActiveCell.Offset(0, 1).Select

ActiveCell.Value = Thanksgiving

ActiveCell.Offset(1, -1).Select

End If

If Range("BF_Choice").Value = True Then

ActiveCell.Value = "Black Friday"

ActiveCell.Offset(0, 1).Select

ActiveCell.Value = BlackFriday

ActiveCell.Offset(1, -1).Select

End If

If Range("CE_Choice").Value = True Then

ActiveCell.Value = "Christmas Eve"

ActiveCell.Offset(0, 1).Select

ActiveCell.Value = ChristmasEve

ActiveCell.Offset(1, -1).Select

End If

If Range("C_Choice").Value = True Then

If C_Observe = 0 Then

ActiveCell.Value = "Christmas"

Else

ActiveCell.Value = "Christmas (Observed)"

End If

ActiveCell.Offset(0, 1).Select

ActiveCell.Value = Christmas

ActiveCell.Offset(1, -1).Select

End If

End Sub

New Year, Independence Day, Veterans Day, and Christmas will have related observed holidays if any of them falls on Saturday or Sunday.

- If the date of the holiday falls on Sunday, the observed holiday will be on the following Monday
- If the date of the holiday falls on Saturday, the observed holiday will be on the preceding Friday

This part is created as a subroutine (CalcObservedHoliday) with passing parameters (HolidayDate and ObserveStatus). It will be called by the core module (CalcHolidays) 4 times for the 4 holidays that have the observed feature.

Sub CalcObservedHoliday(HolidayDate, ObserveStatus)

If Weekday(HolidayDate, vbSunday) = 1 Then

Observed = HolidayDate + 1

ObserveStatus = 1

ElseIf Weekday(HolidayDate, vbSunday) = 7 Then

Observed = HolidayDate - 1

ObserveStatus = 1

Else

Observed = HolidayDate

End If

HolidayDate = Observed

End Sub

Finally, the main module (HolidayList) will do the looping of the core module. If the user chooses a 5 year span, the core module will be looped 5 times.

Sub HolidayList()

Dim BeginYear As Integer, EndYear As Integer, InputYear As Integer

Dim i As Integer

Range("E2:F2000").Select

Selection.ClearContents

BeginYear = Range("BeginYear").Value

EndYear = Range("EndYear").Value

Range("E2").Activate 'Starting point to write the Holiday List

For i = BeginYear To EndYear

Call CalcHolidays(i)

Next i

End Sub

The WORKDAY and NETWORKDAYS functions will be covered separately in this article (WORKDAY and NETWORKDAYS functions).

The Bond Payment Date Calculation will be covered separately in this article (Calculation of Bond Payment Dates).