VB and VBA Users Source Code: Adding a number of weekdays to a date
[
Home
|
Contents
|
Search
|
Reply
| Previous | Next ]
VB/VBA Source Code
Adding a number of weekdays to a date
By:
Andrew Baker
Email (spam proof):
Email the originator of this post
Date:
Thursday, May 20, 2004
Hits:
1746
Category:
Visual Basic General
Article:
The simple function adds a number of weekdays to a date. 'Purpose : Adds a number of weekdays to a specified date. 'Inputs : dtDate The starting date ' lNumDays The number of days to add (if zero will find the first working day ahead of the start date). 'Outputs : The starting date plus the number of specified weekdays 'Author : Andrew Baker (copyright www.vbusers.com) 'Date : 31/Jan/2002 'Notes : For Excel change the header to: ' ' Function WeekDayAdd(ByVal dtDate As Date, ByVal lNumDays As Long) As Date ' Dim lWeekDaysAdded As Long, eWeekday As VbDayOfWeek ' Dim lAdd As Long ' Application.Volatile True ' On Error GoTo ErrFailed ' ' To use the function in worksheets 'Revisions : Function WeekDayAdd(lAddDays As Long, datDate As Date) As Date Dim lWeekDaysAdded As Long Dim intStep As Integer Dim lWeekDay As Long, dtStartDate As Date On Error GoTo ErrorHandler dtStartDate = datDate 'Determine if we are going backwards or forwards If (lAddDays < 0) Then intStep = -1 Else intStep = 1 End If 'Loop until we have added the required number of weekdays Do lWeekDay = Weekday(dtStartDate) If ((lWeekDay <> vbSaturday) And (lWeekDay <> vbSunday)) Then If lWeekDaysAdded = Abs(lAddDays) Then Exit Do End If lWeekDaysAdded = lWeekDaysAdded + 1 End If 'Move forward/backward a day dtStartDate = dtStartDate + intStep Loop WeekDayAdd = dtStartDate Exit Function ErrorHandler: Debug.Print Err.Description Debug.Assert False WeekDayAdd = 0 End Function
Terms and Conditions
Support this site
Download a trial version of the Excel Workbook Rebuilder
