Calculate Business Days Through MS Access Events (VBA)

[lhooker]

I need to calculate the number of business days from dates inputted from a form. Does anyone have VBA routine that does this or know how to accomplish this ? Thanks ! ! !

 

[vbaInet]

Perform an Advanced Search on the forum. I believe it has been covered.

 

[lhooker]

I found the item, but how do you use "Function" ? Do you just paste the code command button (via "Event Procedure) ? I appreciate your help ! ! !

 

[vbaInet]

What does it say on the thread you found?

 

[lhooker]

vbaInet,

Below is the code . . .

Public Function BusinessDays(dteStartDate As Date, dteEndDate As Date) As Long

Dim lngYear As Long
Dim dteStart As Date, dteEnd As Date
Dim dteCurr As Date
Dim lngDay As Long
Dim dteLoop As Variant
Dim blnHol As Boolean
Dim dteHoliday(5) As Date
Dim lngCount As Long, lngTotal As Long

dteStart = dteStartDate
dteEnd = dteEndDate

lngYear = DatePart("yyyy", dteStart)

'July Fourth
dteHoliday(0) = DateSerial(lngYear, 7, 4)

'Christmas
dteHoliday(1) = DateSerial(lngYear, 12, 25)

'New Years
dteHoliday(2) = DateSerial(lngYear, 1, 1)

'Thanksgiving - Last Thursday of November
dteHoliday(3) = DateSerial(lngYear, 11, 29 - _
Weekday(DateSerial(lngYear, 11, 1), vbFriday))

'Memorial Day - Last Monday of May
lngDay = 31
Do
If Weekday(DateSerial(lngYear, 5, lngDay)) = 2 Then
dteHoliday(4) = DateSerial(lngYear, 5, lngDay)
Else
lngDay = lngDay - 1
End If
Loop Until dteHoliday(4) >= DateSerial(lngYear, 5, 1)

'Labor Day - First Monday of Septemeber
lngDay = 1
Do
If Weekday(DateSerial(lngYear, 9, lngDay)) = 2 Then
dteHoliday(5) = DateSerial(lngYear, 9, lngDay)
Else
lngDay = lngDay + 1
End If
Loop Until dteHoliday(5) >= DateSerial(lngYear, 9, 1)

'Easter
lngDay = (((255 - 11 (lngYear Mod 19)) - 21) Mod 30) + 21

dteHoliday(5) = DateSerial(lngYear, 3, 1) + lngDay + _
(lngDay > 48) + 6 - ((lngYear + lngYear \ 4 + _
lngDay + (lngDay > 48) + 1) Mod 7)

For lngCount = 0 To (dteEnd - dteStart)
dteCurr = (dteStart + lngCount)
If (Weekday(dteCurr) <> 1) And (Weekday(dteCurr) <> 7) Then
blnHol = False
For dteLoop = 0 To 5
If (dteHoliday(dteLoop) = dteCurr) Then blnHol = True
Next dteLoop
If blnHol = False Then lngTotal = lngTotal + 1
End If
Next lngCount

CountWorkingDays = lngTotal

 

[vbaInet]

There's more to this code. Have you got the link to the thread?

 

[raskew]

The code appears to come from:

http://www.access-programmers.co.uk/forums/showthread.php?t=133329&highlight=function+businessdays

Bob

 

[vbaInet]

Oh thanks Bob.

lhooker: I haven't gone through Keith's code but from a quick skim through it seems it could work however, I would go with Chergh's code since it seems to be very simple and to the point. If you want to exclude holidays then you could create a table with all the holiday dates for that year then simply exclude from that (also using BETWEEN in the criteria of the query). With this approach you have more flexibility as to what extra days you may want to exclude.

 

[lhooker]

Thanks to all the answer to my question. I never created a function before. How do I set this function up to test ? Does it go in the "Modules" or "Event Procedure" section ? Once it's placed in the right place how do I call the function ? "Docmd" ? . . . Thanks again to all ! ! !

 

[vbaInet]

Which code did you choose to use?

 

[lhooker]

vbaInet,

Below is the code . . . Thanks ! ! !

Public Function WorkingDays(StartDate As Date, EndDate As Date) As Long


Dim intCount As Long

intCount = 0

Do While StartDate <= EndDate

Select Case Weekday(StartDate)
Case Is = 1, 7
intCount = intCount
Case Is = 2, 3, 4, 5, 6
intCount = intCount + 1
End Select

StartDate = StartDate + 1

Loop

WorkingDays = intCount

End Function

 

[vbaInet]

You create a new Module (just like you create a form) and put the whole code inside there. Save the module (Ctrl + S) and call it something like mdlGetWorkingDays.

After you've done that, put this in the CONTROL SOURCE of a textbox:

=WorkingDays(Date(), "31/12/2010")

This will calculate the working days between today's date and 31st of December.

 

[lhooker]

vbaInet,

I'm getting "#Name?" for the results . . . I followed your instructions . . . below is the code . . . This is what's in the Control Source
=WorkingDays(Date(),"31/12/2010") . . .



Public Function mdlWorkingDays(StartDate As Date, EndDate As Date) As Long


Dim intCount As Long

intCount = 0

Do While StartDate <= EndDate

Select Case Weekday(StartDate)
Case Is = 1, 7
intCount = intCount
Case Is = 2, 3, 4, 5, 6
intCount = intCount + 1
End Select

StartDate = StartDate + 1

Loop

BusinessDays = intCount

End Function

 

[vbaInet]

Do not change anything in the code.

Copy and paste it EXACTLY as it is.

 

[gemma-the-husky]

one thing

working days code is tricky

counting weekdays or weekends is relatively straightforward

working days depends on public holidays - these vary, especially easter - so you need some sort of table to determine those days. if you hard code it, one years code may not apply in another year.

 

[vbaInet]

one thing

working days code is tricky

counting weekdays or weekends is relatively straightforward

working days depends on public holidays - these vary, especially easter - so you need some sort of table to determine those days. if you hard code it, one years code may not apply in another year.

Yeap, was mentioned in post #8.

 

[DCrake]

There is a database table in the sample databases that contains everydate until 2025. It has a multitude of fields and this table can be linked to your own table then you apply filters to it to rturn whatever yu like. It includes weekdays and weekends, holidays, etc.

Here is the Link

 

[lhooker]

vbaInet,

Whoops . . . I forgot to change the variable name back . . . I just did the the result is sometimes wrong. For instance . . . See dates and results below:

=WorkingDays(Date(),"12/07/2010") ===> 95
=WorkingDays(Date(),"21/07/2010") ===> 0

 

[lhooker]

Mmmm . . . some how my reply was not posted . . . oh well . . . Here it is again . . . The dates used and the results . . .

=WorkingDays(Date(),"12/07/2010") ===> 95

=WorkingDays(Date(),"07/07/2010") === 0

=WorkingDays(Date(),"21/07/2010") ===> 0

=WorkingDays(Date(),"30/12/2010") ===> 112

 

[vbaInet]

Maybe I should tell you what Date() is.. Date() means today's date. Does that give you a clue?