Loading...
Quick Links

Microsoft Project Custom Formula Guide

Custom formula fields and graphical indicators can be used with any version of the desktop  Microsoft Project application, as well as with Project Server.

The following steps will help you get started with creating your own custom fields and graphical indicators.  You can refer to the examples included in this document for the specific field settings required to define a custom field and associated graphical indicator.

To create a custom field in the “standalone” version of Microsoft Project, do the following:

  1. Start the Microsoft Project application.
    • If you are using Microsoft Project 2003 or 2007, from the menu, select Tools…Customize…Fields.
    • If you are using Microsoft Project 2010 or 2013, from the ribbon, select the Project tab, and click the Custom Fields button.
  2. From the Type drop down, select the type of custom field you want to create (Text, Flag, etc.).
  3. From the Field options, select Task or Resource. All of the examples in this document pertain to Task custom fields.
  4. From the Field list, select the field you want to define (Text1, Text2, etc.).
  5. Click the Rename button to assign a meaningful name to the custom field (e.g., Should Have Started).
  6. Click the Formula button, and a dialog box will be displayed.
  7. Type the formula as shown in this document, and click OK.
  8. Click the Graphical Indicators button, and select the options as shown with the examples in this document.
  9. To create a custom field for Project Server, from the Server Settings page in the Project Web App, select Enterprise Custom Fields and Lookup Tables. Click the New Field button, and enter all of the field settings as described in this document.

The examples in this guide will help you get started with creating custom fields in Microsoft Project.

Download the Complete Guide or simply view the formulas below

If you have any suggestions for other useful fields that the MPUG community would benefit from, please email them to customization@mpug.com.  Each month, we will publish some of the best submissions, and the number one suggestion will be awarded the prestigious “Top Field” award!

 Formula Cheat Sheet

Please refer to the complete guide for more detailed descriptions and instructions.

  1. Display Red, Yellow, or Green Light Indicator Based Upon Finish Variance Threshold
    Formula:  [Finish Variance]
  2. Task Needs To Be Baselined
    Formula:  [Baseline Start] = ProjDateValue(“NA”)
  3. Task Start Date or Finish Date Needs To Be Updated
    Formula:  Switch(Date()>[Start] And [% Complete]=0,”Should Have Started”, Date()<=[Finish] And [% Complete]<100,”Should Have Finished”, Date ()<=[Start],””, Date()<=[Finish],””)
  4. Tasks That Do Not Have Dependencies
    Formula:   IIf(([Predecessors]=”” Or [Successors]=””) And [Summary]=False,True,False)
  5. Summary Tasks That Have Dependencies
    Formula:   IIf(([Predecessors]<>”” Or [Successors]<>””) And [Summary]=True,True,False)
  6. Summary Tasks That Have Resources Assigned
    Formula:   IIf([Summary]=True And [Resource Names]<>””,True,False)
  7. Tasks That Do Not Have Resources Assigned
    Formula:   IIf([Summary]=False And [Milestone]=False and [Resource Names]=””,True,False)
  8. Tasks That Have Negative Total Slack
    Formula:   IIf([Total Slack]<0,True,False)
  9. Unfavorable Variance on Critical Path
    Formula: IIf([Finish Variance]>0 And [Critical]=True,True,False)

Download the complete guide for more information

 

Created by your friends at MPUG and Project Widgets

widgets logo

 


Have you watched this webinar recording? Tell MPUG viewers what you think!

Submit your review
1
2
3
4
5
Submit
     
Cancel
Create your own review
MPUG
Average rating:  
 0 reviews

Community
Written by Community

Where Project Managers and Microsoft® Meet.

Share This Post
6 Comments
  1. I downloaded the document and found some things I use right away. I have worked with Custom Fields both in the desktop and server versions. I find them to be useful but some times frustrating. Any help is appreciated and I thank MPUG sincerely for sending out this doc.

    Reply
  2. Please help me to check this formula. Why for milestone task, the graphic indicator did not show?

    IIf(CDbl([Baseline Finish]>60000),” Not Baseline”,IIf(([Baseline Duration]=0 Or [Duration]=0),Switch([% Complete]=100,” Completed”,[Status Date][Start],”Late”),Switch([Status Date]<[Start]," Not Start Yet",[% Complete]=100,"Completed",(ProjDateDiff([Start],[Status Date])/480)/Val(ProjDurConv(Duration,pjDays))*100[% Complete],”Late”)))

    Reply
  3. Thanks for the useful guide 🙂

    I think the formula for 3 would be better to use the status date as that is really what project reports on (I have used slightly more verbose outputs as it is clearer what is happening for other statuses rather than just blanks

    Switch(
    [% Complete]=100,”Completed”,
    [Status Date]>[Start] And [% Complete]=0,”Should Have Started”,
    [Status Date]>=[Finish] And [% Complete]<100,"Should Have Finished",
    [Status Date]< [Start],"Not Due To Start",
    [Status Date]< [Finish],"Not Due To Finish"
    )

    Reply
  4. i am looking for a formula that will populate hours from other resource hr assignments.

    My manager wants to create a new “resource” to see if people have been assigned for a task. Example : Under the Resource Usage view, we have all our employee assigned to certain projects. For certain projects we need a “meeting facilitator” and want to be able to see if we have double booked these meeting facilitators.

    in excel, i would use a formula similar to “populate this cell if this project for either one of these people has a value”

    Reply
  5. Very interesting.
    What if you share your file with someone else. That personn does not have the language installed on MSP stand alone (not server). The formula in the fielddoes not work any more. the Macros still works.

    Any idea on how to overcome that issue?

    Thanks

    Reply
  6. Hi , thanks for your time and useful information

    i need formula to calculate planed % complete

    Reply

Leave a Reply to Thach Cancel Reply

Your email address will not be published.

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Please complete this equation so we know you’re not a robot. *

Thanks for submitting your comment!
You must be logged in to comment.