Larry,
I had some issues with date formats (I'm in Canada and regional setting is DD/MM/YYYY.) but did get it resolved.
Here is a procedure to add the number of business days(working days) to a date, and get you the end date (inclusive of start date)
of that period. It also accommodates holidays. This shows a starting date of Jul 9 2016 and adding 6 Business Days. Weekend is Saturday and Sunday.
You could make this a function to use any start date and any number of business/working days. The accompanying holiday table requires the design and name I have given --but you could change that as/if necessary.
Here is the code:
Code:
'---------------------------------------------------------------------------------------
' Procedure : addBusDays
' Author : mellon
' Date : 17/July/2016
' Purpose : to calculate 6 business days (inclusive) from a given date AND
'to use Tbl_myHolidays for identified Holidays and to account for holidays
'---------------------------------------------------------------------------------------
'
Sub addBusDays()
'
'
'
Dim BusDayCnt As Integer, NthBusinessDay As Integer
10 Dim dtstart As Date: dtstart = #7/9/2016#
20 Dim intBusDays As Integer: intBusDays = 6
30 NthBusinessDay = intBusDays
' Since Starting day is day 1,must adjust the number of business days
40 intBusDays = intBusDays - 1
50 On Error GoTo addBusDays_Error
60 Do While Not BusDayCnt = intBusDays
70 If Not (WeekDay(dtstart) = 7 Or WeekDay(dtstart) = 1) Then 'weekday
80 If DCount("*", "tbl_MyHolidays", "Holdate=#" & dtstart & "#") > 0 Then 'holiday
90 Debug.Print dtstart & " is a holiday" 'do not add to BusDayCnt
100 Else
110 BusDayCnt = BusDayCnt + 1 ' not holiday and is a weekday =BusDay
120 Debug.Print "BusDayCnt is " & BusDayCnt & " on date " & dtstart
130 End If
140 End If
150 dtstart = dtstart + 1
160 Loop
170 Debug.Print "The " & NthBusinessDay & "th business day is " & dtstart
180 On Error GoTo 0
190 Exit Sub
addBusDays_Error:
200 MsgBox "Error " & err.number & " in line " & Erl & " (" & err.Description & ") in procedure addBusDays of Module AWF_Related"
End Sub
Here is my holidays table tbl_MyHolidays data ( design is in attached jpg the original set up)
HolDate |
id |
03/25/2016 |
1 |
03/28/2016 |
2 |
03/17/2016 |
3 |
12/07/2016 |
4 |
And this is the result in the immediate window: NOTE my Canadian Regional setting for Dates DD/MM/YYYY
BusDayCnt is 1 on date 11/07/2016
12/07/2016 is a holiday
BusDayCnt is 2 on date 13/07/2016
BusDayCnt is 3 on date 14/07/2016
BusDayCnt is 4 on date 15/07/2016
BusDayCnt is 5 on date 18/07/2016
The 6th business day is 19/07/2016
I hope this is helpful.
Good luck with your project.
Note: After posting this I noticed that the holiday for July is formatted as per Canadian Regional setting???
In design view, as soon as I put the cursor on the field, it immediately goes to American MM/dd/yyyy format???
And when I move off the field it reverts to DD/MM/YYYY??????
Update again: I modified my regional setting to show date as dd-mmm-yyyy. The table field Holdate also has format dd-mmm-yyyy and it now shows this latest design:
HolDate
|
id |
25-Mar-2016 |
1 |
28-Mar-2016 |
2 |
17-Mar-2016 |
3 |
12-Jul-2016 |
4 |
and the procedure results are
BusDayCnt is 1 on date 11-Jul-2016
12-Jul-2016 is a holiday
BusDayCnt is 2 on date 13-Jul-2016
BusDayCnt is 3 on date 14-Jul-2016
BusDayCnt is 4 on date 15-Jul-2016
BusDayCnt is 5 on date 18-Jul-2016
The 6th business day is 19-Jul-2016