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 29, 2022
John,
The error occurs because "NA" is not a string, it is in fact a numerical representation of a date. There are a couple of ways to deal with it, this is the method I use:
Text1=IIf([Baseline Start]>50000,"no","yes")

John
  • JBLT-77's avatar
    JBLT-77
    Iron Contributor
    Aug 29, 2022
    Wow. Thats interesting who would have thought that. Thanks for the tip i'll give it a try.
    • John-project's avatar
      John-project
      Silver Contributor
      Aug 29, 2022
      John,
      Another way to handle it is:
      Text1=IIf([Baseline Start]=projDateValue("NA"),"no","yes")

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

        John-project Any idea why Project would store a numerical value for the baseline start date field instead of the value that actually displays "NA".  Thats a little strange.  Also I think there were other customer fields I have that evaluate whether a date field has "NA", and those didn't produce errors, but they were also different custom field types so maybe that had something to do with why the formulas worked.

Resources