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?
Printable View
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?
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.
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
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.
For more of this stuff, click the vB Code link above the reply page.Code:If User = AnyOne Then
MsgBox "Hello World"
End If
Excellent that means I should be able to clearly display my problem area and get loadsa help ?!
------------------------------------------------------------
Code: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