Forum Discussion
Calculated Dates (taking into Account Holidays)
thank you George. I poked around in those modules. Interesting. I saw something named "tlkpHoliday" (below pic). I assume that is a table and maybe I could change my holiday table to that name. I see also a dProposedDate, dStartDate and dEndDate and not certain how I can assign them in my case. I have not read all of it though. Probabaly wouldnt make much of a difference if I did since most of it I unfortunately dont understand, which you hinted that I might not be able to. I will keep poking around in it. thank you for the assistance.
I modified some of the existing codes to produce two functions that should return the date X business days after a specified date and the date X business days before the specified date. It appears to be returning valid dates in my tests so far.
Public Function BusinessDay(ByVal dProposedDate As Date) As Boolean
'Is the proposed Business Day on the holiday calendar or a weekend?
10 BusinessDay = False
20 If CountHolidaysDCount(dProposedDate, dProposedDate) > 0 Then
30 BusinessDay = False
40 ElseIf Weekday(dProposedDate) <> 1 And Weekday(dProposedDate) <> 7 Then
50 BusinessDay = True
60 End If
End Function
Public Function BusinessDayBeforeDate(ByVal dStartDate As Date, ByVal iDayTarget As Integer) As Date
'Calculate the Business day "iDayTarget" number of day before the start date
'Requires the starting date and number of days prior as arguments.
Dim iDayTest As Integer
Dim dProposedDate As Date
10 iDayTarget = Abs(iDayTarget)
20 iDayTest = 0
30 dProposedDate = dStartDate
40 Do While iDayTest < iDayTarget
50 If iDayTest < iDayTarget Then
60 dProposedDate = dProposedDate - 1
70 End If
80 If Not BusinessDay(dProposedDate) Then
90 iDayTest = iDayTest
100 Else
110 iDayTest = iDayTest + 1
120 End If
130 Loop
140 BusinessDayBeforeDate = dProposedDate
End Function
Public Function BusinessDayAfterDate(ByVal dStartDate As Date, ByVal iDayTarget As Integer) As Date
'Calculate the Business day "iDayTarget" number of day After the start date
'Requires the starting date and number of days after as arguments.
Dim iDayTest As Integer
Dim dProposedDate As Date
10 iDayTarget = Abs(iDayTarget) * -1
20 iDayTest = 0
30 dProposedDate = dStartDate
40 Do While iDayTest > iDayTarget
50 If iDayTest > iDayTarget Then
60 dProposedDate = dProposedDate + 1
70 End If
80 If Not BusinessDay(dProposedDate) Then
90 iDayTest = iDayTest
100 Else
110 iDayTest = iDayTest - 1
120 End If
130 Loop
140 BusinessDayAfterDate = dProposedDate
End Function
Public Function CountHolidaysDCount(ByVal dStartDate As Date, ByVal dEndDate As Date) As Integer
CountHolidaysDCount = DCount("Holiday", "tlkpHoliday", "tlkpHoliday.Holiday Between #" & dStartDate & "# AND #" & dEndDate & "#")
End Function
Hi George, thats interesting. Are the 10,20,30...n ==> are the numbers the days prior or something else? Are you using a tblHolidays like I am using the the attached db above? I only need to return a date that is PRIOR to a date that I enter.
This table tblDatesDeliver is what I would want to populate.
If I enter DrawDate then
DeliverToAA is 6 business days prior
DeliverToIE is 11 business days prior
If I enter a date into CAWC then the
DeliverToJPM is 4 business days prior
Could you kindly show me how to use it? I guess a form built off of tblDatesDeliver but I am not sure how to call the functions. Thank you very much. Looking forward to your response.
No they are line numbers for the lines in the VBA code.
There are two Functions in that set, one for days prior, one for days after.
I literally have to run out now, but I can try to give an example sometime late this afternoon.
The Functions take two arguments: the date FROM which to calculate, and the number of days to add or subtract. It does require a table of holidays, yes.
More Later.This little demo might help flesh out the picture. Note that the method used, looping, will be slow on larger record sets and greater intervals. For a couple of dozen records within 30 days or less, it should be fine.
