US Holidays Calculations 

holiday logo
msofficeshare logo

Requirement: Visual Basic Knowledge

File to download:  Holiday.xlsm

Related Articles: Date FunctionsWORKDAY and NETWORKDAYS functionsCalculation 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:

pic1 holidays

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).