Working Days

[Richyrich]

I am about to write a procedure that cycles through working days only. I am going to use a table to hold bank holidays, however I was wondering if anyone had approached this problem differently?

[Buzby]

Since bank holidays vary from country to country I don't think you'll find an universal way to do this except if you do exactly what you say and put the dates in a table.
I had to do a similar thing once to work out working days between two dates. As far as I remember I used DateDiff to get the number of days between the two days, then did a loop that checked each date if it was a Saturday or Sunday ( by using the WeekDay([date]) function - it returns 7 for Saturday, 1 for Sunda) and also checked each date against a table of known bank holidays.

[Richyrich]

I have written a function to work out negative and positive working days (Based on three arguments a day (-ve/+ve); A month and finally a year.

The function works for positive days ie working day 3,4 etc
but negative days are always one working day short, AARRRGG so close I think I am overlooking something.

Hope the identation remains when I post this, and I have made myself clear.

------------------------------------------------------------

Public Function Working_Date(intMonth As Integer, intYear As Integer, intDay As Integer) As Date
Dim dtmBegin As Date, intCount As Integer

'# Note working day function checks if the date is a weekend
'# or within the bank holidays table.

dtmBegin = Start_Date(intMonth, intYear)

Do While Not (Is_WorkDay(dtmBegin))
'# This will take us to our first working day
'# for the month in question
dtmBegin = DateAdd("d", 1, dtmBegin)
Loop

If intDay < 0 Then

'#######################
' Error is here the date is always one working day short!
'#######################
' # Working Day is negative.
intCount = -1
Do Until intDay = intCount
'take off a day
dtmBegin = DateAdd("d", -1, dtmBegin)

If Is_WorkDay(dtmBegin) Then
'if working day then reduce count
intCount = intCount - 1
End If
Loop
Else
'# Working Day is positive.
'# Tested works!!
intCount = 1
Do Until intDay = intCount
'add a day
dtmBegin = DateAdd("d", 1, dtmBegin)

If Is_WorkDay(dtmBegin) Then
'if working day then increase count
intCount = intCount + 1
End If
Loop
End If


Working_Date = dtmBegin

[Frans C]

Richyrich, if you want the indentation of your code to show properly, you can use the code tags.
Just put the word code between square brackets to start the code, and /code also between brackets to end the code segment.

    If User = AnyOne Then
        MsgBox "Hello World"
    End If

For more of this stuff, click the vB Code link above the reply page.

[Richyrich]

Excellent that means I should be able to clearly display my problem area and get loadsa help ?!
------------------------------------------------------------

Public Function Working_Date(intMonth As Integer, intYear As Integer, intDay As Integer) As Date
Dim dtmBegin As Date, intCount As Integer

'# Note working day function checks if the date is a weekend
'# or within the bank holidays table.

dtmBegin = Start_Date(intMonth, intYear)

Do While Not (Is_WorkDay(dtmBegin))
'# This will take us to our first working day
'# for the month in question
    dtmBegin = DateAdd("d", 1, dtmBegin)
Loop

If intDay < 0 Then

'#######################
' Error is here the date is always one working day short!
'#######################
' # Working Day is negative.
    intCount = -1
    Do Until intDay = intCount
        'take off a day
        dtmBegin = DateAdd("d", -1, dtmBegin)
        
        If Is_WorkDay(dtmBegin) Then
            'if working day then reduce count
            intCount = intCount - 1
        End If
    Loop
Else
'# Working Day is positive.
'# Tested works!!
    intCount = 1
    Do Until intDay = intCount
        'add a day
        dtmBegin = DateAdd("d", 1, dtmBegin)
        
        If Is_WorkDay(dtmBegin) Then
                'if working day then increase count
            intCount = intCount + 1
        End If
    Loop
End If


Working_Date = dtmBegin

End Function