As a new way of managing projects for an enterprise, a project team that I work with is embracing the concept of Earned Value (EV). You know… the measuring of current performance to forecast, measure progress, and schedule the overruns at the earlier stages in a project; a basis for what future performance could look like. It’s a comprehensive analysis technique you’ll find a lot of PMPs living, bleeding, and breathing today.

With that, I’ve been asked to introduce the technical elements that embrace this methodology into a one-place-fits-all solution for our executive management, project leads, and general staff who are contributing to the overall project goal(s). Survey Says? Sharepoint!

The purpose and intentions for this collaboration surround the activity of project leaders reporting on high level tasks that are outlined as key deliverables. Giving an quick dashboard to Project Managers on how their projects are progressing, to project teams, and most importantly to the executive management / sponsorship of projects they approved. It’s a vehicle to communicate static and dynamic content as the earned value methology goes to work. Requirements? Glad You asked…

One Sharepoint List — Project Status

Columns : (Required — Req)

  1. Project    (Single Line of Text) – Req
  2. % Complete (Number as Percentage)
  3. Assigned To (Person or Group)
  4. Planned Start Date (Date & Time)
  5. Planned End Date (Date & Time)
  6. Actual Start date (Date & Time)
  7. Actual End Date (Date & Time)
  8. Team (Choice – Dropdown) – Req
  9. Comments (Multiple Lines of Text – 6 lines)
  10. Last Status Update – (Date & Time)
  11. Status/Planned (Calculated)
  12. Status/Project Lead (Calculated)
  13. Status/Planned Calc (Calculated)
  14. Status/Project Lead Update (Choice)
  15. Today (temporary use-case: I’ll explain later)
  16. Last Updated By (Person or Group)
  17. Created By (Person or Group)

A content web part with script used for the above calculated fields (will provide the scripting per request) to render  the following color indicators on the conditions of data fields.


Everyone will have different requirements, but You get the idea of what can be accomplished using Sharepoint as the medium to conveying results. The bulk of the work (aside from various Sharepoint list views with sorts, filters, and group-by settings configured) is in the Calculated fields; And quite frankly should be!

To generate the (2) color indicator fields (Status/Planned and Status/Project Lead columns) there were (2) other columns used, but hidden from the typical user views. These hidden columns did the underneath-the-scenes work and then other (2) exposed calculated fields did the color rendering on the basis of output generated in the hidden columns. The coloration does require source script to be in a content web part, and on the same page as the web part rendering the list of content (using the calculated fields).

The only one that I want to illustrate as an example of color-coded calculation is using the Status/Planned (exposed, calculated column) and the Status/Planned Calc (hidden, calculated column). The designated color spectrum means something to the business in terms of progress and/or risk. So starting with the Status/Planned Calc the following psuedo code needs to be developed…

  • Project Completion is 100% – Green
  • Project Completion is 0% ; Planned Start is greater than or equal to Current Date – Green
  • Project Completion is o%; Planned Start is less than Current Date – White
  • Project Completion is between 0% and 100%; Planned End less than Current Date – Red
  • Project Completion is between 0% and 100%; Planned End greater than Current Date – Green
  • Else Project is Orange

We then have a series of IF Statements to programmatically validate the above conditions.

 =IF([% Complete]=100%,”Green”,IF(AND(AND(Today>[Planned Start],Today<[Planned]),[% Complete]=0%),”Orange”,IF(AND([% Complete]=0%,[Planned Start]>=Today),”Green”,IF(AND([% Complete]=0%,[Planned Start]<Today),”Red”,IF(AND(AND([% Complete]>0%,[% Complete]<100%),[Planned End]<Today),”Red”,IF(AND(AND([% Complete]>0%,[% Complete]<100%),[Planned End]>Today),”Green”,”Orange”))))))

NOTE : In the above formula, there is a subtle issue with how You can get this calculated field to work without Sharepoint throwing an error (on save) regarding a subtle formula syntax. Microsoft does NOT allow you to introduce the [Today] function (aka Current Date) as a reference into any calculated field. Technically You can, but here’s the key to making it work. You have to throw Sharepoint’s attention off by creating another column in your list called “Today” and then create your formula with the [Today] reference made. At save, Sharepoint will NOT throw an error and will commit the formula to the calculated field. Now, you have to go back into the Sharepoint List Settings, and delete the [Today] column. And then the formula You wrote will no longer reference a column but instead the [Today] function. And if you find yourself in need of modifying the formula again, you’d have to re-introduce the [Today] column in the interim to do so. Just a tip worth noting. 

Now that you have your color calculation field defined, and working. It’s time to work on the Status/Planned (exposed, calculated field) for apply the approriate style tags for the users to see. This is where we introduce the javascript source code (available at request) into a content editor web part page (CEWP) that will live on the same page that loads the actualy Project List. Now we introduce the calculation material that will reference the Status/Planned Calc output that designates which color the, in this case, bullet point will be.

Status/Plannedformula is =”<DIV style=’font-weight:bold; font-size:20px; color:”&[Status/Planned Calc]&”;’></DIV>”

Once these (2) fields are configured, start entering data and start moving the values around to validate it is working as you’d expect. My hope is that You found this article useful and that it may have answered a number of questions You were looking to answer. If you have any questions, please let me know.

Thanks. Josef

A scenario I want to demonstrate to our internal user group is how we can build an application that will record Annual Individual Success Plan goals for each employee and Manager to have touch-base meetings to review their progress for the year. As a way to make it easy for data entry into a form, I want to remove extra work by the user(s) to entry their Windows AD Attributes, by making calculated fields that will query this data based on the ‘User :’ field.

For example, I would like to create (3) new calculated fields for this list called [Department], [Job Title], [E-Mail]. I want to make a calculated statement for each field to retrieve the respective data from Windows AD based on the ‘User :’ field.

So for ‘Department’. . . I want to select [Department] from WinAD where [User :] LIKE ‘xyz’
And ‘Job Title’ . . . select [Job Title] from WinAD where [User :] LIKE ‘xyz’
then ‘E-Mail’ . . . select [E-mail] from WinAD where[User :] LIKE ‘xyz’

Ideally if the [User :] changes to ‘123’ the [Department], [Job Title], and [E-Mail] fields will update with new content.

If this approach isn’t possible, is there a way to recommend Auto-Populating another List Called [User List] updating on a daily/hourly basis from WinAD and referencing calculated fields to that?

Thanks – J. Wright