NetWorkDays Function - Microsoft Community

hi all,

i trying write function returns number of workdays between today , due date job.

this have far:

function workdays(due as date)
    dim a integer
    workdays = 0: = 0
    until date + > due
        if weekday(date + a, vbmonday) < 6 then workdays = workdays + 1
        a = + 1
    loop
end function

this works, discounting saturdays , sundays, can't discount holidays.

i have holiday dates (for next 20 years) listed in table. want this:

function workdays(due as date)
    dim a integer
    workdays = 0: = 0
    until date + > due
        if weekday(date + a, vbmonday) < 6 and date + not occur in holiday dates table then workdays = workdays + 1
        a = + 1
    loop
end function

i'm happy loop through dates in holiday dates table, testing each one, have no idea how syntax in access. possible?

(yes, syntax can verb... think)

regards - dave.

here function use; written arvin meyer:

public function workingdays(startdate date, enddate date) long
    '....................................................................
    ' name: workingdays
    ' inputs: startdate date
    ' enddate date
    ' returns: long
    ' author: arvin meyer
    ' date: may 5,2002
    ' modified slightly
    ' comment: accepts 2 dates , returns number of weekdays between them
    ' note function has been modified account holidays.
    ' requires table named tblholidays field named holidaydate.
    '....................................................................

    dim lngcount long
    dim dtmcurr date
    dim rst dao.recordset
    dim dbs dao.database

    on error goto exithandler

    set dbs = currentdb
    set rst = dbs.openrecordset("tblholidays", dbopensnapshot)

    ' use following line include startdate
    ' dtmcurr = startdate
    ' use following line exclude startdate
    dtmcurr = startdate + 1

    lngcount = 0

    while dtmcurr <= enddate
        if weekday(dtmcurr, vbmonday) < 6 then
            rst.findfirst "[holidaydate] = #" & format(dtmcurr, "mm/dd/yyyy") & "#"
            if rst.nomatch then
                lngcount = lngcount + 1
            end if
        end if
        dtmcurr = dtmcurr + 1
    loop

    workingdays = lngcount

exithandler:
    on error resume next
    rst.close
    set rst = nothing
    set dbs = nothing
end function

use workingdays(date(), due) number of workdays today due date.

Office / Access / Windows 10 / Office 365 for business