Calculated Dates (taking into Account Holidays)

[Tony2021]

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.

[#2 · Accepted solution]

@Tony2021 

im too lazy to follow the thread.

but i get the idea based on the worksheet to attached.

see your demo.

[#3]

@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.

[#4]

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 give

same result.

[#5]

No, the date is November 3, 2022. Not March.

[#6]

on the worksheet is March.
coincidence? today's date?

[#7]

Yes, it is.

[#8]

sorry I am tardy.
Hi Arnel.
George: In Arnel's sample db, I think it is correct. The reason it returns 10/27 and not 10/26 is because there is actually not a Halloween holiday in the table but if it is entered in teh holiday tbl then the date changes from 10/27 to 10/26 (I tested this).

Arnel I tested and its perfect! Thats amazing. I can also apply that logic elsewhere and change the count in the code. I have many other day count requirements. Was only a simple example I posted initially.

thanks again guys. Very kind and grateful for the assistance. Enjoy the weekend!

[#9]

goodluck!