Jonmo1
MrExcel MVP
- Joined
- Oct 12, 2006
- Messages
- 44,061
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
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
Code:
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
Last edited:
