Forum Discussion
Tony2021
Feb 27, 2022Steel 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 h...
- Mar 11, 2022
im too lazy to follow the thread.
but i get the idea based on the worksheet to attached.
see your demo.
Tony2021
Feb 28, 2022Steel Contributor
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.
George_Hepworth
Feb 28, 2022Silver Contributor
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_HepworthMar 11, 2022Silver Contributor
BusinessDayBeforeDate(DateAdd("yyyy",-2,[Holiday]),5)
- Tony2021Mar 11, 2022Steel ContributorWhat do you mean? I am not sure what is 2 years in the past?
- George_HepworthMar 11, 2022Silver Contributor
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) - Tony2021Mar 11, 2022Steel Contributor
George, I dont see any other holiday between 11/3/22 and 10/26/22 other than Halloween 10/31/22
- George_HepworthMar 11, 2022Silver ContributorOh, 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?
- Tony2021Mar 11, 2022Steel Contributor
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.
- George_HepworthMar 08, 2022Silver Contributor
- George_HepworthMar 08, 2022Silver ContributorI'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.
- Tony2021Mar 08, 2022Steel ContributorHello 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? - Tony2021Mar 02, 2022Steel ContributorHi 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.
- George_HepworthMar 01, 2022Silver Contributor
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.