Dim holidaysArray(8) As Date

Private Sub btnCalculate_Click()

    ' loop the raw payroll data, calc hours for the week ending on
    ' and including Sunday

    Dim totalWeekHours As Double

    ' you need to use long because excel can have data rows that exceed the max integer value
    Dim lastRow As Long
    Dim sheetRow As Long

    ' list of holidays to check against during the weekly hours calc
    holidaysArray(0) = CDate("1/1/2025")
    holidaysArray(1) = CDate("2/18/2025")
    holidaysArray(2) = CDate("5/26/2025")
    holidaysArray(3) = CDate("7/4/2025")
    holidaysArray(4) = CDate("11/27/2025")
    holidaysArray(5) = CDate("11/28/2025")
    holidaysArray(6) = CDate("12/25/2025")
    holidaysArray(7) = CDate("12/26/2025")

    ' this gets the last row that contains data in the sheet
    lastRow = Sheet1.Cells(Sheet1.Rows.Count, "F").End(xlUp).Row

    ' loop through the data, sum hours for sunday, then add a blank row in the data
    ' for formatting
    totalWeekHours = 0
    sheetRow = 2
    Do Until sheetRow = lastRow + 1
        If Weekday(Sheet1.Cells(sheetRow, "G").Value) = vbSunday Then
            ' include Sundays hours
            totalWeekHours = totalWeekHours + Sheet1.Cells(sheetRow, "K").Value

            ' add the total to the sheet and insert a line after it
            Sheet1.Cells(sheetRow, "D").Value = totalWeekHours
            Sheet1.Range("A" + CStr(sheetRow + 1)).EntireRow.Insert

            ' add a row to the last row given it has been pushed down the sheet
            lastRow = lastRow + 1

            ' add to sheetrow to skip the new blank line added to the sheet
            sheetRow = sheetRow + 1

            totalWeekHours = 0  ' reset to calc the next week
        Else
            ' add the hours worked minus the break to the total hours for the current week being totalled
            totalWeekHours = totalWeekHours + Sheet1.Cells(sheetRow, "K").Value

            If IsHoliday(Sheet1.Cells(sheetRow, "G").Value) Then
                ' the date on this row matches a holiday, add a row below it, insert "Holiday" and add 8 hours
                Sheet1.Range("A" + CStr(sheetRow + 1)).EntireRow.Insert
                sheetRow = sheetRow + 1

                Sheet1.Cells(sheetRow, "G").Value = "Holiday"
                Sheet1.Cells(sheetRow, "K").Value = 8

                ' a blank row is required after a holiday as well
                Sheet1.Range("A" + CStr(sheetRow + 1)).EntireRow.Insert

                ' looks like the holiday hours are not included in the weeks total hours
                ' given the manual total in the example is 37.5 and not 45.5 on the last week
                'totalWeekHours = totalWeekHours + 8

                ' add a row to the last row given it has been pushed down the sheet
                lastRow = lastRow + 2
                sheetRow = sheetRow + 1  ' jump to the next line to calculate
            End If
        End If

        sheetRow = sheetRow + 1
    Loop

    ' check if there is still a total hours, if so write it to the sheet for the last week
    If totalWeekHours > 0 Then
        Sheet1.Cells(sheetRow - 1, "D").Value = totalWeekHours
    End If

End Sub

Private Function IsHoliday(dateToCheck As Date) As Boolean

    ' is the date in the holiday list?
    Dim holidayArrayIndex As Integer

    ' check the array for the existance of the date passed (probably better to do with a dictionary
    ' but with such a small list why add the overhead)
    For holidayArrayIndex = 0 To 8
        If dateToCheck = holidaysArray(holidayArrayIndex) Then
            ' the date passed is a holiday
            IsHoliday = True
            Exit Function
        End If
    Next holidayArrayIndex

    ' if it gets here then the date isn't a holiday
    IsHoliday = False

End Function