If you would like to get paid for surfing the web, jump to http://www.codeoftheweek.com/paidsurf.html
Want to get up to speed on the latest Visual Basic programming? Includes Visual Basic 6 and Visual InterDev 6. Check out our training programs at http://www.codeoftheweek.com/vbtraining.html
This issue shows how to calculate the number of workdays between two dates. It has several options for controlling how Saturday and Sunday are included. It also allows you to add a list of holidays to include in the calculations.
If you have any questions about using this module, let us know at questions@codeoftheweek.com
Public Function BusinessDateDiff(ByVal dDate1 As Date, ByVal dDate2 As Date) As Long
Returns the number of business days (or workdays) between dDate1 and dDate2. It has various properties which can be set to control how the calculation is performed. These properties are detailed below.
Public IncludeSaturdays As Boolean
When True the calculation will include any days that are Saturdays. When False it will omit any Saturdays from the calculation.
Public IncludeSundays As Boolean
When True the calculation will include any days that are Sundays. When False it will omit any Sundays from the calculation.
Public Property Let IncludeFirstDate(bInc As Boolean) Public Property Get IncludeFirstDate() As Boolean
By default the BusinessDateDiff routine includes the first date in the range in the calculation, but does not include the last day. Setting this property to False will omit the first date in the range from the calculation.
Public Property Let IncludeLastDate(bInc As Boolean) Public Property Get IncludeLastDate() As Boolean
By default the BusinessDateDiff routine does not include the last date in the range in the calculation. Setting this property to True will include the last date in the range from the calculation.
Public Sub HolidayAdd(dHoliday As Date)
Add a holiday to the list of dates to omit from the calculation. By default there are no holidays added so the BusinessDateDiff routine will count all dates.
Public Sub HolidayRemove(dHoliday As Date)
Removes a holiday from the list of dates to omit from the calculation.
Public Sub HolidayClear()
Clears the complete list of holidays added with HolidayAdd.
This example accepts two dates and performs several calculations with the various options turned on and off. The results that should be returned for the dates 1/1/99 and 1/31/99 are shown below:
29 30 31 30 25 20
Function WorkdayTest(dDate1 as Date, dDate2 as Date)
Dim BusDay As New cBusinessDates
BusDay.IncludeSaturdays = True
BusDay.IncludeSundays = True
BusDay.HolidayAdd "1/1/99"
BusDay.HolidayAdd "7/4/99"
BusDay.HolidayAdd "12/25/99"
BusDay.HolidayAdd "12/31/99"
Debug.Print BusDay.BusinessDateDiff(dDate1, dDate2)
BusDay.HolidayRemove "1/1/99"
Debug.Print BusDay.BusinessDateDiff(dDate1, dDate2)
BusDay.IncludeLastDate = True
Debug.Print BusDay.BusinessDateDiff(dDate1, dDate2)
BusDay.IncludeFirstDate = False
Debug.Print BusDay.BusinessDateDiff(dDate1, dDate2)
BusDay.IncludeSaturdays = False
Debug.Print BusDay.BusinessDateDiff(dDate1, dDate2)
BusDay.IncludeSundays = False
Debug.Print BusDay.BusinessDateDiff(dDate1, dDate2)
Exit Function
To see the source code for this issue you must be a subscriber to Code of the Week. If you are a subscriber the source code is available at the following address: http://www.codeoftheweek.com/membersonly/bi/0104.html
If you would like to get paid for surfing the web, jump to http://www.codeoftheweek.com/paidsurf.html