none
MS Project - IsNull doesnt work RRS feed

  • Question

  • Hello,

    i wanna check if there is a Date typed in, in the start column.
    I cant check it with IfNull or IfNumeric using a if Function.

    IIf( IsNull( Anfang ) ; 1 ; 0 )

    Can anybody help?

    Thanks
    Wednesday, April 10, 2019 6:59 AM

Answers

  • Gandalf91,

    Yes, beginning with Project 2010, the Start and Finish fields are in fact Text fields - to support the manually-scheduled task mode.  You can test these entries with the following conditions:

    • IsDate([Start]) ... is TRUE for all automatically scheduled tasks and for all manual tasks with an entry that Project has interpreted as a date.  (For example, "today" and "tomorrow" in English language packs are interpreted as dates, but "yesterday" is not.)
    • Not ([Start]="") ... is TRUE for automatically scheduled tasks AND for all manual tasks with any entry in the Start column.  With "yesterday" in the Start field, this condition evaluates to TRUE; the previous condition is FALSE.

    You haven't specified "another column" enough to diagnose your error.  #Error codes often result when a formula returns a non-date value in a date field/column or a text value in a number field/column.  I suspect this is your issue.  For example, the Start1 field can hold ONLY date values. Then

    • IIf(IsDate([Start]),[Start],(ProjDateValue("NA"))) ... returns "NA" for any task with a non-date entry in the Start field.  (You'll need to adjust "NA" and the list separator for your local language).  No other text values can be entered into that field/column.

    Good luck, tom


    Thursday, April 11, 2019 2:14 PM

All replies

  • Gandalf91,

    Instead of IsNull, use a condition like this: ([Date1]<2^32-1).

    When a date is empty ("NA" in English versions), then Project actually stores a very large number, not a null value, in the field.  You could also use a very large date instead of 2^32-1.

    good luck, tom

    Wednesday, April 10, 2019 1:39 PM
  • Hey Tom,

    thanks for your help. I was successful with the Date1 column.
    But the column i wanna use is the Start Date of MS Project which isnt custom. There you can type in something else, like "test" as well.

    Do you have a result for this too?

    Thanks a lot !

    Edit:

    Hello again,

    for understanding. The problem is, i have another column (Calculate) which is calculating with the Start Date column. If there isnt any date, the Calculate column shows #Error. All i want to do is to avoid this message and show something else like "unscheduled" or just show a empty cell. Maybe there is another way to solve this problem.

    Thanks again!



    • Edited by Gandalf91 Thursday, April 11, 2019 7:55 AM
    Thursday, April 11, 2019 6:12 AM
  • Gandalf91,

    Yes, beginning with Project 2010, the Start and Finish fields are in fact Text fields - to support the manually-scheduled task mode.  You can test these entries with the following conditions:

    • IsDate([Start]) ... is TRUE for all automatically scheduled tasks and for all manual tasks with an entry that Project has interpreted as a date.  (For example, "today" and "tomorrow" in English language packs are interpreted as dates, but "yesterday" is not.)
    • Not ([Start]="") ... is TRUE for automatically scheduled tasks AND for all manual tasks with any entry in the Start column.  With "yesterday" in the Start field, this condition evaluates to TRUE; the previous condition is FALSE.

    You haven't specified "another column" enough to diagnose your error.  #Error codes often result when a formula returns a non-date value in a date field/column or a text value in a number field/column.  I suspect this is your issue.  For example, the Start1 field can hold ONLY date values. Then

    • IIf(IsDate([Start]),[Start],(ProjDateValue("NA"))) ... returns "NA" for any task with a non-date entry in the Start field.  (You'll need to adjust "NA" and the list separator for your local language).  No other text values can be entered into that field/column.

    Good luck, tom


    Thursday, April 11, 2019 2:14 PM
  • Hello Tom,

    thanks for your help! I did it.

    With a change to a text column and the function

     IIf(Not ([Anfang]="");Val(Int(((Anfang-43479,33)/14)+1));"Unscheduled")
    I still needed the Val for getting the same format.

    Thanks a lot!

    Friday, April 12, 2019 7:02 AM
  • Glad you got it sorted.  Kindly mark one of the responses as the answer, then this thread can be closed.

    tom

    Friday, April 12, 2019 1:09 PM