Every once in a while someone needs a way to compute the number of weekdays between two dates.
No professional programmer should be doing this with a loop. An 8-year-old child can do this with a loop. It's simply a matter of 5/7ths of the total with some tweaking. Also, you only have to understand a function in detail when debugging it. Other than that, use it if you have it, just like a car. Non-auto-engineers can drive. -- Mike Yearwood
Stating that others are too willing to accept or live with crappy code is not bullying. It is a fact. The code being shown here is 300,000 to 1,000,000 times better than the 8-year-old child code. The backlash I get from supposedly professional computer science people is BULLYING. https://www.rickhanson.net/stand-up-to-bullies/ -- Mike Yearwood.
A function should do one thing and do it well. Therefore this function does not compute holidays. Subtract the output of a custom weekday-holidays-function from the output of this function to compute the number of business days - which could also be a new function.
A function should also be the simplest, cleanest, and fastest possible. I now present one that beats what Ben and I came up with. In 3 runs my test code showed (old vs new) 3.094 vs 1.953, 3.172 vs 2.015, and 3.172 vs 1.968. That looks like a 37% faster result.
I saved the above as MyWeekDays and tested it versus the previous one with the following code.
The following one function is deprecated. -- Mike Yearwood
Ben Creighton came up with a really good one. So I applied formatting and adopted it. ;) -- Mike Yearwood
- INSERT and REPLACE are much slower than the previous code. -- Mike Yearwood
I just walked right into yet another looping version of the weekdays function.
Having a think about it, I once again wonder if there is any way to prevent the divide by 7 and the modulus. It seems only REPLACE can do it. Insert does not compute and store the values as they happen. Right? -- Mike Yearwood
Contributor: Mike Yearwood, Martina Jindrova
Category UDF