SOLVED

Calculated Dates (taking into Account Holidays)

Super Contributor

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 have this built in Excel and I am using the excel's WORKDAY formula (below) and a named range "HolidaysUS" which is a list of holidays and if there is a holiday hit in the day count then the day returned is adjusted accordingly so that the count does not include the holiday.

 

I am trying to replicate this excel file inside of Access. 

In excel I do the following:

1. Enter the [DrawDate] (manual enter)

2. [DelivertoAA] in xl is assigned with this formula (6 bus days prior): =WORKDAY(EDATE(DrawDate,0)+1,-6,HolidaysUS)

3. [DelivertoEE] in xl is assigned using this formula (11 bus days prior):

=WORKDAY(EDATE(DrawDate,0)+1,-11,HolidaysUS)

4. Enter the [CAWC] date (manual enter)

5. [DelivertoJPM] in xl is assigned using this formula (4 bus days prior):

=WORKDAY(EDATE(CAWC,0)+1,-4,HolidaysUS)

 

Wondering if some experts here have had experience with this.  I am not sure if the excel formula can be used in Access and the calculation should be made at the table level or in a form? Or if a crafty function is a better way to handle this.  

 

I have attached a simple database with 2 tables (tblDatesDeliver and tblHolidays with the holidays populated).  Also attached my excel file.  

Tony2021_0-1645968749856.png

 

some guidance is greatly appreciated. 

thank you.  Let me know if any questions.

 

27 Replies

@Tony2021 

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.

 

@George Hepworth 

 

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:

Tony2021_1-1645974025206.png

 

Tony2021_0-1645973769195.png

 

@Tony2021 

 

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.

 

 

 

@George Hepworth 

 

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.  

 

 

Tony2021_0-1645984309667.png

 

@Tony2021 

 

Back when I first created most of that code, I used a naming convention that included "tbl" prefixes for standard tables, "tlkp" for lookup tables, and "tjx" for junction tables. I got away from that because it's a bit of overkill for the most part. Your naming convention only needs to be consistent with what you otherwise do, although the VBA does have to line up with it.

 

I don't think it's so much a case of not understanding as it is wading through unfamiliar code for the first time and learning how someone else does things. Concentrate on the parts that do calculations of days between two dates to see if you can decompose the logic involved. At some point, you'll be able to reverse engineer that logic to create a calculated future date. 


We all started somewhere, and jumping  into the pool at the deep end isn't always the worst way to do that.

 

@Tony2021 

 

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

@George Hepworth 

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

 

Tony2021_0-1646063775069.png

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. 

@Tony2021 

 

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.

@George Hepworth 

 

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.

 

Hi George, sorry but I have been busy with my real job. I probably wont be able to dig into this until Thursday or possibly later. I am looking forward to testing it.
Hello George, sorry for my tardy reply. I looked it over and it looks good but I ahv a follow up if you coujld be so kind.
If I enter 11/3/2022 and a holiday is 10/31/2022 then 5 days prior should be: 10/26 but i think it returns 10/27 in column Business Days Before.

I am not sure if I am entering the data correctly though and/or understand how it works.
Could you kindly assist?
I'll have to make time today to revisit that. If the Holiday is in the holiday table, it should work the same as other date ranges.

@Tony2021 

 

This is the result I see in the version of the sample database on my computer.

GeorgeHepworth_1-1646757222061.png

 

 

@George Hepworth 

Hi George,

What could I be doing wrong if get a different answer on my end? I enter the same date 11/3/22 but it returns 10/27/22 and not 10/26/22 like you show above in your pic?  There is a holiday in the holidaytable of Mon 10/31/22. 

 

Tony2021_0-1646959550069.png

 

Oh, That's over Thanksgiving, right? I think in my table I have both Thursday (day) and Friday (day after) as holidays. Do you have them both?

@George Hepworth 

George, I dont see any other holiday between 11/3/22 and 10/26/22 other than Halloween 10/31/22

Tony2021_0-1646962637402.png

 

@Tony2021 

Oops, I was thinking of Thanksgiving, not Halloween.

The only other thing I can think of is that I used dates two years in the past.
Perhaps that's causing the discrepancy?


Business Days Before: BusinessDayBeforeDate(DateAdd("yyyy",-2,[Holiday]),5)

What do you mean? I am not sure what is 2 years in the past?

@Tony2021 

BusinessDayBeforeDate(DateAdd("yyyy",-2,[Holiday]),5)