I'd like to create a "reference" schedule, then create an optimistic and pessimistic schedule based on this where the duration of each task in the optimistic/pessimistic schedules are calculated as a formula relative to the optimal schedule.

I know how to create custom fields with calculated values, but how do I structure the work so I can toggle between the different Gantt charts based on the optimal, reference and pessimistic schedules?


3 Answers 3


If you want to create alternate schedules quickly, without a lot of setup, what I have done is to make a copy of the Project file, and change the base calendar in that copy. Thus if your pessimistic schedule has everything taking twice as long, reduce working hours to 4 each day in that version. I have done this to create a schedule showing the effect of furloughs, by changing to a 4-day week.

This will need to be redone if you change the schedule, but is very quick to do. The copying among alternate durations method in the other answers has the advantage of permanently recording the modified durations, and allowing you to apply different scaling factors to different tasks, but requires more setup and maintenance.

Note that this will only scale work-based task durations. This may be what you want or not, when you specify that a task will take fixed time regardless of resources. You may need to be clever with the task types depending on the mix of task types you typically use.


If all you're changing are task durations (i.e. no different tasks, changed predecessors, successors, etc), you could use the DurationX columns (Duration1, Duration2, etc) to represent the other durations.

For example, in the screenshot below, I've set Duration1 to "Optimistic Duration" and configured the field to be 1/2 of the "Standard Duration" (Duration3). Duration2 = "Pessimistic Duration" and is configured to be 2x the "Standard Duration". The second screenshot shows how Duration1 and Duration2 are automatically updated (i.e. blue background) when Duration3 is updated.

Optimistic, Standard, and Pessimistic Duration columns

Screenshot showing Optimistic, Standard, and Pessimistic Duration columns (Duration 1,3, and 2, respectively)

The rub is that the Duration3 field is not used in the Gantt chart -- you'll need to copy-and-paste the duration values (from Duration1, 2, or 3) into the "Duration" field to have the Gantt chart display those specific values. Project (at least as of 2007) does not have a way to show multiple durations for a single task.

Setting the Formula for "Pessimistic Dur" as an example:

Setting the Formula for "Pessimistic Dur" as an example

Here's what it looks like (using Project 2007) when you update the "Standard Dur" field -- the Optimistic and Pessimistic fields are re-calculated automatically.

When you update the "Standard Dur" field -- the Optimistic and Pessimistic fields are re-calculated

Creating a script to update Duration values for all tasks would be trivial to write.

  • Okay, say I update DurationX with optimistic/pessimistic values, will Project be smart enough to make the association with StartX/FinishX and update them based on the DurationX values?
    – Gili
    Commented Feb 2, 2013 at 4:06
  • Yes, as long as you set the value of the fields to a calculation based on the value of the Standard Duration field. If you customize the Optimistic Duration field, for example, to a formula of "[Duration3]/2]" will set Duration1 (Optimistic) to 1/2 of the standard duration (Duration2). Right-click on the column header then choose "Customize field", click "Formula" button and set the formula as above. Then you'll see the calculated fields change automatically whenever the Standard Duration field is updated.
    – balinjdl
    Commented Feb 2, 2013 at 4:53
  • It doesn't seem to work. Duration1 is being calculated correctly, but Start1 and Finish1 are not updated. Any ideas?
    – Gili
    Commented Feb 2, 2013 at 22:51
  • 1
    StartX and FinishX columns (e.g. Start1, Start2, etc) are not automatically calculated. Only Start and Finish are. Since DurationX is not used in calculating the Start and Finish values, you'll need to update the Duration column manually (copy-and-paste from a DurationX column) or programmatically (using a VBA script to set the values based on a DurationX value). That's what I was trying to say with the "The rub" paragraph above. The automatic values are simply the DurationX values, which need to be copied into the Duration field.
    – balinjdl
    Commented Feb 3, 2013 at 12:35

I'm not sure that what you want to do can be done.

Furthermore, based on my experience, even if it can be done, you may not wish to do it.

Our experience is that although you can do clever things with the fieldx fields(@balinjdl's description is well written and a very good introduction), that very quickly those clever things become insupportable.

In particular we've found it fiendishly difficult to error check calculations on customized fields, and particularly diabolic when performing any calculations against dates (as you are contemplating doing). Project converts dates to different units, which makes math and comparisons unpredictable. The effort involved in maintaining what initially appeared to be relatively trivial code far surpasses the effort of saving the project plan as an excel document and doing the math in excel, then manually copying it back into the project plan.

I'm going to repeat with emphasis (mostly because I wish I could go back and say this to my past self) that the cost of supporting even trivial custom fields/formula's in excel far outweighs the value.

  • 1
    Good comments but I've got to say: what a pain in the ass. I don't see this as difficult to implement from a programming point of view. It's just too bad no one has put together a realistic project management software to do this. The closest thing I found was liquidplanner.com
    – Gili
    Commented Feb 5, 2013 at 19:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.