SOLVED

Calculated Dates (taking into Account Holidays)

Steel Contributor

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.

27 Replies
best response confirmed by Tony2021 (Steel Contributor)
Solution

Re: Calculated Dates (taking into Account Holidays)

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

Re: Calculated Dates (taking into Account Holidays)

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

Re: Calculated Dates (taking into Account Holidays)

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.

Re: Calculated Dates (taking into Account Holidays)

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

Re: Calculated Dates (taking into Account Holidays)

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

Yes, it is.

Re: Calculated Dates (taking into Account Holidays)

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!

goodluck!