Search code examples
formulaprojectproject-managementms-project

MS Project formula calculation returns inconsistent results

In MS Project Professional I have a custom field that returns the correct value...sometimes, no value at other times, and an #ERROR at still other times with no apparent rhyme or reason.

The goal: I need to capture the [Resource Names] field for use in an external application - easy enough - but when I have a fixed units task with limited resource units I need to exclude the "[##%]" portion of the name. Example: Sam[25%] but I need just, "Sam"

The formula: IIf(IsNumeric(InStr(1,[Resource Names],"[")),LEFT([Resource Names],Len([Resource Names])-5),[Resource Names])

The results are in summary:

  • Marian == M
  • Sam == #ERROR
  • Sam[25%] == Sam
  • IDNR == #ERROR
  • Core Dev == Cor
  • Bindu == Bindu
  • Bindu[50%] == Bindu
  • Michele == Mi
  • Michele[25%] == Michele
  • Disha == empty
  • Disha[33%] == Disha
  • Stuart[50%] == Stuart
  • Stuart == S

Strangely enough, Summary Tasks show no value which is correct.

The need: can someone help me fix the formula? Or, should I just suck it up and manually delete the offending brackets and numbers?

Solution

  • If you only ever have one resource assigned to a task, this formula will work: IIf(0=InStr(1,[Resource Names],"["),[Resource Names],Left([Resource Names],InStr(1,[Resource Names],"[")-1)).

    However, building a formula to handle more than one resource would be extremely tedious with the limited functions available. In that case a macro to update the field would work much better:

    Sub GetResourceNames()
    Dim t As Task
    For Each t In ActiveProject.Tasks
        Dim resList As String
        resList = vbNullString
        Dim a As Assignment
        For Each a In t.Assignments
            resList = resList & "," & a.Resource.Name
        Next a
        t.Text2 = Mid$(resList, 2)
    Next t
End Sub