Custom Formula "#ERROR"


S

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
 
Ad

Advertisements

C

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
 
S

scott_hanebutt

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

Scott

Chris Marriott said:
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 said:
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
 
J

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
..
scott_hanebutt said:
Thanks for the suggestion but it did not work. Instead of getting "#ERROR" I
get "4928781.28".

Scott

Chris Marriott said:
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 said:
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
 
C

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


JackD said:
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
..
scott_hanebutt said:
Thanks for the suggestion but it did not work. Instead of getting "#ERROR" I
get "4928781.28".

Scott

Chris Marriott said:
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
 
J

John

JackD said:
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
 
Ad

Advertisements

S

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

Chris Marriott said:
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


JackD said:
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
..
scott_hanebutt said:
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
 
J

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
..
John said:
JackD said:
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
 
Ad

Advertisements

J

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
..
scott_hanebutt said:
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

Chris Marriott said:
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


JackD said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top