Flexible Timeframe Project Template RRS feed

  • Question

  • To Whom it May Concern:

    I am trying to create a Microsoft Project Template for my company's building design process. We have multiple phases that we implement into each project with several tasks per phase. I want to create one template that allows me to enter in the Start Date and End Date of each phase and then apply a percentage duration to each task, instead of a number of hours/days/weeks. (ie - I want to show the duration of a task as a percentage of the entire phase that it is a part of. Then, I would like it to establish the start and end date of that task based on the overall project timeframe.)

    Is this possible?

    Any help would be very much appreciated!

    Wednesday, April 16, 2014 3:40 PM

All replies

  • EllenB19,

    Yes it is possible but not directly. To do it you will need some VBA. If that approach is acceptable then we can probably help get you started.

    However you won't be able to simply enter the start and finish dates for each phase unless you set each phase task line for manual schedule mode. If using auto-scheduling for your template for each phase task line enter a start date and a duration, Project will calculate the finish date based on the project calendar. You can adjust the duration if the end date isn't what you need.


    Wednesday, April 16, 2014 6:01 PM
  • John,

    Thank you for the prompt response! I don't know anything about VBA (Visual Basic, I'm assuming) so, I'm not sure if that is the correct direction for me to go...

    I have been playing with my template and have created a Summary Task as the Phase with its associated tasks beneath it. The Summary Task is Manual and all the others are Automatic. Because I don't want to enter a duration into each task, I cannot get the Gantt Chart to show the proper length of calculated time for each task. Is there any way to have the Gantt Chart based on a Custom Duration that I've created with a formula instead of the built-in Duration field?

    Thank you for your time!


    Wednesday, April 16, 2014 7:46 PM
  • EllenB19,

    Yes, VBA stands for Visual Basic for Applications. It is included with most Microsoft applications but it does require some learning in order to user it effectively. If you are interested, Project MVP, Rod Gill, has an excellent book ( And we are also here to help.

    You can certainly show a custom duration on the Gantt Chart by creating your own Gantt Bar (Format/Bar Styles group/Format/Bar Styles). But instead of using your custom duration directly to set the custom bar, you will need to use custom start and finish fields (e.g. Start1 and Finish1) for the "From" and "To" columns in the Bar Styles window. Also be advised that bars at the bottom of the list take precedence over bars at the beginning of the list, in case there is a conflict.

    Just curious, what kind of formula are you using to develop your custom duration? I thought you needed a percentage of the total phase duration and assuming your phases are summary lines, you can't use that duration value in a task line formula. That's why I suggested VBA and not a custom formula in my initial response.


    Thursday, April 17, 2014 2:49 AM
  • John,

    Thanks for the pointer about the Gantt Chart set-up...I didn't know that was an option. If you can't tell, I'm not proficient in Project, yet.

    So, in order to create my custom duration, I put in a Start and Finish value for my Phase Summary Task. Then, I made custom Duration1 (Phase Duration) = ProjDateDiff([Project Start],[Project Finish],"Standard") and custom Duration2 (Task Duration) = [Duration1]*([Number3]/100). Custom Number3 is a whole number indicating during what percentage of the phase that a task should be completed (5%, 10%, etc.). I then, created a custom Task Due Date column that adds the start date of the phase to the Task Duration days for each task. However, the Gantt Chart just shows all the tasks at the Start Date because I have deleted their individual Duration parameters. I may be able to finagle that to work, now that I know about the custom settings.

    Do you think that the VBA approach would be cleaner?

    Thank you for your help!


    Thursday, April 17, 2014 2:57 PM
  • EllenB19,

    It looks like you're getting a handle on it but I have a few comments.

    First, unlike Excel, formulas in custom fields can only operate on data in the current row. What that means is you can't take a value from a summary line and use it in a formula to calculate task values. A macro (VBA) doesn't have this restriction.

    Second, you don't need the DateDiff formula for the Duration1 field. Since your Phase summary task is manually scheduled, when you manually enter both a start and finish date, you will see that Project automatically calculates the duration based on the standard calendar. Therefore you can simply copy the normal Duration field to Duration1. To get that value to appear in Duration1 for each subtask, use a fill down.

    Third, I'm not quite clear on how you want the Task Due Date to be calculated. I would assume the following but correct me if I'm wrong. Let's say you have a phase duration of 40 days and that phase has 3 subtasks, the first taking 20%, the second 10% and the third 20% of the total phase duration. I'll also assume that the three subtasks must be performed in a sequence of 1,2 and 3. Now, here is where it gets complicated when attempting to do this using custom field formulas. You can easily calculate the first subtask due date by using a simple ProjDateAdd function in a formula with the task Start date as the "seed". However because subtasks two and three cannot use their respective task start dates, you will need to set up an additional custom start field and manually copy the previous task's finish date such that that field can be used a a "seed" for that tasks due date formula.

    Fourth, all this works strictly with duration and in Project, duration accomplishes nothing, it is simply a span of time. In a "normal" project plan, a set of linked tasks are defined, (i.e. what is to be done), and then resources are assigned. The Work field describes the amount of effort required from one or more resources to actually accomplish each task in the plan. I call these tasks "performance tasks" since that's where the "rubber hits the road" so to speak. Summary lines do not have any resources since they are not tasks at all, but only a summary of the performance tasks under them.

    Is your head spinning yet?

    What is your end goal? You say you want a template but how will that template then be used? Do you intend to manually enter the percentage for each performance task? I could help you with the VBA to do this, but I need more information.


    Thursday, April 17, 2014 4:11 PM
  • John,

    My head is definitely spinning...even after re-reading your response several times!

    Let me explain to you how I would like the template to work when I am finished and see if it is even possible.

    I work for an Architecture and Engineering firm. We design all types of buildings including: hotels, schools, convention centers, courthouses, etc. We have a process that we would like to follow on every single project, no matter the size or building type. We've developed a 'checklist' of items that are important to coordinate between every discipline involved in a building's design and construction. This checklist is simply a PDF in a folder that no one uses. Our people have asked for an 'interactive checklist'.

    We also have another version of this task list in Excel that simply allows the PM to insert the start and finish dates and then each item has a percentage next to it to show when that portion of the project needs to be finished which is then converted to a due date for each task.

    After exploring Microsoft Project, I learned that you can link Project to Outlook to assign tasks to people (resources). So, what I would like to do, is have a hybrid of the two items listed above. One general template that a Project Manager can open, insert the beginning and end dates of each phase and its deliverable, create their project team by dragging people from Outlook and assign each team member their group of tasks. The tasks would then be in order of importance in each team member's Outlook Tasks by project so, every person would know what their expectations are, what is most important to finish first (because another person is waiting for it to be completed), and when each task is due. It is unrealistic to have a PM enter in days, hours or months for each task in each project that they touch. That is why I would like the task timeframe to be elastic and adjust per the timeframe of each phase, based on when the task should be completed within that phase.

    I don't mind entering in a percentage for each task as a part of the template. As long as the PM does not have to enter in a timeframe for each task in each project. The template is going to be used as a means to keep people on task. In this industry, we have set deadlines and if we have to work extra to get it done, we do. The PM's have other means of calculating the workload for each individual so, I'm not interested in using the work portion of Project.

    Does this make sense? Is it possible? I talked to my boss about VBA and he has developed several macros for Excel and other programs so, if you think that VBA is the best approach, I think I can make it work with his help!

    I really do appreciate your time helping me with this. I am an engineer in an architect's world so, I'm trying to remedy the disconnect between our two disciplines and I really feel like this type of solution can help our company be more profitable...

    Thanks, again.


    Friday, April 18, 2014 3:14 PM
  • Ellen,

    Thanks for the detailed explanation of what you are doing. I don't use Outlook so I'm not familiar with the process involved in integrating Project and Outlook, but I believe it has changed and is less convenient than it was in versions of Project prior to Project 2010. You might want to check out the following two links:

    However, I'm not sure Project is a good match for what you are trying to do. The main purpose for using Project is to create an active schedule of tasks and resources to accomplish those tasks. Work is integral to that integration and although a user does not have to assign resources to tasks, using Project in that top level form (i.e. tasks only with no resource assignments) does nothing more than provide an outline of tasks with some Gantt Bars.

    For the process you describe I suggest you stick with Excel, Project will not offer any added benefit unless you plan to change your process and create active schedules. That's my opinion, others may see it differently.


    Friday, April 18, 2014 8:00 PM
  • Ellen,

    Everyone here would like to help you to do what you say you want to do, or what you say you need to do. I would like to help you to do that too.

    However, I really have to advise you to reconsider whether it is really what you should do, and whether, even if you achieve it, the results will be useful, or worth the trouble and effort and time, or what you really need. Perhaps the trouble and effort and time that you are investing would be more productively invested in a different approach and a different outcome. I think you are missing an opportunity to exploit the real benefits of using MS Project in the way it was designed to be used, in pursuit of what will turn out to be a futile endeavour to bend it to your will. I am sure that others reading this will be having the same reservations (come in any time here).

    First, I want to recommend two of my favourite books:

    Total Project Control By Stephen Devaux
    Construction Planning And Scheduling by Jimmie W Hinze

    Next, get a very clear (ie not superficial) understanding of the critical path method.

    You say "It is unrealistic to have a PM enter in days, hours or months for each task in each project that they touch".

    Actually, it is very realistic to do just that. I have done it hundreds of times over two decades on all kinds of projects, including engineering and architectural design and construction projects. It is not just realistic, it is essential, and it is exactly how MS Project was designed to be used. It works, and it is also very easy. There is nothing hard about it once you learn how. You are dismissing it as unrealistic without having attempted it.

    By the way, every industry has set deadlines. This observation is not relevant to this issue.

    Saturday, April 19, 2014 10:50 PM
  • John,

    Modelling resource assignments are good. You get to see work and manage the resources.

    Resource costs are good. You get to see the costs of each resource and each task.

    But even without them, a project broken down into tasks to describe the scope, with durations estimated and predecessors and successors, is still very useful. It gives you a critical path, calculates float and provides a way to monitor, track and record progress, and update the plan during execution.

    Of course, if it was just done with a crayon on a sheet of butcher paper you are right, it doesn't amount to very much. But when it is done in MSP, it is a huge advance on any other method and a big step in the right direction.

    Saturday, April 19, 2014 10:59 PM