gistfile1.txt

Sub GenerateCalendarDaysCSV()
    Dim ws As Worksheet
    Dim startDate As Date
    Dim currentDate As Date
    Dim i As Long
    Dim totalDays As Long
    Dim holidays As Collection
    Dim holidayWeekFridays As Collection
    Dim isIncluded As Boolean
    Dim weekStart As Date
    Dim weekEnd As Date
    Dim isHolidayWeek As Boolean
    
    ' Set up the worksheet
    Set ws = ThisWorkbook.Sheets.Add
    ws.Name = "CalendarDays"
    ws.Cells(1, 1).Value = "Day"
    ws.Cells(1, 2).Value = "Included"
    
    ' Initialize variables
    startDate = DateSerial(2025, 6, 4) ' June 4, 2025
    totalDays = 1671 ' Total days from June 4, 2025, to January 1, 2030
    
    ' Define holidays (weekday federal holidays, day after Thanksgiving, Lincoln’s Birthday)
    Set holidays = New Collection
    ' 2025
    holidays.Add DateSerial(2025, 6, 19) ' Juneteenth
    holidays.Add DateSerial(2025, 7, 4) ' Independence Day
    holidays.Add DateSerial(2025, 9, 1) ' Labor Day
    holidays.Add DateSerial(2025, 10, 13) ' Columbus Day
    holidays.Add DateSerial(2025, 11, 11) ' Veterans Day
    holidays.Add DateSerial(2025, 11, 27) ' Thanksgiving
    holidays.Add DateSerial(2025, 11, 28) ' Day after Thanksgiving
    holidays.Add DateSerial(2025, 12, 25) ' Christmas
    ' 2026
    holidays.Add DateSerial(2026, 1, 1) ' New Year’s Day
    holidays.Add DateSerial(2026, 1, 19) ' MLK Day
    holidays.Add DateSerial(2026, 2, 12) ' Lincoln’s Birthday
    holidays.Add DateSerial(2026, 2, 16) ' Washington’s Birthday
    holidays.Add DateSerial(2026, 5, 25) ' Memorial Day
    holidays.Add DateSerial(2026, 6, 19) ' Juneteenth
    holidays.Add DateSerial(2026, 7, 5) ' Independence Day observed
    holidays.Add DateSerial(2026, 9, 7) ' Labor Day
    holidays.Add DateSerial(2026, 10, 12) ' Columbus Day
    holidays.Add DateSerial(2026, 11, 11) ' Veterans Day
    holidays.Add DateSerial(2026, 11, 26) ' Thanksgiving
    holidays.Add DateSerial(2026, 11, 27) ' Day after Thanksgiving
    holidays.Add DateSerial(2026, 12, 25) ' Christmas
    ' 2027
    holidays.Add DateSerial(2027, 1, 1) ' New Year’s Day
    holidays.Add DateSerial(2027, 1, 18) ' MLK Day
    holidays.Add DateSerial(2027, 2, 12) ' Lincoln’s Birthday
    holidays.Add DateSerial(2027, 2, 15) ' Washington’s Birthday
    holidays.Add DateSerial(2027, 5, 31) ' Memorial Day
    holidays.Add DateSerial(2027, 7, 5) ' Independence Day observed
    holidays.Add DateSerial(2027, 9, 6) ' Labor Day
    holidays.Add DateSerial(2027, 10, 11) ' Columbus Day
    holidays.Add DateSerial(2027, 11, 11) ' Veterans Day
    holidays.Add DateSerial(2027, 11, 25) ' Thanksgiving
    holidays.Add DateSerial(2027, 11, 26) ' Day after Thanksgiving
    ' 2028
    holidays.Add DateSerial(2028, 1, 17) ' MLK Day
    holidays.Add DateSerial(2028, 2, 21) ' Washington’s Birthday
    holidays.Add DateSerial(2028, 5, 29) ' Memorial Day
    holidays.Add DateSerial(2028, 6, 19) ' Juneteenth
    holidays.Add DateSerial(2028, 7, 4) ' Independence Day
    holidays.Add DateSerial(2028, 9, 4) ' Labor Day
    holidays.Add DateSerial(2028, 10, 9) ' Columbus Day
    holidays.Add DateSerial(2028, 11, 23) ' Thanksgiving
    holidays.Add DateSerial(2028, 11, 24) ' Day after Thanksgiving
    holidays.Add DateSerial(2028, 12, 25) ' Christmas
    ' 2029
    holidays.Add DateSerial(2029, 1, 1) ' New Year’s Day
    holidays.Add DateSerial(2029, 1, 15) ' MLK Day
    holidays.Add DateSerial(2029, 2, 12) ' Lincoln’s Birthday
    holidays.Add DateSerial(2029, 2, 19) ' Washington’s Birthday
    holidays.Add DateSerial(2029, 5, 28) ' Memorial Day
    holidays.Add DateSerial(2029, 6, 19) ' Juneteenth
    holidays.Add DateSerial(2029, 7, 4) ' Independence Day
    holidays.Add DateSerial(2029, 9, 3) ' Labor Day
    holidays.Add DateSerial(2029, 10, 8) ' Columbus Day
    holidays.Add DateSerial(2029, 11, 12) ' Veterans Day observed
    holidays.Add DateSerial(2029, 11, 22) ' Thanksgiving
    holidays.Add DateSerial(2029, 11, 23) ' Day after Thanksgiving
    holidays.Add DateSerial(2029, 12, 25) ' Christmas
    
    ' Define Fridays in holiday weeks (to retain)
    Set holidayWeekFridays = New Collection
    ' 2025
    holidayWeekFridays.Add DateSerial(2025, 6, 20)
    holidayWeekFridays.Add DateSerial(2025, 9, 5)
    holidayWeekFridays.Add DateSerial(2025, 10, 17)
    holidayWeekFridays.Add DateSerial(2025, 11, 14)
    holidayWeekFridays.Add DateSerial(2025, 12, 26)
    ' 2026
    holidayWeekFridays.Add DateSerial(2026, 1, 2)
    holidayWeekFridays.Add DateSerial(2026, 1, 23)
    holidayWeekFridays.Add DateSerial(2026, 2, 13)
    holidayWeekFridays.Add DateSerial(2026, 2, 20)
    holidayWeekFridays.Add DateSerial(2026, 5, 28)
    holidayWeekFridays.Add DateSerial(2026, 7, 9)
    holidayWeekFridays.Add DateSerial(2026, 9, 11)
    holidayWeekFridays.Add DateSerial(2026, 10, 16)
    holidayWeekFridays.Add DateSerial(2026, 11, 13)
    ' 2027
    holidayWeekFridays.Add DateSerial(2027, 1, 22)
    holidayWeekFridays.Add DateSerial(2027, 2, 19)
    holidayWeekFridays.Add DateSerial(2027, 6, 4)
    holidayWeekFridays.Add DateSerial(2027, 7, 9)
    holidayWeekFridays.Add DateSerial(2027, 9, 10)
    holidayWeekFridays.Add DateSerial(2027, 10, 15)
    holidayWeekFridays.Add DateSerial(2027, 11, 12)
    ' 2028
    holidayWeekFridays.Add DateSerial(2028, 1, 21)
    holidayWeekFridays.Add DateSerial(2028, 2, 11)
    holidayWeekFridays.Add DateSerial(2028, 2, 25)
    holidayWeekFridays.Add DateSerial(2028, 6, 2)
    holidayWeekFridays.Add DateSerial(2028, 6, 23)
    holidayWeekFridays.Add DateSerial(2028, 7, 7)
    holidayWeekFridays.Add DateSerial(2028, 9, 8)
    holidayWeekFridays.Add DateSerial(2028, 10, 13)
    holidayWeekFridays.Add DateSerial(2028, 12, 29)
    ' 2029
    holidayWeekFridays.Add DateSerial(2029, 1, 5)
    holidayWeekFridays.Add DateSerial(2029, 1, 19)
    holidayWeekFridays.Add DateSerial(2029, 2, 16)
    holidayWeekFridays.Add DateSerial(2029, 2, 23)
    holidayWeekFridays.Add DateSerial(2029, 6, 1)
    holidayWeekFridays.Add DateSerial(2029, 6, 22)
    holidayWeekFridays.Add DateSerial(2029, 7, 6)
    holidayWeekFridays.Add DateSerial(2029, 9, 7)
    holidayWeekFridays.Add DateSerial(2029, 10, 12)
    holidayWeekFridays.Add DateSerial(2029, 11, 16)
    holidayWeekFridays.Add DateSerial(2029, 12, 28)
    
    ' Iterate through each day
    For i = 1 To totalDays
        currentDate = startDate + (i - 1)
        ws.Cells(i + 1, 1).Value = i
        isIncluded = True
        
        ' Check if it’s a weekend (Saturday = 7, Sunday = 1)
        If Weekday(currentDate, vbMonday) >= 6 Then
            isIncluded = False
        Else
            ' Check if it’s a holiday
            For Each holiday In holidays
                If currentDate = holiday Then
                    isIncluded = False
                    Exit For
                End If
            Next holiday
            
            ' If it’s a Friday, check if it’s in a holiday week
            If Weekday(currentDate, vbMonday) = 5 Then
                isHolidayWeek = False
                ' Determine the week (Monday to Sunday)
                weekStart = currentDate - Weekday(currentDate, vbMonday) + 1
                weekEnd = weekStart + 6
                ' Check if any holiday falls in this week
                For Each holiday In holidays
                    If holiday >= weekStart And holiday <= weekEnd Then
                        isHolidayWeek = True
                        Exit For
                    End If
                Next holiday
                ' If not in a holiday week and not in holidayWeekFridays, exclude it
                If Not isHolidayWeek Then
                    isIncluded = False
                    For Each friday In holidayWeekFridays
                        If currentDate = friday Then
                            isIncluded = True
                            Exit For
                        End If
                    Next friday
                End If
            End If
        End If
        
        ' Write the date if included
        If isIncluded Then
            ws.Cells(i + 1, 2).Value = Format(currentDate, "yyyy-mm-dd")
        Else
            ws.Cells(i + 1, 2).Value = ""
        End If
    Next i
    
    ' Auto-fit columns for readability
    ws.Columns("A:B").AutoFit
    
    MsgBox "Calendar days generated. Save the worksheet as a CSV file.", vbInformation
End Sub