US Holidays Calculations
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 https://www.cpearson.com/excel/holidays.htm website.
- VB Codes to calculate Easter are from https://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).
