Iterate range of dates breaking qty in appropriat week
As ever, started on something I thought would be easy with QV and it is turning out to be a nightmare... In a ver simple example I have three fields [Start Date], [End Date] and [Qty]. I need break the date range down into constituent weeks and allocate the correct fraction of [Qty] to that week..
As an example (dates in dd/mm/yy format for this old fashioned English girl)
[Start Date], [End Date], [Qty]
At this time I'd settle for something that creates three records (say with fields [WeekNo] and [WeekQty]) in an iterated loop as follows:
w/c 06/12/10 with quantity 11
w/c 13/12/10 with quantity 11
w/c 20/12/10 with quantity 11
But being such a perfectionist I'd really like it to aportion the right amount to each week by the number of working days in that week.
w/c 06/12/10 has 4 working days (in the range specified) and so gets 12
w/c 13/12/10 as 5 working days (in the range specified) and so gets 15
w/c 20/12/10 has 2 working days (in the range specified) and so gets 6
Hopefully that makes sense to some QV guru out there who can help!