访问工作日函数以匹配Excel？

Question

我需要一个访问函数，其输出将在100％的时间内与excel工作簿函数匹配。

这个问题困扰了我多年。互联网上有很多代码，但输出与excel不匹配。

下面的代码是最后为我工作的代码。

Answer 1

Function workday(start_date As Date, days As Long) 

Dim count_days As Long 

days = days - 1 
count_days = 1 

Do Until days < 1 And Weekday(DateAdd("d", count_days, start_date)) <> 1 And Weekday(DateAdd("d", count_days, start_date)) <> 7 

    If Weekday(DateAdd("d", count_days, start_date)) = 1 Or Weekday(DateAdd("d", count_days, start_date)) = 7 Then 
     days = days 
     count_days = count_days + 1 
    Else 
     days = days - 1 
     count_days = count_days + 1 
    End If 
Loop 

workday = DateAdd("d", count_days, start_date) 

End Function 

Answer 2

如果你想确保你有完全匹配，那么你可以使用实际的功能。

Function Workday2(start_date, days, Optional Holiday As Variant) As Date 
    Static xlApp As Object 
    If xlApp Is Nothing Then Set xlApp = CreateObject("Excel.Application") 
    Workday2 = xlApp.WorksheetFunction.Workday(start_date, days, Holiday) 
End Function 

或者这应该工作：

Function Workday3(start_date, days As Long, Optional Holidays As Variant) As Date 
    Dim dHolidays As Object 
    Dim x As Long 
    Dim Holiday As Variant 
    Set dHolidays = CreateObject("System.Collections.ArrayList") 

    For Each Holiday In Holidays 
     If Not dHolidays.Contains(DateValue(Holiday)) Then dHolidays.Add DateValue(Holiday) 
    Next 

    Do Until x = days 
     start_date = start_date + 1 
     If Weekday(start_date) <> vbSaturday And Weekday(start_date) <> vbSunday And Not dHolidays.Contains(DateValue(start_date)) Then x = x + 1 
    Loop 

    Workday3 = start_date 

End Function