By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,222 Members | 1,129 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,222 IT Pros & Developers. It's quick & easy.

Question about algorithm for calculating dates that do not fall on weekend/holiday

USTRAGNU1
P: 36
The post was an algorithm by Adezii.

[code]..
1.Public Function fCalcWorkDays2(dteStartDate As Date, lngNumOfDays As Long)
2. Dim lngCount As Long
3. Dim lngCtr As Long
4. Dim dteDate As Date
5.
6. lngCount = 0
7. lngCtr = 1
8.
9. Debug.Print "Date", "Day Count", "Weekday"
10.
11. Do
12. dteDate = DateAdd("d", lngCtr, dteStartDate)
13. Select Case Weekday(dteDate)
14. Case 7, 1 'Saturday and Sunday, do nothing
15. Case Else 'Monday thru Friday, OK
16. 'Is it a Holiday as posted in tblHolidays?
17. If DCount("*", "tblHolidays", "[Date] = #" & dteDate & "#") < 1 Then 'NOT Holiday
18. lngCount = lngCount + 1 'Increment Counter
19. Debug.Print dteDate, lngCount, Weekday(dteDate)
20. End If
21. End Select
22. lngCtr = lngCtr + 1
23. Loop While lngCount < lngNumOfDays
24. fCalcWorkDays2 = dteDate
25. End Function]
[Code/]..

I work for the Air Force and I am trying to create a function that will do this very thing, but mine is adding too many days. Adezii's function looks much cleaner, but I do not understand a couple of items.

1. I don't understand how just typing "Case 7,1" on line 14 tells access to ignore Saturday and Sunday. Can someone explain that?

2. Also, can someone explain the purpose of the pound signs on line 17:

If DCount("*", "tblHolidays", "[Date] = #" & dteDate & "#") < 1 ?

Thank you for your assistance in this matter.
Mar 16 '17 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.