Using Formulas in MS Project

Hello Ajibola,

Thank you for your question regarding working with formulas in Project. Your question is outside of the scope of Project Introduction / Intermediate. We do run an advanced course where we introduce custom fields using formulas.


If you require further assistance, please reply to this post. Or perhaps you have another Microsoft Office question?

Have a great day.
Regards,

Rodney
Microsoft Office Specialist Trainer

Hello Ajibola,

My apologies, I had to check with our admin team who confirmed you had completed the advanced course. It doesn't show up in the courses attended location in the forum post.

To answer your first question as to what approach to use when working with formulas in Project and the difference between Excel formulas and those of Project will revolve around what you would like to achieve.

For example, if you require to use a graphical indicator to show the status of the completeness of each task, you must first decide what parameters to use, then create a custom formula field using appropriate functions such as Iif which incorporate your chosen parameters. Then set the graphical indicators which match the results of your formula... then add the new custom field to a table and make sure that the results are what you are looking for.

Writing formulas in Project and Excel is fairly similar, however, the syntax may differ. When using Iif the syntax within the brackets is (Expression, True Part, False Part) pretty much the same as Excel.

To explain the formula above it is easier if you separate it into lines... 1 for each action. The formula begins with Iif then is followed by the Switch function. The Switch function syntax is as follows: Switch(Expression1,value1,expression2,value2...)etc.

Shown on separate lines the formula goes like this:

IIf([% Complete]<100,

Switch(([% Complete]=0 And (DateValue([Current Date])-DateValue([Start]))>0),1,

((DateValue([Current Date])-DateValue([Finish]))<-5),3,

(DateValue([Current Date])-DateValue([Finish]))>=-5 And (DateValue([Current Date])-DateValue([Finish]))<0,2,

(DateValue([Current Date])-DateValue([Finish]))>=0,1),3)

You will notice that number 1 or 2 or 3 appear in the formula as values after the expression calculation. These numbers represent the result you are looking for if the above parameters are true. If none of them are true the final result is a 3 (which is also the same as the second Switch expression).

You can now use these numbers when setting graphical indicators... Equals > 1 > Green
Equals > 2 > Yellow
Equals > 3 > Red

...or what ever choices you make.

You will also notice in the formula that the function DateValue is used. The syntax is DateValue(Date). The fields that have been used in the formula are Current Date, Start and Finish.

Examine the formula closely and you will be able to analyse it.

I hope this resolves your question. If it has, please mark this question as resolved.

If you require further assistance, please reply to this post. Or perhaps you have another Microsoft Office question?

Have a great day.
Regards,

Rodney
Microsoft Office Specialist Trainer