Forum Discussion
Getting #ERROR in Custom Text Field Using IIf Function For The False Argument
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
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
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.
JohnJohn-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.
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