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

Business date difference

P: 37
Has anyone got a good method to calculate buisness days differences or predict a date based on buisness days?

For example, If today 9/12 I place an order from vendor A they will say OK it will be ready in 10 business days. That means it will be ready on 9/26. If I had the same order on 8/29 I would have the order ready on 9/15.

My best idea is to put a table of dates that are holidays for my vendor (I have to work the day after Thanksgiving darnit why doesn't everyone!) then check every day to see if it is on the table or a Monday through Friday until I get to the buisness day counter. Likewise I could count from day A to day B to find the actual buisness days between order and reciept.

Any better ideas because I think mine sucks...

Carl
Sep 12 '08 #1
Share this Question
Share on Google+
4 Replies


FishVal
Expert 2.5K+
P: 2,653
Hello, Carl.

Idea to create a table with vendor's holidays is a good one.
As for calculating algorithm, I would like to suggest you an idea better than days iteration:
  • Using quite simple math you may exclude Saturdays and Sundays from days difference.
  • To exclude vendor's holidays just check record count between two dates mentioned in holidays table. Table should be filtered by weekday to not contain Saturdays and Sundays.

One more point. Almost pure SQL. ;)

You could make it using Cartesian query producing sequential dates. Saturdays and Sundays could be excluded by WHERE clause (as in example under link). Vendor's holidays could be excluded with outer join with [holidays table].
Then, as above, just calculate records count between dates.

Regards,
Fish
Sep 12 '08 #2

P: 69
In my experience, what FishVal says about a table is absolutely true and I tend to use the day iteration technique. If you construct such a routine from first principles you can make it do what you (and your customer!) want. Customers being the sort of creatures that they are, they will almost certainly come back with an additional requirement which will likely be easier to add to your own first effort.

Never underestimate the ingenuity of customers when it comes to delivering curved balls!
Sep 13 '08 #3

missinglinq
Expert 2.5K+
P: 3,532
Here's a link to a couple of hacks from Arvin Meyer. The second example excludes weekends and holidays!

http://www.mvps.org/access/datetime/date0006.htm

Linq ;0)>
Sep 13 '08 #4

FishVal
Expert 2.5K+
P: 2,653
IMHO, Arvin Meyer's code is playing at giveaway with logic.
It demostrates lack of imagination and creative approach multiplied by blind pragmatism. :P
I don't criticise this gentleman personally. Everyone has a period of young enthusiasm. ;)

Kind regards,
Fish
Sep 13 '08 #5

Post your reply

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