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.
George_Hepworth
Mar 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.
Tony2021
Mar 08, 2022Steel Contributor
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?
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?
- 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.