Generating a List of Project Parts Based on Project Duration and Time Window

Hi

I have a table of projects with 1) duration in year, 2) time window (number of years of our planning cycle), and 3) start year of the project. I want to generate a list of project parts of all projects where they may take place. This will serve as an input to an optimization program.

So a project of 2-year duration should have 2 parts over any year within the time window. I am including the "impossible" ones for my developer to tag them as "0" when we run it through his code.

I am wondering if there's a way to generate this list without VBA code. If not, how should I approach it with VBA? I hope the attached will help.

Thank you in advance for your help!

Glenn

I don't understand what "project parts" means.

How do "parts" derive from duration, time window, and start?
Like, mathematically.

For that matter, what's the difference between "duration" and "time window"?

See attached file for a formula-based solution. I've moved your larger table across so that you can compare the results - the bits that I've added are in blue.

Hope this helps.

Pete

Sorry for not making it clear. Maybe I am not using the right terms. Let me try to clarify.

A project with duration of 2 years will have 2 parts, 3 years will have 3 parts... each part taking up 1 year. So the parts in column H will not exceed the project's duration.

Time window is the time range that these projects will take place. So a time window of 5 years with "starting year" of 2014 will result in a "time window" from 2014 to 2018.

So I want to generate columns F to I from columns A to D.

Did you not see my solution? No comment back from you yet.

Pete

Yes, that was perfect! Thanks! I will mark it solved.