Forum Discussion
Solved
Calculated Dates (taking into Account Holidays)
Hello Experts, I am building a table: tblDatesDeliver. There are 5 fields in this table. 2 are manually entered 3 should be calculated based on the number of business days count prior I h...
First, calculations generally should not be stored in tables (although there are some exceptions which probably aren't applicable here).
A lot of developers have created functions to calculate due dates, etc. Most involve, as your does, a holiday table. I can provide mine, but you might find some simpler to follow because they're already laid out for sharing.
HI George,
thank you. I am trying to get it working.
I dont know if this is what I am after since it returns a count between 2 dates and I am not looking for the count but rather a date based on the count but possibly could get it working. Also the holidays in the codes are only Federal Holidays but I need to include dates on top of those dates and modifying the fixed dates in the codes is not easy due to the way the count is.
I have an error on "Throw"
Assume a reference needs to be added
Do you happen to know what reference it could be?
I have the following references:
Agreed. It will take some work to implement any of the common sources in a way that meets your needs.
I have a whole module devoted to this kind of date related calculations, but I hesitated to offer it because it is pretty complicated. I recommended Mike's blog because I admire his work and it's usually pretty straightforward.
As you noted, you will have to maintain the holidays table with any dates your organization observes as holidays. That is going to vary by organization.
I don't see anything in that screenshot that tells me what the problem is, unfortunately.
I have a module for many kinds of date calculations, but I'm not sure it has anything in it that meets your needs directly. Check it out. Import the two files in this attached zip into your VBE environment.
I'll poke around some more, I know I've seen similar functions; it's just a matter of the right search words in Google.
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 = True60 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 FunctionPublic 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 = 030 dProposedDate = dStartDate
40 Do While iDayTest > iDayTarget
50 If iDayTest > iDayTarget Then
60 dProposedDate = dProposedDate + 1
70 End If80 If Not BusinessDay(dProposedDate) Then
90 iDayTest = iDayTest
100 Else
110 iDayTest = iDayTest - 1
120 End If130 Loop
140 BusinessDayAfterDate = dProposedDate
End FunctionPublic Function CountHolidaysDCount(ByVal dStartDate As Date, ByVal dEndDate As Date) As Integer
CountHolidaysDCount = DCount("Holiday", "tlkpHoliday", "tlkpHoliday.Holiday Between #" & dStartDate & "# AND #" & dEndDate & "#")
End Function