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