Date span calculation

[cm_027]

OK. I admit I've been out of development for a while. I am attempting to develop a simple resource allocation spreadsheet to help me schedule my team's projects.

I have a listing of projects with the number of hours requested, a start date and the number of hours available to work on projects (among a few other columns irrelevant to this thread). I need a summary of the allocation of each person per week for the next several weeks.

I am fairly close, however, I have a few calculation problems plus one "bonus problem" I would like to resolve.

Please Login or Register  to view this content.

This should be the relevant pieces of code.

The major problem I am having is when the project ENDS on the date passed in or during CurrWeek; it miscalculates.

Another issue is that it seems to be falling down on multi-week spans. If a project spans 4 weeks, the intermediate weeks do not calculate properly. At least that is how it looks in my testing. I have a few weeks in the middle with odd numbers such as 80% utilization (HrsAllocated / HrsAvailable) when I expect 100%.

My bonus piece is the fraction of a project within a week. Say we have a 60 hour project with 40 hours of time available. The current calculation would be 2 weeks at 100% utilization. I would like 1 week at 100% and 1 week at 50%. I am not taking into account the number of remaining project hours.

HELP! How could this function (GetHrs) be rewritten properly? The full workbook is available upon request. I can post it on a web server if requested.

Thanks!

[cm_027]

I've posted the file as it stands in its unfinished state at:
http://www.cm-software.com/ProjectList.xls

I greatly appreciate any help!

[cm_027]

In attaching the actual xls file, I get a free bump. Hope it's not frowned upon.

I realize this is a simple logic problem, but it's stumping me. I may rewrite the impacted function entirely and try to be less "cute" with it. I may just end up brute forcing the logic.

If anyone is interested, there are a few fun items in this spreadsheet that could be ripped out and reused.

Even if you cannot help me out, the file could be useful to someone.