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...
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
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.
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.
BusinessDayBeforeDate(DateAdd("yyyy",-2,[Holiday]),5)
- What do you mean? I am not sure what is 2 years in the past?
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)George, I dont see any other holiday between 11/3/22 and 10/26/22 other than Halloween 10/31/22
- 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?
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.
- 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.
- 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? - 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.
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.