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?
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
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:
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.
Creating a script to update Duration values for all tasks would be trivial to write.
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.
