Forum Discussion

JBLT-77's avatar
JBLT-77
Iron Contributor
Aug 28, 2022
Solved

Getting #ERROR in Custom Text Field Using IIf Function For The False Argument

I created a simple IIF function in a custom text field to check whether a task is part of the project baseline using the Baseline Start field.  If the task is not part of baseline then the field disp...
  • John-project's avatar
    John-project
    Aug 31, 2022

    John,
    In the digital world dates are represented by numbers. For example, 8/29/22 12:00 PM is stored as 44802.54. String based fields (e.g. Text1) can of course have any string variable (e.g. "NA").

    What I don't fully understand is that the latest date possible with Project (current versions) is 12/31/2149 which is represented by: 91312.34, yet using the ">50000" trick still works for "NA".

     

    Interestingly enough, an "if" statement in VBA can directly detect "NA" in a date field. For example, the following code works fine:

    Sub testNA()
    For Each t In ActiveProject.Tasks
    t.Text1 = "yes"
    If (t.BaselineStart = "NA") Then t.Text1 = "no"
    Next t
    End Sub

    Oh the mysteries of Project 🙂
    John

John-project's avatar
John-project
Silver Contributor
Aug 31, 2022

JBLT-77 

Are you sure that works, because it doesn't for me. Something is fishy about your formula anyway because you show the false result to be a null string (i.e. not a numerical value) which is not valid for a number field. Here's what I get (substituting a 1 for your KPI number).

John

JBLT-77's avatar
JBLT-77
Iron Contributor
Aug 31, 2022

John-project All I know is I'm not getting an error.  Below is the custom field and formula, and what the view looks like.   Now if I do hover over the graphical indicator Cost KPI field.  For tasks where the Actual Start=NA, it does display #ERROR, but only when I hover over it.  Otherwise it just shows as blank.

 

Resources