Aug 28 2022 03:50 PM
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:
Aug 28 2022 05:09 PM
Aug 28 2022 07:55 PM
Aug 29 2022 06:42 AM
Aug 31 2022 04:55 AM
@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.
Aug 31 2022 07:07 AM - edited Aug 31 2022 08:55 AM
SolutionJohn,
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
Aug 31 2022 09:21 AM
@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.
Aug 31 2022 10:04 AM
Aug 31 2022 10:54 AM
Aug 31 2022 01:08 PM
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
Aug 31 2022 02:44 PM
@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.
Aug 31 2022 03:20 PM - edited Aug 31 2022 03:21 PM
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
Aug 31 2022 07:07 AM - edited Aug 31 2022 08:55 AM
SolutionJohn,
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