SOLVED

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

Iron Contributor

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 displays "No", otherwise the field displays a value of "Yes".  For some reason, the field for tasks where the value should be displaying "No", are instead displaying #ERROR.  Why?  Does it have something to do with the NA value in the Baseline start field?  If the true part worked, I don't understand why the false part is resulting in #ERROR.  Here is the formula I'm using:

IIf([Baseline Start]="NA","No","Yes")

This is what project ends up displaying:

JBLT83_0-1661727018732.png

 

11 Replies
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
Wow. Thats interesting who would have thought that. Thanks for the tip i'll give it a try.
John,
Another way to handle it is:
Text1=IIf([Baseline Start]=projDateValue("NA"),"no","yes")

John

@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.

best response confirmed by JBLT-77 (Iron Contributor)
Solution

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 Would possibly using a different custom field type than a Text field be better to produce a Yes/No value based on whether a date field contains "NA".  It appears to work for number custom field types, but that field type can't produce a Yes/No Value.   I played around with the flag custom field type and I don't think that will work either.

John,
What makes you think it works for a custom Number field? It still generates an error if the test value equals "NA".

Why not just use the "tricks" I showed you. There are a few things that defy normal logic thinking when working with formulas in Project custom fields so the goal is to find a workaround and use it.

John
It works for Number fields because I have a custom field that displays graphical indicators. The formula I use for my custom Cost KPI field uses IIf ([Actual Start] <> "NA", [CPI], ""), and I don't get an error.

@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).

2022-08-31_13-05-14.png

John

@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.

JBLT83_0-1661982103914.pngJBLT83_1-1661982153957.png

 

John,
Switch your cost KPI to display "data" instead of an indicator. If it shows ERROR, then it's an error (i.e. data invalid), regardless of what you may think the indicator shows.

 

As a friend recently said, you can put lipstick on a pig, but it's still a pig.

John

1 best response

Accepted Solutions
best response confirmed by JBLT-77 (Iron Contributor)
Solution

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

View solution in original post