Forum Discussion
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 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.
some guidance is greatly appreciated.
thank you. Let me know if any questions.
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 addedDo 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.
arnel_gp Interesting, but your code also returns the wrong date, the 27th, not the 26th.
I'm still unsure as to why I do get the right date and Tony doesn't from the same data and function.
I suppose I could hard code the three options specified in the original question as default number of days, but my version allows for any date and number of days calculations, hence, more flexibility for other uses.
are you referring to March month?
then your date is reversed, should be:
#3/11/2022# (US-date format) or
#2022-11-3# (iso format).
if you enter the same infor like in the worksheet it will givesame result.
