Date Generation

[clauses]

I am attempting to write a module that will generate all the worling days between two dates. This list of dates should exclude weekends and holidays. The NetWorkDays and WorkDays functions available in Access could accomplish this but is there any similar functions in VBA, and how would they be used.

 

[Ken Grubb]

Weekdays are easy enough. See the Weekday Function in Help.

Holidays are a bit trickier because there is no universally accepted standard. For example, National Shoe Day is celebrated the 3rd of April in West Elbonia, but the 6th of May in Northern Elbonia. To my knowledge no one else recognizes National Shoe Day. [That is a shame, but the topic of another thread another day.]

Also, take a look at this thread including the link to Dev's site.

HTH,

Ken Grubb
Burlington, NC, USA

 

[John liem]

I am trying to use this function, but it doesn't work .... it errors out on this statement "dtmTemp = dhNextWorkdayA(dtmTemp, adtmDates)". Can someone tell me why? John

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