Custom Formula "#ERROR"

[scott_hanebutt]

I have created a custom formula for number19. Basicly I am wanting to show
the difference between two date fields. My problem is that one of the other
date fields (Finsh1) is sometimes "NA". This causes "#ERROR" to display in
the Number19 field for that task. I need the tasks to instead be blank or if
that is not possible I need it to show "NA" instead. I do not want to use
VBA for this since I want the field to autocalculate. I think it may be
possible to accomplish this by changing the font to white but I would prefer
not to use that method if I can avoid it.

My formula is:
IIf([Finish1]="NA",0,[Finish1]-[Finish3])

Thanks,
Scott Hanebutt

 

[Chris Marriott]

Scott

Try this

IIf(CDBL(([Finish1] OR [Finish3])>6000),0,[Finish1]-[Finish3])

If Finish 1 or Finish 3 is empty then 0 is returned otherwise your formula
is run

I hope this helps
--
Regards


Chris Marriott - PMP MCSE MCDBA
UK - EPM Consultant & Trainer

 

[scott_hanebutt]

Thanks for the suggestion but it did not work. Instead of getting "#ERROR" I
get "4928781.28".

Scott

Scott

Try this

IIf(CDBL(([Finish1] OR [Finish3])>6000),0,[Finish1]-[Finish3])

If Finish 1 or Finish 3 is empty then 0 is returned otherwise your formula
is run

I hope this helps
--
Regards


Chris Marriott - PMP MCSE MCDBA
UK - EPM Consultant & Trainer

I have created a custom formula for number19. Basicly I am wanting to show
the difference between two date fields. My problem is that one of the other
date fields (Finsh1) is sometimes "NA". This causes "#ERROR" to display in
the Number19 field for that task. I need the tasks to instead be blank or if
that is not possible I need it to show "NA" instead. I do not want to use
VBA for this since I want the field to autocalculate. I think it may be
possible to accomplish this by changing the font to white but I would prefer
not to use that method if I can avoid it.

My formula is:
IIf([Finish1]="NA",0,[Finish1]-[Finish3])

Thanks,
Scott Hanebutt

 

[JackD]

Is there something wrong with 4928781.28?
There are a couple of ways to deal with "NA" in a calculated field.
This post describes how:
http://zo-d.com/blog/archives/programming/working-with-custom-field-formulas.html

iif([Finish] = projdatevalue("NA"), ...
is the key to sorting it out.

--
-Jack ... For Microsoft Project information and macro examples visit
http://masamiki.com/project
or http://zo-d.com/blog/index.html
..

Thanks for the suggestion but it did not work. Instead of getting "#ERROR" I
get "4928781.28".

Scott

Scott

Try this

IIf(CDBL(([Finish1] OR [Finish3])>6000),0,[Finish1]-[Finish3])

If Finish 1 or Finish 3 is empty then 0 is returned otherwise your formula
is run

I hope this helps
--
Regards


Chris Marriott - PMP MCSE MCDBA
UK - EPM Consultant & Trainer

I have created a custom formula for number19. Basicly I am wanting to show
the difference between two date fields. My problem is that one of the other
date fields (Finsh1) is sometimes "NA". This causes "#ERROR" to display in
the Number19 field for that task. I need the tasks to instead be blank or if
that is not possible I need it to show "NA" instead. I do not want to use
VBA for this since I want the field to autocalculate. I think it may be
possible to accomplish this by changing the font to white but I would prefer
not to use that method if I can avoid it.

My formula is:
IIf([Finish1]="NA",0,[Finish1]-[Finish3])

Thanks,
Scott Hanebutt

 

[Chris Marriott]

JackD

Thanks

My workaround only helped with a single field -- this is a much better
solution.


--
Regards


Chris Marriott - PMP MCSE MCDBA
UK - EPM Consultant & Trainer

Is there something wrong with 4928781.28?
There are a couple of ways to deal with "NA" in a calculated field.
This post describes how:
http://zo-d.com/blog/archives/programming/working-with-custom-field-formulas.html

iif([Finish] = projdatevalue("NA"), ...
is the key to sorting it out.

--
-Jack ... For Microsoft Project information and macro examples visit
http://masamiki.com/project
or http://zo-d.com/blog/index.html
..

Thanks for the suggestion but it did not work. Instead of getting "#ERROR" I
get "4928781.28".

Scott

Scott

Try this

IIf(CDBL(([Finish1] OR [Finish3])>6000),0,[Finish1]-[Finish3])

If Finish 1 or Finish 3 is empty then 0 is returned otherwise your formula
is run

I hope this helps
--
Regards


Chris Marriott - PMP MCSE MCDBA
UK - EPM Consultant & Trainer


:

I have created a custom formula for number19. Basicly I am wanting to show
the difference between two date fields. My problem is that one of the other
date fields (Finsh1) is sometimes "NA". This causes "#ERROR" to display in
the Number19 field for that task. I need the tasks to instead be blank or if
that is not possible I need it to show "NA" instead. I do not want to use
VBA for this since I want the field to autocalculate. I think it may be
possible to accomplish this by changing the font to white but I would prefer
not to use that method if I can avoid it.

My formula is:
IIf([Finish1]="NA",0,[Finish1]-[Finish3])

Thanks,
Scott Hanebutt

 

[John]

Is there something wrong with 4928781.28?
There are a couple of ways to deal with "NA" in a calculated field.
This post describes how:
http://zo-d.com/blog/archives/programming/working-with-custom-field-formulas.h
tml

iif([Finish] = projdatevalue("NA"), ...
is the key to sorting it out.

--
-Jack ... For Microsoft Project information and macro examples visit
http://masamiki.com/project
or http://zo-d.com/blog/index.html
Scott Hanebutt

Jack,
I tried every which way to decode the "NA" with string functions but no
luck. It is interesting that VBA string functions can read the "NA" in a
date field with no problem. Any idea as to why formulas are so fussy? It
looks like formula functions are about as robust as Events (i.e. often
flaky).

John

 

[scott_hanebutt]

I have figured it out. I ended up using a text field instead of a number
field so I could set it to "".

Thanks
Scott Hanebutt

JackD

Thanks

My workaround only helped with a single field -- this is a much better
solution.


--
Regards


Chris Marriott - PMP MCSE MCDBA
UK - EPM Consultant & Trainer

Is there something wrong with 4928781.28?
There are a couple of ways to deal with "NA" in a calculated field.
This post describes how:
http://zo-d.com/blog/archives/programming/working-with-custom-field-formulas.html

iif([Finish] = projdatevalue("NA"), ...
is the key to sorting it out.

--
-Jack ... For Microsoft Project information and macro examples visit
http://masamiki.com/project
or http://zo-d.com/blog/index.html
..

Thanks for the suggestion but it did not work. Instead of getting "#ERROR" I
get "4928781.28".

Scott

:

Scott

Try this

IIf(CDBL(([Finish1] OR [Finish3])>6000),0,[Finish1]-[Finish3])

If Finish 1 or Finish 3 is empty then 0 is returned otherwise your formula
is run

I hope this helps
--
Regards


Chris Marriott - PMP MCSE MCDBA
UK - EPM Consultant & Trainer


:

I have created a custom formula for number19. Basicly I am wanting to show
the difference between two date fields. My problem is that one of the other
date fields (Finsh1) is sometimes "NA". This causes "#ERROR" to display in
the Number19 field for that task. I need the tasks to instead be blank or if
that is not possible I need it to show "NA" instead. I do not want to use
VBA for this since I want the field to autocalculate. I think it may be
possible to accomplish this by changing the font to white but I would prefer
not to use that method if I can avoid it.

My formula is:
IIf([Finish1]="NA",0,[Finish1]-[Finish3])

Thanks,
Scott Hanebutt

 

[JackD]

I think that formulas are actually more robust than events.
There are just some oddities. This one relates to the data being stored
internally in a different way than it is displayed.


--
-Jack ... For Microsoft Project information and macro examples visit
http://masamiki.com/project
or http://zo-d.com/blog/index.html
..

Is there something wrong with 4928781.28?
There are a couple of ways to deal with "NA" in a calculated field.
This post describes how:
http://zo-d.com/blog/archives/programming/working-with-custom-field-formulas.h
tml

iif([Finish] = projdatevalue("NA"), ...
is the key to sorting it out.

--
-Jack ... For Microsoft Project information and macro examples visit
http://masamiki.com/project
or http://zo-d.com/blog/index.html
Scott Hanebutt

Jack,
I tried every which way to decode the "NA" with string functions but no
luck. It is interesting that VBA string functions can read the "NA" in a
date field with no problem. Any idea as to why formulas are so fussy? It
looks like formula functions are about as robust as Events (i.e. often
flaky).

John

 

[JackD]

I almost always use text fields.


--
-Jack ... For Microsoft Project information and macro examples visit
http://masamiki.com/project
or http://zo-d.com/blog/index.html
..

I have figured it out. I ended up using a text field instead of a number
field so I could set it to "".

Thanks
Scott Hanebutt

JackD

Thanks

My workaround only helped with a single field -- this is a much better
solution.


--
Regards


Chris Marriott - PMP MCSE MCDBA
UK - EPM Consultant & Trainer

Is there something wrong with 4928781.28?
There are a couple of ways to deal with "NA" in a calculated field.
This post describes how:
http://zo-d.com/blog/archives/programming/working-with-custom-field-formulas.html

iif([Finish] = projdatevalue("NA"), ...
is the key to sorting it out.

--
-Jack ... For Microsoft Project information and macro examples visit
http://masamiki.com/project
or http://zo-d.com/blog/index.html
..
Thanks for the suggestion but it did not work. Instead of getting
"#ERROR" I
get "4928781.28".

Scott

:

Scott

Try this

IIf(CDBL(([Finish1] OR [Finish3])>6000),0,[Finish1]-[Finish3])

If Finish 1 or Finish 3 is empty then 0 is returned otherwise your
formula
is run

I hope this helps
--
Regards


Chris Marriott - PMP MCSE MCDBA
UK - EPM Consultant & Trainer


:

I have created a custom formula for number19. Basicly I am wanting to
show
the difference between two date fields. My problem is that one of the
other
date fields (Finsh1) is sometimes "NA". This causes "#ERROR" to
display in
the Number19 field for that task. I need the tasks to instead be
blank or if
that is not possible I need it to show "NA" instead. I do not want to
use
VBA for this since I want the field to autocalculate. I think it may
be
possible to accomplish this by changing the font to white but I would
prefer
not to use that method if I can avoid it.

My formula is:
IIf([Finish1]="NA",0,[Finish1]-[Finish3])

Thanks,
Scott Hanebutt