Retrieving and Populating Holidays for Any Year

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I would like to share the following code I developed for retrieving and
populating standard Holiday dates in the U.S. for any given year. It is
built around the general protocol where holidays falling on Saturdays are
usually taken off on the Friday before and Sunday holidays are taken off on
Mondays. The code assumes that Memorial day is the last Monday of May, Labor
Day is the first Monday in September and Thanksgiving is the last Thursday in
November. (The company I'm working for does not take the Friday after
Thanksgiving off).

Most of the code I've seen on other sites for this subject seem to depend
upon a manually-kept table of Holidays each year in order for their great
code to work. Several of the companies I've worked for have needed the
Holidays to be predetermined automatically. I'm sure this can be simplified
and improved upon, but I hope it helps.

I have seven fields in my frmHolidays named:
CurrNewYear
MemDay (Memorial Day)
IndDay (4th of July)
LabDay (Labor Day)
TDAy (Thanksgiving Day)
CDay (Christmas Day)
NextNewYear

These fields are populated upon my form's OnOpen event.


Private Sub Form_Open(Cancel As Integer)
If Weekday("1 / 1" & "/" & Format(Now(), "yyyy")) = 7 Then
CurrNewYear = DateAdd("d", -1, 1 / 1 & "/" & Format(Now(), "yyyy"))
ElseIf Weekday("1 / 1" & "/" & Format(Now(), "yyyy")) = 1 Then
CurrNewYear = DateAdd("d", 1, "1 / 1" & "/" & Format(Now(), "yyyy"))
Else: CurrNewYear = Format("1 / 1" & "/" & Format(Now(), "yyyy"))
End If

MemDay = IIf(DateAdd("d", 2 - Weekday("5/31"), ("5/31")) < "6/1",
DateAdd("d", 2 - Weekday("5/31"), ("5/31")), DateAdd("d", 2 -
Weekday("5/31"), ("5/31")) - 7)

If Weekday("7 / 4" & "/" & Format(Now(), "yyyy")) = 7 Then
IndDay = DateAdd("d", -1, "7 / 4" & "/" & Format(Now(), "yyyy"))
ElseIf Weekday("7 / 4" & "/" & Format(Now(), "yyyy")) = 1 Then
IndDay = DateAdd("d", 1, "7 / 4" & "/" & Format(Now(), "yyyy"))
Else: IndDay = Format("7 / 4" & "/" & Format(Now(), "yyyy"))
End If

LabDay = IIf(DateAdd("d", 2 - Weekday("9/1"), ("9/1")) < "8/31",
DateAdd("d", 2 - Weekday("9/1"), ("9/1")) + 7, DateAdd("d", 2 -
Weekday("9/1"), ("9/1")))

Tday = IIf(DateAdd("d", 5 - Weekday("11/30"), ("11/30")) < "12/1",
DateAdd("d", 5 - Weekday("11/30"), ("11/30")), DateAdd("d", 5 -
Weekday("11/30"), ("11/30")) - 7)

CDay = IIf(Weekday("12/25") = 1, "12/26/" & Format(Now(), "yyyy"),
IIf(Weekday("12/25") = 7, "12/24/" & Format(Now(), "yyyy"), "12/25/" &
Format(Now(), "yyyy")))

If Weekday("1 / 1" & "/" & Format(Now(), "yyyy") + 1) = 7 Then
NextNewYear = DateAdd("d", -1, "1 / 1" & "/" & Format(Now(), "yyyy") + 1)
ElseIf Weekday("1 / 1" & "/" & Format(Now(), "yyyy")) + 1 = 1 Then
NextNewYear = DateAdd("d", 1, "1 / 1" & "/" & Format(Now(), "yyyy") + 1)
Else: NextNewYear = Format("1 / 1" & "/" & Format(Now(), "yyyy") + 1)
End If

End Sub
 
I'd recommend changing how you refer to the dates. Rather than, say,
Weekday("9/1"), use

Weekday(DateSerial(Year(Date()), 9, 1))

Otherwise, you can run into problems if any of your users have their Short
Date format set to other than mm/dd/yyyy. (Yes, I realize that's the usual
format in the US, but there can be rebels!)
 
Thank you for some sound wisdom with the date usage. It makes a lot of sense.

Also, I discovered the last "ElseIf" line of code had a discrepancy which
causes the "NextNewYear" day to come up short by one day.
It should read:
ElseIf Weekday("1 / 1" & "/" & Format(Now(), "yyyy") + 1) = 1 Then
instead of:
ElseIf Weekday("1 / 1" & "/" & Format(Now(), "yyyy")) + 1 = 1 Then

Thanks again for the welcome input.
 
Back
Top