Here's a start. I'm not sure it will lead anywhere.
Anyhoo, what I did was to calculate adjusted Submit and Complete such that anything submitted on the weekend is adjusted forward to the beginning of the work week and anything completed is adjusted back to the end of the work week.
From there, it's a simple subtraction, Complete - Submit...
...except if it spans a weekend (e.g. Fri-Mon, Thu-Tue). For that, it tests if the Weekday(Complete)<Weekday(Submit)...if it is, then subtract 2 (I've taken care of that).
...except if it spans two weekends, well...no dice. May be able to account for this with something like INT((Complete-Submit)/7) which would give the number of whole weeks.
...except if it spans a weekend, but is more than a full week (e.g. Fri-Fri), it won't subtract the weekend time. Or maybe the solution to the previous bullet will cover this.
Still doesn't account for downtime. We can skin that cat later. In the meantime, please test this out. The solution is not as pretty as I would like and I have a feeling that it's leading us down a rabbit hole of exceptions to be accounted for.
|