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