CODEWITHSUNDEEP
vbams-access
Mateyobi
Mateyobi

Reputation: 123

Access Workday Function to match Excel?

I need an access function whose output will match the excel workbook function 100% of the time.

This problem has plagued me for years. There is a lot of code on the internet, but the output does not match excel.

The below code is what finally worked for me.

Upvotes: 0

Views: 3746

Answers (3)

WingedEggplant
WingedEggplant

Reputation: 1

I have never posted here and never expected to, but since I was googling this today, and playing around with it, I decided to share my easy answer. I found you can do this pretty easily by adding the Microsoft Excel 16.0 Object Library to your references:

Function GetWorkDays(startDate As Date, days As Long) As Date


Dim Holidays(3) As Variant
Holidays(0) = "10/14/19"
Holidays(1) = "11/10/19"
Holidays(2) = "11/28/19"
Holidays(3) = "12/25/19"

GetWorkDays = WorksheetFunction.WorkDay(startDate, days, Holidays)

End Function

Upvotes: 0

user6432984
user6432984

Reputation:

If you want an to be sure that you have an exact match then you can use the actual function.

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

Alternatively this should work:

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

Upvotes: 4

Mateyobi
Mateyobi

Reputation: 123

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

Upvotes: 2

Related Questions