Formula in Custom Fields

[Penny]

I have a custom Date Field #1. I would like another Custom Field #2 (not
sure if it should be date or text) that says: If there is a date in Custom
Date Field 1 then put yes In Customer Field #2.

Is this possible?

 

[JulieS]

Hi Penny,

Yes, it is. The Date field will show "NA" if there is no entry, so
your formula will need to test for that. In a Text or Flag field
enter the formula:

IIf([Date1] = ProjDateValue("NA"), "No", "Yes")

If Date1 has no date and therefore reads "NA", this will show "No",
if there is any date, it will show "Yes".

I hope this helps. Let us know how you get along.

Julie
Project MVP

Visit http://project.mvps.org/ for the FAQs and additional
information about Microsoft Project

 

[Jim Aksel]

You can use either a text field or a flag field. Your "specification" is not
complete -- what would you like to return if there is no date in Date1??

The following formula will return Yes or No as appropriate. However, with
careful programming, this column can be avoided.

IIf(ProjDateValue([Date1])=ProjDateValue("NA"),"Yes","No")

--
If this post was helpful, please consider rating it.

Jim Aksel, MVP

Check out my blog for more information:
http://www.msprojectblog.com

 

[Jim Aksel]

Hi Penny - Please note that Julie and I used opposite logic ... Julie has
directly answered your question. I answered the reciprocal question. "If
the value is NA, then return Yes"

Note the format of the "If" statement is
Iif( Test, True, False).

If the test evaluates to "True" then do what is in the "True" part,
otherwise do what is in the "False" part. Sometimes folks get confused that
when a test evaluates to "true" or "yes" that the formula returns "No" ...
just mind the syntax and remember what you are testing and all is well.

Something esle to think about ... we arrive at the conclusion that if there
is not NA in the Date1 field this means there must be a date there. Keep in
mind that is an assumption. Fortunately, MS Project will only allow a date
or NA to be enterred in that field. [The geek in me is revealed....]
--
If this post was helpful, please consider rating it.

Jim Aksel, MVP

Check out my blog for more information:
http://www.msprojectblog.com

 

[Penny]

This worked perfect JulieS. thanks so much.

Another question please. If a custom Text Field 5 has a Y in it then go and
grab the "name" description and put it in Text 5? Or would it have to go in
another field?

Penny

Hi Penny,

Yes, it is. The Date field will show "NA" if there is no entry, so
your formula will need to test for that. In a Text or Flag field
enter the formula:

IIf([Date1] = ProjDateValue("NA"), "No", "Yes")

If Date1 has no date and therefore reads "NA", this will show "No",
if there is any date, it will show "Yes".

I hope this helps. Let us know how you get along.

Julie
Project MVP

Visit http://project.mvps.org/ for the FAQs and additional
information about Microsoft Project

I have a custom Date Field #1. I would like another Custom Field
#2 (not
sure if it should be date or text) that says: If there is a date
in Custom
Date Field 1 then put yes In Customer Field #2.

Is this possible?

 

[JulieS]

You're welcome Penny glad to have helped and thanks for the
feedback. You cannot create a formula in a field (Text5) which
refers to itself. You'll have to add the information in another
field. You could hide Text5 in a view however if Text5 is a
calculated field.

I hope this helps. Let us know how you get along.

Julie
Project MVP

Visit http://project.mvps.org/ for the FAQs and additional
information about Microsoft Project

This worked perfect JulieS. thanks so much.

Another question please. If a custom Text Field 5 has a Y in it
then go and
grab the "name" description and put it in Text 5? Or would it
have to go in
another field?

Penny

Hi Penny,

Yes, it is. The Date field will show "NA" if there is no entry,
so
your formula will need to test for that. In a Text or Flag field
enter the formula:

IIf([Date1] = ProjDateValue("NA"), "No", "Yes")

If Date1 has no date and therefore reads "NA", this will show
"No",
if there is any date, it will show "Yes".

I hope this helps. Let us know how you get along.

Julie
Project MVP

Visit http://project.mvps.org/ for the FAQs and additional
information about Microsoft Project

I have a custom Date Field #1. I would like another Custom
Field
#2 (not
sure if it should be date or text) that says: If there is a
date
in Custom
Date Field 1 then put yes In Customer Field #2.

Is this possible?

 

[Penny]

Okay that is what I figured. I have the formula IIF([Text8]="Y","[Name]","
") now. But instead of going to the name field and returning the data in
that field it is returning [Name]. Can I do something to my formula so it
actually returns the description (or data) that is in the name column?

You're welcome Penny glad to have helped and thanks for the
feedback. You cannot create a formula in a field (Text5) which
refers to itself. You'll have to add the information in another
field. You could hide Text5 in a view however if Text5 is a
calculated field.

I hope this helps. Let us know how you get along.

Julie
Project MVP

Visit http://project.mvps.org/ for the FAQs and additional
information about Microsoft Project

This worked perfect JulieS. thanks so much.

Another question please. If a custom Text Field 5 has a Y in it
then go and
grab the "name" description and put it in Text 5? Or would it
have to go in
another field?

Penny

Hi Penny,

Yes, it is. The Date field will show "NA" if there is no entry,
so
your formula will need to test for that. In a Text or Flag field
enter the formula:

IIf([Date1] = ProjDateValue("NA"), "No", "Yes")

If Date1 has no date and therefore reads "NA", this will show
"No",
if there is any date, it will show "Yes".

I hope this helps. Let us know how you get along.

Julie
Project MVP

Visit http://project.mvps.org/ for the FAQs and additional
information about Microsoft Project


I have a custom Date Field #1. I would like another Custom
Field
#2 (not
sure if it should be date or text) that says: If there is a
date
in Custom
Date Field 1 then put yes In Customer Field #2.

Is this possible?

 

[Mike Glen]

Hi Penny,

Try removing the quotes around [Name]. It is the quotes that's creating it
as text.

Mike Glen
Project MVP
See http://tinyurl.com/2xbhc for my free Project Tutorials

Okay that is what I figured. I have the formula
IIF([Text8]="Y","[Name]"," ") now. But instead of going to the name
field and returning the data in that field it is returning [Name].
Can I do something to my formula so it actually returns the
description (or data) that is in the name column?

You're welcome Penny glad to have helped and thanks for the
feedback. You cannot create a formula in a field (Text5) which
refers to itself. You'll have to add the information in another
field. You could hide Text5 in a view however if Text5 is a
calculated field.

I hope this helps. Let us know how you get along.

Julie
Project MVP

Visit http://project.mvps.org/ for the FAQs and additional
information about Microsoft Project

This worked perfect JulieS. thanks so much.

Another question please. If a custom Text Field 5 has a Y in it
then go and
grab the "name" description and put it in Text 5? Or would it
have to go in
another field?

Penny

:

Hi Penny,

Yes, it is. The Date field will show "NA" if there is no entry,
so
your formula will need to test for that. In a Text or Flag field
enter the formula:

IIf([Date1] = ProjDateValue("NA"), "No", "Yes")

If Date1 has no date and therefore reads "NA", this will show
"No",
if there is any date, it will show "Yes".

I hope this helps. Let us know how you get along.

Julie
Project MVP

Visit http://project.mvps.org/ for the FAQs and additional
information about Microsoft Project


I have a custom Date Field #1. I would like another Custom
Field
#2 (not
sure if it should be date or text) that says: If there is a
date
in Custom
Date Field 1 then put yes In Customer Field #2.

Is this possible?

 

[Penny]

That's it!!! Thanks so much.

Hi Penny,

Try removing the quotes around [Name]. It is the quotes that's creating it
as text.

Mike Glen
Project MVP
See http://tinyurl.com/2xbhc for my free Project Tutorials

Okay that is what I figured. I have the formula
IIF([Text8]="Y","[Name]"," ") now. But instead of going to the name
field and returning the data in that field it is returning [Name].
Can I do something to my formula so it actually returns the
description (or data) that is in the name column?

You're welcome Penny glad to have helped and thanks for the
feedback. You cannot create a formula in a field (Text5) which
refers to itself. You'll have to add the information in another
field. You could hide Text5 in a view however if Text5 is a
calculated field.

I hope this helps. Let us know how you get along.

Julie
Project MVP

Visit http://project.mvps.org/ for the FAQs and additional
information about Microsoft Project

This worked perfect JulieS. thanks so much.

Another question please. If a custom Text Field 5 has a Y in it
then go and
grab the "name" description and put it in Text 5? Or would it
have to go in
another field?

Penny

:

Hi Penny,

Yes, it is. The Date field will show "NA" if there is no entry,
so
your formula will need to test for that. In a Text or Flag field
enter the formula:

IIf([Date1] = ProjDateValue("NA"), "No", "Yes")

If Date1 has no date and therefore reads "NA", this will show
"No",
if there is any date, it will show "Yes".

I hope this helps. Let us know how you get along.

Julie
Project MVP

Visit http://project.mvps.org/ for the FAQs and additional
information about Microsoft Project


I have a custom Date Field #1. I would like another Custom
Field
#2 (not
sure if it should be date or text) that says: If there is a
date
in Custom
Date Field 1 then put yes In Customer Field #2.

Is this possible?

 

[Mike Glen]

You're welcome, Penny

Mike Glen
MS Project MVP
See http://tinyurl.com/2xbhc for my free Project Tutorials

That's it!!! Thanks so much.

Hi Penny,

Try removing the quotes around [Name]. It is the quotes that's
creating it as text.

Mike Glen
Project MVP
See http://tinyurl.com/2xbhc for my free Project Tutorials

Okay that is what I figured. I have the formula
IIF([Text8]="Y","[Name]"," ") now. But instead of going to the name
field and returning the data in that field it is returning [Name].
Can I do something to my formula so it actually returns the
description (or data) that is in the name column?


:

You're welcome Penny glad to have helped and thanks for the
feedback. You cannot create a formula in a field (Text5) which
refers to itself. You'll have to add the information in another
field. You could hide Text5 in a view however if Text5 is a
calculated field.

I hope this helps. Let us know how you get along.

Julie
Project MVP

Visit http://project.mvps.org/ for the FAQs and additional
information about Microsoft Project

This worked perfect JulieS. thanks so much.

Another question please. If a custom Text Field 5 has a Y in it
then go and
grab the "name" description and put it in Text 5? Or would it
have to go in
another field?

Penny

:

Hi Penny,

Yes, it is. The Date field will show "NA" if there is no entry,
so
your formula will need to test for that. In a Text or Flag field
enter the formula:

IIf([Date1] = ProjDateValue("NA"), "No", "Yes")

If Date1 has no date and therefore reads "NA", this will show
"No",
if there is any date, it will show "Yes".

I hope this helps. Let us know how you get along.

Julie
Project MVP

Visit http://project.mvps.org/ for the FAQs and additional
information about Microsoft Project


I have a custom Date Field #1. I would like another Custom
Field
#2 (not
sure if it should be date or text) that says: If there is a
date
in Custom
Date Field 1 then put yes In Customer Field #2.

Is this possible?

 

[JulieS]

Thanks for picking up Mike! Glad you got it to work out Penny.

Julie

That's it!!! Thanks so much.

Hi Penny,

Try removing the quotes around [Name]. It is the quotes that's
creating it
as text.

Mike Glen
Project MVP
See http://tinyurl.com/2xbhc for my free Project Tutorials

Okay that is what I figured. I have the formula
IIF([Text8]="Y","[Name]"," ") now. But instead of going to the
name
field and returning the data in that field it is returning
[Name].
Can I do something to my formula so it actually returns the
description (or data) that is in the name column?


:

You're welcome Penny glad to have helped and thanks for the
feedback. You cannot create a formula in a field (Text5)
which
refers to itself. You'll have to add the information in
another
field. You could hide Text5 in a view however if Text5 is a
calculated field.

I hope this helps. Let us know how you get along.

Julie
Project MVP

Visit http://project.mvps.org/ for the FAQs and additional
information about Microsoft Project

This worked perfect JulieS. thanks so much.

Another question please. If a custom Text Field 5 has a Y in
it
then go and
grab the "name" description and put it in Text 5? Or would
it
have to go in
another field?

Penny

:

Hi Penny,

Yes, it is. The Date field will show "NA" if there is no
entry,
so
your formula will need to test for that. In a Text or Flag
field
enter the formula:

IIf([Date1] = ProjDateValue("NA"), "No", "Yes")

If Date1 has no date and therefore reads "NA", this will
show
"No",
if there is any date, it will show "Yes".

I hope this helps. Let us know how you get along.

Julie
Project MVP

Visit http://project.mvps.org/ for the FAQs and additional
information about Microsoft Project


I have a custom Date Field #1. I would like another Custom
Field
#2 (not
sure if it should be date or text) that says: If there is a
date
in Custom
Date Field 1 then put yes In Customer Field #2.

Is this possible?