+ Reply to Thread
Results 1 to 6 of 6

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

  1. #1
    Forum Contributor
    Join Date
    01-29-2009
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    123

    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
    Attached Files Attached Files

  2. #2
    Forum Expert ben_hensel's Avatar
    Join Date
    01-26-2012
    Location
    Rocester, New York, USA
    MS-Off Ver
    Excel 2007:10
    Posts
    2,019

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

    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"?

  3. #3
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    21,684

    Re: Generating a List of Project Parts Based on Project 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
    Attached Files Attached Files

  4. #4
    Forum Contributor
    Join Date
    01-29-2009
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    123

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

    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.

  5. #5
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    21,684

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

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

    Pete

  6. #6
    Forum Contributor
    Join Date
    01-29-2009
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    123

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

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 1
    Last Post: 05-02-2014, 03:05 PM
  2. [SOLVED] Sum values in cells based off of project name and project end date
    By ronalgreen in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-26-2013, 01:08 AM
  3. [SOLVED] Function that concatenates multiple sub-project descriptions based on project code
    By markbpi in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-28-2012, 06:49 PM
  4. project sales based on known awards amount over time
    By cegarbage in forum Excel General
    Replies: 22
    Last Post: 05-04-2011, 11:52 PM
  5. Replies: 1
    Last Post: 10-18-2005, 11:05 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1