我需要一个VBA函数来计算两个日期之间的工作日 [英] I need a VBA function to count the working days between two dates

问题描述

我有这个:

Public Function WorkingDays(StartDate As Date, _
                        ByVal EndDate As Date, _
                        Optional ByVal Holidays As Range, _
                        Optional ByVal Workdays As Variant) As Long
Dim mpDays As Long
Dim mpHolidays As Variant
Dim mpWorkdays As Variant 
Dim i As Long
If Not Holidays Is Nothing Then mpHolidays = Application.Transpose(Holidays)
If IsMissing(Workdays) Then
    mpWorkdays = Array(2, 3, 4, 5, 6)
Else
    mpWorkdays = Workdays
End If
For i = StartDate To EndDate

    If Not IsError(Application.Match(Weekday(i), mpWorkdays, 0)) Then

        If IsError(Application.Match(i, mpHolidays, 0)) Then
            mpDays = mpDays + 1
        End If
    End If
Next i
WorkingDays = mpDays 
End Function

但是它非常慢，我在工作簿和我的工作表中有130K记录，都使用了此功能.该如何改善?

but it is very slow, I use this function in my Workbook and my sheet I've 130K records. How can it be improved?

推荐答案

您是否看到Excel具有NETWORKDAYS和WORKDAYS函数?

Have you seen that Excel has the NETWORKDAYS and the WORKDAYS functions?

与其循环地查看从StartDate到EndDate的日期非常慢，为什么不计算两个日期之间的工作日数，然后遍历假期以查看每个日期是否在StartDate和EndDate之间?

Instead of looping through the dates from StartDate to EndDate which is very slow, why not work out the number of week days between the two dates and then loop through the holidays to see if each date falls between StartDate and EndDate?

这篇关于我需要一个VBA函数来计算两个日期之间的工作日的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！