Another way to look at this is the need to count "business" days between dates. The problem, as Ray points to concerning holidays varying between cultures/countries, is how to adjust your calendar from one year to the next.
You have two requirements: count the number of non-weekend days between dates, and adjust for weekdays that should not be counted.
1) A calendar table with a flag for each day of the year: count-me, or don't-count-me.
2) A process that loads this table for each calendar year.
In the US, we have a varying number of holidays each year in the financial industry. We load our calendar every December for the coming year.
A very simplistic calculation would look like this. Instead of a count-me flag, I assign a value or not as follows:
Each day of the year has an ordinal (sequentially numeric) value if it is supposed to be counted. In your case the first step is check the ending date against the table. This gives you your ending ordinal value. Subtract the intransit amount, look up that ordinal on the table for its date.
Just to clarify:
Code: Select all
Mon Tues Wed Thur Fri Sat Sun Mon Tues Wed ...
001 002 003 004 005 --- --- 006 007 008 ...
If the 2nd Wednesday in my example is May 25, and your intransit value is 6, then the final lookup gives you Tuesday May 17.
Basically, instead of finding your weekend days every time you calculate, just set up to skip over them.
I leave it to the audience to figure out what to do when your start date is in December and your delivery date is in January.
