VBA计算周末天数

我正在寻找方法计算两个日期之间周末（周六和周日）的天数使用VBA。

我已经在网上搜索过，但都显示了如何计算工作日（有些使用 DateDiff，有些使用 Networkdays），但没有周末的日子，而且我已经成功地在工作日执行此操作。

例子 ：

从日期 3/10/2015 到 9/10/2015，应该返回 2 天（星期六和星期日，而不是 5 天（星期一、星期二、星期三、星期四、星期五）。

>>

我尝试根据@R3uK风格更改代码，但答案很“奇怪”，我不明白为什么结果会这样。这是代码：

Sub DateWeekDiff()
Sheets("Duplicate Removed").Activate
Dim Date1 As Date, Date2 As Date, StartDate As Date, EndDate As Date
Dim WeekendDays As Long, CountWeekendDays As Long, i As Long
Dim lrow As Long
Dim PRow As Long
Dim CurrentSheet As Worksheet
Set CurrentSheet = Excel.ActiveSheet
FRow = CurrentSheet.UsedRange.Cells(1).Row
lrow = CurrentSheet.UsedRange.Rows(CurrentSheet.UsedRange.Rows.count).Row
WeekendDays = 0

For PRow = lrow To 2 Step -1
'If CurrentSheet.Cells(PRow, "AD").Value <> "" And CurrentSheet.Cells(PRow, "T").Value <> "" Then _
'    CurrentSheet.Cells(PRow, "AP").Value = Abs(DateDiff("d", (CurrentSheet.Cells(PRow, "AD").Value), (CurrentSheet.Cells(PRow, "T").Value)))

For i = 0 To DateDiff("d", CurrentSheet.Cells(PRow, "AD").Value, CurrentSheet.Cells(PRow, "T").Value)
    Select Case Weekday(DateAdd("d", i, CurrentSheet.Cells(PRow, "AD").Value))
        Case 1, 7
            WeekendDays = WeekendDays + 1
    End Select
Next i
    CountWeekendDays = WeekendDays
    CurrentSheet.Cells(PRow, "AL").Value = CountWeekendDays
Next PRow
End Sub

结果变为（例如）AD = 26/1/2015 5:00:00 PM 和 T = 13/1/2015 8:05:00 AM 等于 AL = 807878。 循环也很慢（有一段时间没有响应）。

我的计算周末天数的函数版本：

Public Function CountWeekendDays(Date1 As Date, Date2 As Date) As Long
    Dim weekDifference As Integer
    Dim weekday1 As Byte
    Dim weekday2 As Byte
    '------------------------------------------------------------------

    weekDifference = VBA.DateDiff("w", Date1, Date2)
    weekday1 = VBA.Weekday(Date1, vbMonday)
    weekday2 = VBA.Weekday(Date2, vbMonday)

    CountWeekendDays2 = VBA.Abs(VBA.DateDiff("w", Date1, Date2) * 2)

    If Date1 < Date2 Then
        CountWeekendDays2 = CountWeekendDays2 + VBA.IIf(weekday1 < 6, 2, 8 - weekday1) + _
                                                VBA.IIf(weekday2 < 6, 0, weekday2 - 5)
        If weekday2 >= weekday1 Then CountWeekendDays2 = CountWeekendDays2 - 2
    Else
        CountWeekendDays2 = CountWeekendDays2 + VBA.IIf(weekday2 < 6, 2, 8 - weekday2) + _
                                                VBA.IIf(weekday1 < 6, 0, weekday1 - 5)
        If weekday1 >= weekday2 Then CountWeekendDays2 = CountWeekendDays2 - 2
    End If

End Function

该函数仅使用算术运算，因此它比使用循环的函数快得多。