Networkdays function in Access

[oliviar]

Hi guys,
Right now I have a function in Excel:

networkdays(datecolumn1,datecolumn2)+1

and I want to now put it into Access.

It doesn't appear receptive to networkdays as a function though.

What do you guys use?

 

[SydneyGeek]

A custom function is what you need...

I have used both of the variants on this page before.

Denis

 

[Kreszch68]

Or you simply set reference to the Microsoft Excel 12.0 Object Library to use your native Excel functions in Access

 

[oliviar]

Well I'd like to try both, but I'm very uncertain what you mean Kreszch68. How do you do what you're suggesting? I've never heard of it.

 

[oliviar]

Okay I'm about to scream!!! I just don't know what I'm doing.

Nothing I am finding works. They all presume that my holidays are in a single column, but they aren't. They are in a separate table, yes, but over 6 columns.

In excel I run the function with start date, end date, and "holidays" taking up 6 rows in the middle.

So its: Number of Days=networkdays(startdate,enddate,b2:g2)

I need a way to create a NumberofDays calculated column in my query, using start date, end date and the 6 columns in the middle?

I am happy to use any method, but if you can help me I will need a lot of detail in the instructions.

 

[Kreszch68]

oliviar, got to the Tools menu (when you are in the module window, Alt-F11), click references and scroll to the Microsoft Excel 12.0 Object Library. That's it.

 

[SydneyGeek]

Try the Excel reference option first, but why are the holidays in 6 different fields?

You should only need 2 or 3; one for the actual date, the others for descriptions. Example:

Excel Workbook
	A	B	C
1	StateOrNational	Description	HolidayDate
2	National	New Year	1/01/2011
3	National	Australia Day	26/01/2011
4	National	Good Friday	22/04/2011
5	National	Easter Sunday	24/04/2011
6	National	Easter Holiday	25/04/2011
7	National	ANZAC Day holiday	26/04/2011
8	NSW	Queens Birthday	4/06/2011
Sheet1

Denis

 

[oliviar]

Hi Denis,
I actually need dates to be able to exist in ALL 6 columns. Some do have dates in them, and some don't. But the number of columns is non-negotiable. Some dates only apply to certain people, some people have up to 6 incidents of exceptions, and some people have none. Hence the 6 columns have to exist.

As for using the excel reference library. It sounds good in theory, but even after I have set it up, I've told Access it can use them... when I go to put it into my query, I do
totaldays: networkdays(start,end,hol1:hol6)
it says 'undefined function'

So I am doing something wrong. I am missing something.

 

[oliviar]

Okay, just to throw a spanner in there.... It turns out I need to calculate a fixed number of business days from the starting date, and I won't be provided the end date, the end date is what I need to generate in the query.

In my query, the columns will therefore be:

StartDate, DaystoCompletion, Holiday1, Hol2, Hol3, Hol4, Hol5, Hol6, SOLUTION

This is the 'workday' function in excel.

If I had to guess, I think that this code from that link is what I would use, but I don't know HOW to use it. I mean, if I run it, where does my result end up? How does it know what columns in my query to use?

Public Function dhAddWorkDaysA(lngDays As Long, _
Optional dtmDate As Date = 0, _
Optional adtmDates As Variant) As Date
    ' Add the specified number of work days to the
    ' specified date.
    
    ' Modified from code in
    ' "Visual Basic Language Developer's Handbook"
    ' by Ken Getz and Mike Gilbert
    ' Copyright 2000; Sybex, Inc. All rights reserved.
    
    ' In:
    '   lngDays:
    '       Number of work days to add to the start date.
    '   dtmDate:
    '       date on which to start looking.
    '       Use the current date, if none was specified.
    '   adtmDates (Optional):
    '       Array containing holiday dates. Can also be a single
    '       date value, if that's what you want.
    ' Out:
    '   Return Value:
    '       The date of the working day lngDays from the start, taking
    '       into account weekends and holidays.
    ' Example:
    '   dhAddWorkDaysA(10, #2/9/2000#, Array(#2/16/2000#, #2/17/2000#))
    '   returns #2/25/2000#, which is the date 10 work days
    '   after 2/9/2000, if you treat 2/16 and 2/17 as holidays
    '   (just made-up holidays, for example purposes only).
    
    ' Did the caller pass in a date? If not, use
    ' the current date.
    Dim lngCount As Long
    Dim dtmTemp As Date
    
    If dtmDate = 0 Then
        dtmDate = Date
    End If
    
    dtmTemp = dtmDate
    For lngCount = 1 To lngDays
        dtmTemp = dhNextWorkdayA(dtmTemp, adtmDates)
    Next lngCount
    dhAddWorkDaysA = dtmTemp
End Function

Public Function dhNextWorkdayA( _
Optional dtmDate As Date = 0, _
Optional adtmDates As Variant = Empty) As Date