Formula in custom fields -How do I note blank date

[Steve the large]

I am creating a formula for one of the flags to use it as an error indicator
if required fields are not all filled in.

I can not get the formula to recognize an unentered date.

I want flag19 to be true start1 has nothing in it. (start1 shows as NA).

I've used IIF as follows, and nothing seems to work.

IIF([start1]="",True,False)
IIF(isdate([start1]),True,False)
IIF(isnull([start1]),False,True)
IIF(isempty([start1]),True,False)
IIF([start1]="NA",True,False)

none of these work. How do you identify an un-filled cell? I was sure
isnull or isempty would work, but I'm not understanding something here.

I'm running MS Project 2003 version 11.1.2004.1707.15 SP1 on a win 2K
platform.

 

[Jan De Messemaeker]

Hi

Try [Start1]>2^30

 

[Steve the large]

Works like a charm, thanks.

I was going to try >0, but hadn't gotten around to it.

2 to the 30th power is an interesting number. It's not an intuitively
obvious solution, I mean I didn't hit my forehead and say "Wow -how did I
miss that?".

Any background on this solution, is it a known bug or what? I would think
isempty() and isnull() should both work, alas.

Thanks again, it's exactly the help I needed.

Hi

Try [Start1]>2^30

--
Jan De Messemaeker
Microsoft Project MVP
http://users.online.be/prom-ade

I am creating a formula for one of the flags to use it as an error
indicator
if required fields are not all filled in.

I can not get the formula to recognize an unentered date.

I want flag19 to be true start1 has nothing in it. (start1 shows as NA).

I've used IIF as follows, and nothing seems to work.

IIF([start1]="",True,False)
IIF(isdate([start1]),True,False)
IIF(isnull([start1]),False,True)
IIF(isempty([start1]),True,False)
IIF([start1]="NA",True,False)

none of these work. How do you identify an un-filled cell? I was sure
isnull or isempty would work, but I'm not understanding something here.

I'm running MS Project 2003 version 11.1.2004.1707.15 SP1 on a win 2K
platform.

 

[Jack Dahlgren]

The actual "value" of NA is 2^32-1 but you can use projdatevalue to test for
NA if you like.
For example:

iif([Baseline Finish]=projdatevalue("NA"), "Has Baseline", "No, Baseline")

You can read more about it here:

http://zo-d.com/blog/archives/programming/working-with-custom-field-formulas.html

--
Jack Dahlgren
Project Blog: http://zo-d.com/blog
Macros: http://masamiki.com/project/macros.htm
"docendo discimus"

Works like a charm, thanks.

I was going to try >0, but hadn't gotten around to it.

2 to the 30th power is an interesting number. It's not an intuitively
obvious solution, I mean I didn't hit my forehead and say "Wow -how did I
miss that?".

Any background on this solution, is it a known bug or what? I would think
isempty() and isnull() should both work, alas.

Thanks again, it's exactly the help I needed.

Hi

Try [Start1]>2^30

--
Jan De Messemaeker
Microsoft Project MVP
http://users.online.be/prom-ade
message

I am creating a formula for one of the flags to use it as an error
indicator
if required fields are not all filled in.

I can not get the formula to recognize an unentered date.

I want flag19 to be true start1 has nothing in it. (start1 shows as
NA).

I've used IIF as follows, and nothing seems to work.

IIF([start1]="",True,False)
IIF(isdate([start1]),True,False)
IIF(isnull([start1]),False,True)
IIF(isempty([start1]),True,False)
IIF([start1]="NA",True,False)

none of these work. How do you identify an un-filled cell? I was sure
isnull or isempty would work, but I'm not understanding something here.

I'm running MS Project 2003 version 11.1.2004.1707.15 SP1 on a win 2K
platform.

 

[Steve the large]

Thanks for the info, especially the link. I think I know why they assigned
2^32 - 1 to the value. In hex, this is FFFFFFFE, (for a 4byte wide value)
which everybody knows is a -1 in two's complement.

If the dates are not floating point, like they are in Excel, but instead
some number of positive days since m/d/yyyy (pick an old day), then a
negative one makes no sense and it's safe to use as flag in the variable.

Just a guess by an old-timer who wrote in C and Assmebly for twenty years.

The actual "value" of NA is 2^32-1 but you can use projdatevalue to test for
NA if you like.
For example:

iif([Baseline Finish]=projdatevalue("NA"), "Has Baseline", "No, Baseline")

You can read more about it here:

http://zo-d.com/blog/archives/programming/working-with-custom-field-formulas.html

--
Jack Dahlgren
Project Blog: http://zo-d.com/blog
Macros: http://masamiki.com/project/macros.htm
"docendo discimus"

Works like a charm, thanks.

I was going to try >0, but hadn't gotten around to it.

2 to the 30th power is an interesting number. It's not an intuitively
obvious solution, I mean I didn't hit my forehead and say "Wow -how did I
miss that?".

Any background on this solution, is it a known bug or what? I would think
isempty() and isnull() should both work, alas.

Thanks again, it's exactly the help I needed.

Hi

Try [Start1]>2^30

--
Jan De Messemaeker
Microsoft Project MVP
http://users.online.be/prom-ade
message
I am creating a formula for one of the flags to use it as an error
indicator
if required fields are not all filled in.

I can not get the formula to recognize an unentered date.

I want flag19 to be true start1 has nothing in it. (start1 shows as
NA).

I've used IIF as follows, and nothing seems to work.

IIF([start1]="",True,False)
IIF(isdate([start1]),True,False)
IIF(isnull([start1]),False,True)
IIF(isempty([start1]),True,False)
IIF([start1]="NA",True,False)

none of these work. How do you identify an un-filled cell? I was sure
isnull or isempty would work, but I'm not understanding something here.

I'm running MS Project 2003 version 11.1.2004.1707.15 SP1 on a win 2K
platform.

 

[Steve the large]

OOPS!!!

FFFFFFFE is a -2, in 2's complement, it's a -1 in one's complement. Sorry,
I haven't used 2's complement arithmetic since the 80's. Anyways, it is
common in some circles to use a "way-out, wacky number" that the user "can't"
put into a variable as a flag value. In this case FFFFFFFE is obviously the
flag for "NA". There might be other flags that also show up as NA but are
interpreted differently within the code. Guessing again, but I just can't
help it.

Thanks for the info, especially the link. I think I know why they assigned
2^32 - 1 to the value. In hex, this is FFFFFFFE, (for a 4byte wide value)
which everybody knows is a -1 in two's complement.

If the dates are not floating point, like they are in Excel, but instead
some number of positive days since m/d/yyyy (pick an old day), then a
negative one makes no sense and it's safe to use as flag in the variable.

Just a guess by an old-timer who wrote in C and Assmebly for twenty years.

The actual "value" of NA is 2^32-1 but you can use projdatevalue to test for
NA if you like.
For example:

iif([Baseline Finish]=projdatevalue("NA"), "Has Baseline", "No, Baseline")

You can read more about it here:

http://zo-d.com/blog/archives/programming/working-with-custom-field-formulas.html

--
Jack Dahlgren
Project Blog: http://zo-d.com/blog
Macros: http://masamiki.com/project/macros.htm
"docendo discimus"

Works like a charm, thanks.

I was going to try >0, but hadn't gotten around to it.

2 to the 30th power is an interesting number. It's not an intuitively
obvious solution, I mean I didn't hit my forehead and say "Wow -how did I
miss that?".

Any background on this solution, is it a known bug or what? I would think
isempty() and isnull() should both work, alas.

Thanks again, it's exactly the help I needed.



:

Hi

Try [Start1]>2^30

--
Jan De Messemaeker
Microsoft Project MVP
http://users.online.be/prom-ade
message
I am creating a formula for one of the flags to use it as an error
indicator
if required fields are not all filled in.

I can not get the formula to recognize an unentered date.

I want flag19 to be true start1 has nothing in it. (start1 shows as
NA).

I've used IIF as follows, and nothing seems to work.

IIF([start1]="",True,False)
IIF(isdate([start1]),True,False)
IIF(isnull([start1]),False,True)
IIF(isempty([start1]),True,False)
IIF([start1]="NA",True,False)

none of these work. How do you identify an un-filled cell? I was sure
isnull or isempty would work, but I'm not understanding something here.

I'm running MS Project 2003 version 11.1.2004.1707.15 SP1 on a win 2K
platform.