# Holidays



## Jonmo1 (May 9, 2008)

Thought I'd share some Code I wrote to build an array of holidays.
Adds major holidays (New Years, Easter, Momorial, 4th July, Labor, Thanksgiving, Day After Thanksgiving, Christmas)
to an array that can be used in the networkdays function. Or other functions.
My company recognizes the day after thanksgiving as a holiday as well, that is noted as ThanksGiving2 in the code.

=MyHdays(Start Year, End Year)

So you can use this in NetworkDays like

=NetworkDays(A1,B1,MyHdays(2008,2010))

Both Start Year and End Year are optional..
If only End Year is omitted, returns holidays for Start Year Specified.
if only Start Year is omitted, returns holidays for Current Year THROUGH End year specified.
If BOTH are omitted, returns holidays for current year only.

I got the Ndow code, and the formula for Easter from Cpearson's website 
http://www.cpearson.com/excel/mainpage.aspx. 
The rest came from my own head.

Hope you find this usefull


```
Public Function MyHdays(Optional SY As Long, Optional EY As Long) As Variant
'Builds an Array of Hodidays for the specified Range of Years.
'New Years Day, Easter, Momorial Day, Independance Day, Labor Day,
'ThanksGiving, Day After ThanksGiving, Christmas
'Uses 2 other functions, Hday and Ndow
Dim test() As Variant
Dim i As Integer, x As Long, z
Dim Holidays
Dim hdy As String
Holidays = Array("New Years", "Easter", "Momorial Day", "Independance Day", "Labor Day", "ThanksGiving", "ThanksGiving2", "Chirstmas")
If SY = 0 And EY = 0 Then
    SY = Year(Date)
    EY = Year(Date)
ElseIf SY = 0 And EY <> 0 Then
    SY = Year(Date)
ElseIf SY <> 0 And EY = 0 Then
    EY = SY
End If
z = 1
For i = SY To EY
    For x = LBound(Holidays) To UBound(Holidays)
        hdy = Holidays(x)
        ReDim Preserve test(1 To z)
        If Holidays(x) = "ThanksGiving2" Then
            test(z) = Hday(hdy, i) + 1
            z = z + 1
        Else
            test(z) = Hday(hdy, i)
            z = z + 1
        End If
    Next x
Next i
MyHdays = test
End Function
 
Public Function NDow(Nth As Integer, DOW As Integer, Optional MyMonth As Integer, _
Optional MyYear As Integer) As Date
'Returns the Nth Weekday(Monday/Tuesday/etc) Date of the given month(MyMonth)/year(MyYear)
'if MyMonth and/or MyYear are omitted, the current month/year is used.
 
If MyMonth = 0 Then MyMonth = Month(Date)
If MyYear = 0 Then MyYear = Year(Date)
NDow = DateSerial(MyYear, MyMonth, (8 - Weekday(DateSerial(MyYear, MyMonth, 1), _
  (DOW + 1) Mod 8)) + ((Nth - 1) * 7))
End Function
 
Public Function Hday(hdy As String, Optional YR As Integer)
'Returns the Date of the specified Holiday in the Specified Year
'Hdy you only need to enter the first character of the holiday name.  Will still work with entire name
'Finds only the following 7 Holidays
'NewYearsDay, IndependenceDay, LaborDay, MomorialDay, ThanksGiving, Christmas, Easter
 
Dim D As Long
If YR = 0 Then YR = Year(Date)
Select Case UCase(Left(hdy, 1))
    Case "N" ' New Years Day
        Hday = DateSerial(YR, 1, 1)
    Case "I" ' Independence Day
        Hday = DateSerial(YR, 7, 4)
    Case "C" ' Christmas
        Hday = DateSerial(YR, 12, 25)
    Case "T" 'Thansgiving
        Hday = NDow(4, 5, 11, YR)
    Case "M" 'Momorial Day
        Hday = NDow(1, 2, 6, YR) - 7
    Case "L" 'Labor Day
        Hday = NDow(1, 2, 9, YR)
    Case "E" ' Easter
        D = (((255 - 11 * (YR Mod 19)) - 21) Mod 30) + 21
        Hday = DateSerial(YR, 3, 1) + D + (D > 48) + 6 - ((YR + YR \ 4 + D + (D > 48) + 1) Mod 7)
    End Select
End Function
```


----------



## Greg Truby (May 9, 2008)

FWIW, here are the US holiday formulae, running off of New Year's as the starting point:

Excel WorkbookAB1NYD01-Jan-082MLK21-Jan-083Pres18-Feb-084Mem26-May-085Ind04-Jul-086Lab01-Sep-087TG27-Nov-088daTG28-Nov-089Xmas25-Dec-08Sheet1



Astute observers will note the use of EDATE() which requires that Analysis Toolpak be installed. No Easter, though.


----------



## Zack Barresse (May 9, 2008)

Here is a few functions I re-wrote a while back for most holidays.  The TestIt() and PreviousDay() routines are just the particular test the person (whom I was helping) wanted to run for the holidays (check days prior, etc).  The functions should work unto themselves.  Note Easter will need to be changed after quite a few years though.  The 'additionals' functions were not originally written by me.




<font face=Courier New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN><br><br><SPAN style="color:#007F00">'////////////////////////////////////////////////////////////////////</SPAN><br><SPAN style="color:#007F00">'TOGGLE WHICH DAYS TO RECOGNIZE</SPAN><br><SPAN style="color:#007F00">'////////////////////////////////////////////////////////////////////</SPAN><br><br><SPAN style="color:#00007F">Const</SPAN> bSaturday <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN> = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">Const</SPAN> bSunday <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN> = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">Const</SPAN> NewYears <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN> = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">Const</SPAN> MartinLutherKing <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN> = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">Const</SPAN> Groundhogs <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN> = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">Const</SPAN> Valentines <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN> = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">Const</SPAN> Easter <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN> = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">Const</SPAN> Presidents <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN> = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">Const</SPAN> Memorial <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN> = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">Const</SPAN> Independence <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN> = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">Const</SPAN> Labor <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN> = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">Const</SPAN> Columbus <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN> = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">Const</SPAN> Thanksgiving <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN> = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">Const</SPAN> Veterans <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN> = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">Const</SPAN> Christmas <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN> = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">Const</SPAN> ChristmasEve <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN> = <SPAN style="color:#00007F">True</SPAN><br><br><SPAN style="color:#007F00">'////////////////////////////////////////////////////////////////////</SPAN><br><SPAN style="color:#007F00">'CALCULATE WORKDAYS WITH HOLIDAYS</SPAN><br><SPAN style="color:#007F00">'////////////////////////////////////////////////////////////////////</SPAN><br><br><SPAN style="color:#00007F">Sub</SPAN> TestIt()<br>    <SPAN style="color:#00007F">Dim</SPAN> iOffset <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, dtStart <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Date</SPAN>, dtFinal <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Date</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, s <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <SPAN style="color:#00007F">Const</SPAN> DNL <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = vbNewLine & vbNewLine<br>    dtStart = DateValue("December 26, 2007")    <SPAN style="color:#007F00">'starting date</SPAN><br>    <SPAN style="color:#007F00">'For today's date, use:</SPAN><br>    <SPAN style="color:#007F00">'    dtStart = Date</SPAN><br>    iOffset = 2    <SPAN style="color:#007F00">'workdays prior to get</SPAN><br>    <SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> 6<br>        dtStart = DateSerial(2007, 12, i + 20)    <SPAN style="color:#007F00">'starting date</SPAN><br>        dtFinal = PreviousDay(dtStart, iOffset)<br>        s = s & "Start: " & Format(dtStart, "mm/dd/yyyy - dddd") & vbNewLine & "Finish: " & Format(dtFinal, "mm/dd/yyyy - dddd") & DNL<br>    <SPAN style="color:#00007F">Next</SPAN> i<br>    MsgBox s, vbInformation, "COMPLETE!"<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br><SPAN style="color:#00007F">Function</SPAN> PreviousDay(dtPrior <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Date</SPAN>, <SPAN style="color:#00007F">Optional</SPAN> vOffset <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN> = &H0) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Date</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> dtTemp <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Date</SPAN>, iMinusDays <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, iHolidays <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, iStep <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> iCnt <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, xMinusDays <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, xHolidays <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    iStep = 0: iCnt = 0<br>    <SPAN style="color:#00007F">Do</SPAN> <SPAN style="color:#00007F">Until</SPAN> iCnt > vOffset<br>        dtTemp = dtPrior - iStep: Debug.Print dtTemp<br>        <SPAN style="color:#00007F">If</SPAN> Weekday(dtTemp) = 7 And bSaturday = <SPAN style="color:#00007F">True</SPAN> <SPAN style="color:#00007F">Then</SPAN> iMinusDays = iMinusDays + 1<br>        <SPAN style="color:#00007F">If</SPAN> Weekday(dtTemp) = 1 And bSunday = <SPAN style="color:#00007F">True</SPAN> <SPAN style="color:#00007F">Then</SPAN> iMinusDays = iMinusDays + 1<br>        <SPAN style="color:#00007F">If</SPAN> NewYears = <SPAN style="color:#00007F">True</SPAN> <SPAN style="color:#00007F">Then</SPAN> iHolidays = iHolidays + IsNewYears(dtTemp)<br>        <SPAN style="color:#00007F">If</SPAN> MartinLutherKing = <SPAN style="color:#00007F">True</SPAN> <SPAN style="color:#00007F">Then</SPAN> iHolidays = iHolidays + IsMartinLutherKing(dtTemp)<br>        <SPAN style="color:#00007F">If</SPAN> Groundhogs = <SPAN style="color:#00007F">True</SPAN> <SPAN style="color:#00007F">Then</SPAN> iHolidays = iHolidays + IsGroundhogs(dtTemp)<br>        <SPAN style="color:#00007F">If</SPAN> Valentines = <SPAN style="color:#00007F">True</SPAN> <SPAN style="color:#00007F">Then</SPAN> iHolidays = iHolidays + IsValentines(dtTemp)<br>        <SPAN style="color:#00007F">If</SPAN> Easter = <SPAN style="color:#00007F">True</SPAN> <SPAN style="color:#00007F">Then</SPAN> iHolidays = iHolidays + IsEaster(dtTemp)<br>        <SPAN style="color:#00007F">If</SPAN> Presidents = <SPAN style="color:#00007F">True</SPAN> <SPAN style="color:#00007F">Then</SPAN> iHolidays = iHolidays + IsPresidents(dtTemp)<br>        <SPAN style="color:#00007F">If</SPAN> Memorial = <SPAN style="color:#00007F">True</SPAN> <SPAN style="color:#00007F">Then</SPAN> iHolidays = iHolidays + IsMemorial(dtTemp)<br>        <SPAN style="color:#00007F">If</SPAN> Independence = <SPAN style="color:#00007F">True</SPAN> <SPAN style="color:#00007F">Then</SPAN> iHolidays = iHolidays + IsIndependence(dtTemp)<br>        <SPAN style="color:#00007F">If</SPAN> Labor = <SPAN style="color:#00007F">True</SPAN> <SPAN style="color:#00007F">Then</SPAN> iHolidays = iHolidays + IsLabor(dtTemp)<br>        <SPAN style="color:#00007F">If</SPAN> Columbus = <SPAN style="color:#00007F">True</SPAN> <SPAN style="color:#00007F">Then</SPAN> iHolidays = iHolidays + IsColumbus(dtTemp)<br>        <SPAN style="color:#00007F">If</SPAN> Thanksgiving = <SPAN style="color:#00007F">True</SPAN> <SPAN style="color:#00007F">Then</SPAN> iHolidays = iHolidays + IsThanksgiving(dtTemp)<br>        <SPAN style="color:#00007F">If</SPAN> Veterans = <SPAN style="color:#00007F">True</SPAN> <SPAN style="color:#00007F">Then</SPAN> iHolidays = iHolidays + IsVeterans(dtTemp)<br>        <SPAN style="color:#00007F">If</SPAN> Christmas = <SPAN style="color:#00007F">True</SPAN> <SPAN style="color:#00007F">Then</SPAN> iHolidays = iHolidays + IsChristmas(dtTemp)<br>        <SPAN style="color:#00007F">If</SPAN> ChristmasEve = <SPAN style="color:#00007F">True</SPAN> <SPAN style="color:#00007F">Then</SPAN> iHolidays = iHolidays + IsChristmasEve(dtTemp)<br>        iStep = iStep + 1<br>        <SPAN style="color:#00007F">If</SPAN> iMinusDays = 0 And iHolidays = 0 <SPAN style="color:#00007F">Then</SPAN> iCnt = iCnt + 1<br>        <SPAN style="color:#00007F">If</SPAN> iMinusDays > 0 <SPAN style="color:#00007F">Then</SPAN> xMinusDays = xMinusDays + iMinusDays: iMinusDays = 0<br>        <SPAN style="color:#00007F">If</SPAN> iHolidays <> 0 <SPAN style="color:#00007F">Then</SPAN> xHolidays = xHolidays + iHolidays: iHolidays = 0<br>    <SPAN style="color:#00007F">Loop</SPAN><br>    PreviousDay = dtPrior - vOffset + xHolidays - xMinusDays<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN><br><br><br><SPAN style="color:#007F00">'////////////////////////////////////////////////////////////////////</SPAN><br><SPAN style="color:#007F00">'NON-FLOATING HOLIDAYS</SPAN><br><SPAN style="color:#007F00">'////////////////////////////////////////////////////////////////////</SPAN><br><br><SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">Function</SPAN> IsNewYears(dtTest <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Date</SPAN>) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN> <SPAN style="color:#007F00">'Jan-01</SPAN><br>    IsNewYears = <SPAN style="color:#00007F">CBool</SPAN>(Int(dtTest) = Int(DateSerial(Year(dtTest), 1, 1)))<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN><br><SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">Function</SPAN> IsGroundhogs(dtTest <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Date</SPAN>) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN> <SPAN style="color:#007F00">'Feb-02</SPAN><br>    IsGroundhogs = <SPAN style="color:#00007F">CBool</SPAN>(Int(dtTest) = Int(DateSerial(Year(dtTest), 2, 2)))<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN><br><SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">Function</SPAN> IsValentines(dtTest <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Date</SPAN>) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN> <SPAN style="color:#007F00">'Feb-14</SPAN><br>    IsValentines = <SPAN style="color:#00007F">CBool</SPAN>(Int(dtTest) = Int(DateSerial(Year(dtTest), 2, 14)))<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN><br><SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">Function</SPAN> IsIndependence(dtTest <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Date</SPAN>) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN> <SPAN style="color:#007F00">'Jul-04</SPAN><br>    IsIndependence = <SPAN style="color:#00007F">CBool</SPAN>(Month(dtTest) = 7 And Day(dtTest) = 4)<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN><br><SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">Function</SPAN> IsVeterans(dtTest <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Date</SPAN>) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN> <SPAN style="color:#007F00">'Nov-11</SPAN><br>    IsVeterans = <SPAN style="color:#00007F">CBool</SPAN>(Month(dtTest) = 11 And Day(dtTest) = 11)<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN><br><SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">Function</SPAN> IsChristmasEve(dtTest <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Date</SPAN>) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN> <SPAN style="color:#007F00">'Dec-24</SPAN><br>    IsChristmasEve = <SPAN style="color:#00007F">CBool</SPAN>(Month(dtTest) = 12 And Day(dtTest) = 24)<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN><br><SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">Function</SPAN> IsChristmas(dtTest <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Date</SPAN>) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN> <SPAN style="color:#007F00">'Dec-25</SPAN><br>    IsChristmas = <SPAN style="color:#00007F">CBool</SPAN>(Month(dtTest) = 12 And Day(dtTest) = 25)<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN><br><br><br><SPAN style="color:#007F00">'////////////////////////////////////////////////////////////////////</SPAN><br><SPAN style="color:#007F00">'FLOATING HOLIDAYS</SPAN><br><SPAN style="color:#007F00">'////////////////////////////////////////////////////////////////////</SPAN><br><br><SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">Function</SPAN> IsEaster(dtTest <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Date</SPAN>) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN><br><SPAN style="color:#007F00">'http://www.cs.rutgers.edu/pub/soc.religion.christian/faq/easter-date</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> d <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>, y <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN><br>    y = Year(dtTest)<br>    d = (((255 - 11 * (y Mod 19)) - 21) Mod 30) + 21<br>    IsEaster = <SPAN style="color:#00007F">CBool</SPAN>(DateSerial(y, 3, 1) + d + (d > 48) + 6 - ((y + y \ 4 + d + (d > 48) + 1) Mod 7) = dtTest)<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN><br><SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">Function</SPAN> IsPresidents(dtTest <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Date</SPAN>) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN> <SPAN style="color:#007F00">'Third Monday in February</SPAN><br>    IsPresidents = <SPAN style="color:#00007F">CBool</SPAN>(Month(dtTest) = 2 And (Day(dtTest) > 15 <SPAN style="color:#00007F">Or</SPAN> Day(dtTest) < 21))<br>    <SPAN style="color:#00007F">If</SPAN> Month(dtTest) <> 2 And <SPAN style="color:#00007F">CBool</SPAN>(Day(dtTest) >= 15 <SPAN style="color:#00007F">Or</SPAN> Day(dtTest) < 21) And GetWeekdayNumber(dtTest) = 1 <SPAN style="color:#00007F">Then</SPAN><br>        IsPresidents = <SPAN style="color:#00007F">CBool</SPAN>(NthXDay(3, vbMonday, dtTest) = Day(dtTest))<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN><br><SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">Function</SPAN> IsMemorial(dtTest <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Date</SPAN>) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN> <SPAN style="color:#007F00">'Last Monday in May</SPAN><br>    <SPAN style="color:#00007F">If</SPAN> Month(dtTest) = 5 And Day(dtTest) >= 25 And GetWeekdayNumber(dtTest) = 1 <SPAN style="color:#00007F">Then</SPAN><br>        IsMemorial = <SPAN style="color:#00007F">CBool</SPAN>(Day(LastXDay(dtTest, vbMonday)) = Day(dtTest))<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN><br><SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">Function</SPAN> IsMartinLutherKing(dtTest <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Date</SPAN>) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN> <SPAN style="color:#007F00">'Third Monday in January</SPAN><br>    <SPAN style="color:#00007F">If</SPAN> Month(dtTest) = 1 And <SPAN style="color:#00007F">CBool</SPAN>(Day(dtTest) < 15 <SPAN style="color:#00007F">Or</SPAN> Day(dtTest) > 21) And GetWeekdayNumber(dtTest) <> 1 <SPAN style="color:#00007F">Then</SPAN><br>        IsMartinLutherKing = <SPAN style="color:#00007F">CBool</SPAN>(NthXDay(3, vbMonday, dtTest) = Day(dtTest))<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN><br><SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">Function</SPAN> IsLabor(dtTest <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Date</SPAN>) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN> <SPAN style="color:#007F00">'First Monday in September</SPAN><br>    <SPAN style="color:#00007F">If</SPAN> Month(dtTest) = 9 And Day(dtTest) < 7 And GetWeekdayNumber(dtTest) <> 1 <SPAN style="color:#00007F">Then</SPAN><br>        IsLabor = <SPAN style="color:#00007F">CBool</SPAN>(NthXDay(1, vbMonday, dtTest) = Day(dtTest))<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN><br><SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">Function</SPAN> IsColumbus(dtTest <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Date</SPAN>) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN> <SPAN style="color:#007F00">'Second Monday in October</SPAN><br>    <SPAN style="color:#00007F">If</SPAN> Month(dtTest) = 10 And <SPAN style="color:#00007F">CBool</SPAN>(Day(dtTest) > 8 <SPAN style="color:#00007F">Or</SPAN> Day(dtTest) < 14) And GetWeekdayNumber(dtTest) = 1 <SPAN style="color:#00007F">Then</SPAN><br>        IsColumbus = <SPAN style="color:#00007F">CBool</SPAN>(NthXDay(2, vbMonday, dtTest) = Day(dtTest))<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN><br><SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">Function</SPAN> IsThanksgiving(dtTest <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Date</SPAN>) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN> <SPAN style="color:#007F00">'Last Thursday in November</SPAN><br>    IsThanksgiving = <SPAN style="color:#00007F">CBool</SPAN>(dtTest = DateSerial(Year(dtTest), 11, 29 - Weekday(DateSerial(Year(dtTest), 11, 1), vbFriday)))<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN><br><br><br><SPAN style="color:#007F00">'////////////////////////////////////////////////////////////////////</SPAN><br><SPAN style="color:#007F00">'ADDITIONALS</SPAN><br><SPAN style="color:#007F00">'////////////////////////////////////////////////////////////////////</SPAN><br><br><SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">Function</SPAN> GetWeekdayNumber(dtTest <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Date</SPAN>) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN><br>    GetWeekdayNumber = Weekday(dtTest, vbMonday) Mod 7<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN><br><SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">Function</SPAN> LastXDay(dtD <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Date</SPAN>, DayConst <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Date</SPAN><br>    LastXDay = DateSerial(Year(dtD), Month(dtD) + 1, (-Weekday(DateSerial(Year(dtD), Month(dtD) + 1, 0)) + DayConst - 7) Mod 7)<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN><br><SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">Function</SPAN> NthXDay(N <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>, d <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>, dtD <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Date</SPAN>) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN><br>    NthXDay = (7 - Weekday(DateSerial(Year(dtD), Month(dtD), 1)) + d) Mod 7 + 1 + (N - 1) * 7<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN><br><SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">Function</SPAN> NDow(y <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>, M <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>, N <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>, DOW <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Date</SPAN><br>    NDow = DateSerial(y, M, (8 - Weekday(DateSerial(y, M, 1), (DOW + 1) Mod 8)) + ((N - 1) * 7))<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN><br><SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">Function</SPAN> DOWsInMonth(Yr <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>, M <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>, DOW <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN><br>    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> EndFunction<br>    <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>, Lim <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN><br>    Lim = Day(DateSerial(Yr, M + 1, 0))<br>    DOWsInMonth = 0<br>    <SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> Lim<br>        <SPAN style="color:#00007F">If</SPAN> Weekday(DateSerial(Yr, M, i)) = DOW <SPAN style="color:#00007F">Then</SPAN><br>            DOWsInMonth = DOWsInMonth + 1<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> i<br>    <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Function</SPAN><br>EndFunction:<br>    DOWsInMonth = 0<br>End <SPAN style="color:#00007F">Function</SPAN><br></FONT>


----------



## barry houdini (May 9, 2008)

Greg Truby said:


> FWIW, here are the US holiday formulae, running off of New Year's as the starting point:


 
Hello Greg,

I can't really resist.....

Here's a shorter version for Thanksgiving, assuming 1st Jan in B1

=EDATE(B1,10)+28-WEEKDAY(EDATE(B1,10)-5)

You can calculate  most of the others in a similar way


----------



## njimack (May 9, 2008)

A combination of formulae and UDF (courtesy of Chip Pearson) for UK Bank Holidays...S&P Timetable 2008 05.xlsABCD1BankHolidays-200801/01/0823NewYrsDay01-01-084GoodFriday21-03-085EasterMonday24-03-086EarlyMayBankHoliday05-05-087SpringBankHoliday26-05-088SummerBankHoliday25-08-089XmasDay25-12-0810BoxingDay26-12-08Holidays


```
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

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
Next I
Exit Function
EndFunction:
DOWsInMonth = 0
End Function
```


----------



## Greg Truby (May 9, 2008)

UK banks celebrate _Cinco de Mayo?_ ¡Vaya que sorpresa, jamás imaginé!


----------



## barry houdini (May 9, 2008)

I never saw the point of using DOWsInMonth function here, to get the last Monday in May with a formula I'd just calculate the 1st Monday in June and subtract 7....given that logic rather than

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

why not just

=NDow(Year,6,1,2)-7


----------



## litrelord (May 13, 2008)

Zack,

that's a pretty nifty easter calculation. I've been using the following in my VBA which I lifted straight from crystal reports:


```
Option Explicit

Function cdEasterDate(y As Long) As Date

' EASTER DATE CALCULATION FOR YEARS 1583 TO 4099

' y is a 4 digit year 1583 to 4099
' d returns the day of the month of Easter
' m returns the month of Easter

' Easter Sunday is the Sunday following the Paschal Full Moon
' (PFM) date for the year

' This algorithm is an arithmetic interpretation of the 3 step
' Easter Dating Method developed by Ron Mallen 1985, as a vast
' improvement on the method described in the Common Prayer Book

' Because this algorithm is a direct translation of the
' official tables, it can be easily proved to be 100% correct

' This algorithm derives values by sequential inter-dependent
' calculations, so ... DO NOT MODIFY THE ORDER OF CALCULATIONS!

' The \ operator may be unfamiliar - it means integer division
' for example, 30 \ 7 = 4 (the remainder is ignored)

' All variables are integer data types

' It's free!  Please do not modify code or comments!
' ==========================================================

    Dim FirstDig, Remain19, temp    'intermediate results
    Dim tA, tB, tC, tD, tE          'table A to E results
    Dim m, d

    FirstDig = y \ 100              'first 2 digits of year
    Remain19 = y Mod 19             'remainder of year / 19

    'calculate PFM date
    temp = (FirstDig - 15) \ 2 + 202 - 11 * Remain19
    If FirstDig > 26 Then temp = temp - 1
    If FirstDig > 38 Then temp = temp - 1
   
    'this next line uses underscore (_) line continuation characters
    'in older versions of VB you may need to revert to one long line
    If ((FirstDig = 21) Or (FirstDig = 24) Or (FirstDig = 25) _
        Or (FirstDig = 33) Or (FirstDig = 36) Or (FirstDig = 37)) _
        Then temp = temp - 1
   
    temp = temp Mod 30

    tA = temp + 21
    If temp = 29 Then tA = tA - 1
    If (temp = 28 And Remain19 > 10) Then tA = tA - 1
        
    'find the next Sunday
    tB = (tA - 19) Mod 7
    
    tC = (40 - FirstDig) Mod 4
    If tC = 3 Then tC = tC + 1
    If tC > 1 Then tC = tC + 1
        
    temp = y Mod 100
    tD = (temp + temp \ 4) Mod 7
    
    tE = ((20 - tB - tC - tD) Mod 7) + 1
    d = tA + tE

    'return the date
    If d > 31 Then
        d = d - 31
        m = 4
    Else
        m = 3
    End If

    cdEasterDate = DateSerial(y, m, d)

End Function
```

Not nearly as neat but thought I'd show it here in case it's of any interest.

Regards

Nick


----------



## Zack Barresse (May 13, 2008)

litrelord said:


> Zack,
> 
> that's a pretty nifty easter calculation.


Thanks Nick.  It's an adaptation from Chip Pearson's function, so I can't really take any credit for coming up with it, I just messed with it a bit.  I like things compact.


----------



## cummingsea (May 13, 2008)

Here's my version of U.S. Holidays
Excel WorkbookBCDE31/1/20084200856Holiday Calculations78HolidayDescriptionDateWeekday9New Year's Day1st Day in JanuaryJanuary 1, 2008Tuesday10Martin Luther King Jr. Day3rd Monday in JanuaryJanuary 21, 2008Monday11Presidents' Day3rd Monday in FebruaryFebruary 18, 2008Monday12Memorial DayLast Monday in MayMay 26, 2008Monday13Independence Day4th Day of JulyJuly 4, 2008Friday14Labor Day1st Monday in SeptemberSeptember 1, 2008Monday15Columbus Day2nd Monday in OctoberOctober 13, 2008Monday16Veterans Day11th Day of NovemberNovember 11, 2008Tuesday17Thanksgiving Day4thThursday in NovemberNovember 27, 2008Thursday18Christmas Day25th Day of DecemberDecember 25, 2008Thursday19Armed Forces DayThird Saturday In MayMay 17, 2008Saturday20Flag DayJune 14June 14, 2008Saturday21Lincoln's BirthdayFebruary 12February 12, 2008Tuesday22Valentine's DayFebruary 14February 14, 2008Thursday23Saint Patrick's DayMarch 17March 17, 2008Monday24Mother's DaySecond Sunday In MayMay 11, 2008Sunday25Father's DayThird Sunday In JuneJune 15, 2008Sunday26HolloweenMarch 23, 2008SundayHolidays


----------



## cummingsea (May 14, 2008)

B26 Is Easter, Not Holloween.


----------



## KitLam (Jun 16, 2010)

Thank You for adding that, it is exactly what I was looking for!


----------

