Formula for CustomField which will check if date field [Start9] has a date entered - problem

[projectfun]

Hi Team,

Could you please help
I have a problem with "simple" formula for custom field which will check if field [Start9] has a date or no

For [Flag1] field I try following formula

IIf(([Start9])="NA",Yes,No)

IIf((InStr([Start9],"")<>0),Yes,No)

IIf(IsNull([Start9]),Yes,No)

IIf(IsEmpty([Start9]),Yes,No)

{Does not matter if I have a date or NA – I got no change  in [Flag1]}

I try other Custom field [Text29]

IIf(((InStr([Start9],"")<>0)),"OK","NotOK")

{If I got date in Start9 – I got OK but if I delete date in Start9 (have NA) ms Project show “#Error” in Text29 instead if “NotOk”}

[#2 · Accepted solution]

projectfun --

The easiest way to test if a default or custom field contains a date is to use the ProjDateValue function, such as in the following example:

IIf([Start9]=ProjDateValue("NA"),"No Date","Has a Date")

Hope this helps.

[#3]

Excellent !
Thank you

[#4]

projectfun --

You are more than welcome, my friend! Thanks much for marking my reply as the answer to your question.

[#5]

Dale has already answered your question perfectly. There are other ways to identify NA in a date field. In project environments where MS Project's versions in multiple languages are used, you need to replace "NA" string with the proper one in all formulas. MS Project does not touch the strings (characters enclosed with double quotes) while performing replacements based on the language of the product. So if you do not want to edit the formulas each time you pass mpp files containing date-related formulas to the other users in the team, you can use a simple comparison [Start9] = [Date10], provided that Date10 is always blank since it should be intentionally left blank/reserved for date comparisons. You can even rename it as NA, so that the expression becomes [Start9] = [NA]. But renaming is for us to make it easy to find it in the field list, otherwise MS Project does not keep the new name in the formula. At least, it will be easy while entering the formula for the first time.