Unanswered: Calculate difference between two dates excluding weekends
I'm sure that this question has been asked before, but I couldn't find the thread here. Maybe i'm not that good at searching.
Basically, I have two columns in a query, [tool_in date] and [end_repair_date]. I want to calculate the difference between the dates but want to exclude the weekends. Is that possible?? I know that I'm gonna have to use DateDiff, but, not sure how to use it. Please correct me if i'm wrong.
At first sight I would compute the number of entire weeks between the 2 dates. This number multiplied by 5 will yield a number of days. To this number I would add the number of weekdays (if any) in the fractional parts (days before the first entire week and days after the last entire week). The final sum would be the number of working days, except for any official holiday that can be in the period.
If you want to take official holidays into account, you'll probably need to import a calendar table with those holidays (they vary from country to country, some have a fixed date, some have a varying date that must be computed). There are several versions of such calendars available on the net. With such a calendar table, a simple INNER JOIN query with a condition excluding holidays and weekends, plus a COUNT() function will yield the correct result.