Customize a field - formulas

[Julie G]

I am trying to customize a field in Project 2002, in
Excel the formula is: =IF(D2<>"NA","Complete",IF
(E2>0,"Late", "On Track"))

but I can not get this formula working in project, I only
seem to be able to use one IF statement i.e 'IF
(E2>0,"Late", "On Track"))' If I try nesting an IF
statement or using an 'OR' the second IF statement does
not work.

Any ideas how to overcome this?

 

[Dale Howard [MVP]]

Julie G --

In Project 2002, what fields do you want to test in your formula using the
IF statements? It looks like you might be testing for Finish Variance, but
I can't tell for sure. Let me know.

--
Dale A. Howard [MVP]
Enterprise Project Trainer/Consultant
Denver, Colorado
http://www.msprojectexperts.com
"We wrote the book on Project Server"

 

[Julie G]

Dale,

The formula I am trying to use is as follows:
IIf([Actual Finish]<>"NA","Complete",IIf([Text6]
0,"Late","On Track"))

Text6 is a variance field I have set up to calculate the
variance between a forecast date (Forecast Finish) and a
Baseline Finish date. The Forecast Finish date is one of
the Project Finish fields.

If I use 'IIf([Text6]>0,"Late","On Track")' then this
seems to work fine, it's only when I try to add the
additional If statement of 'IF [Actual Finish] ..' that
the formula fails. Again if I use the 'IIf([Actual Finish]

 

[Dale Howard [MVP]]

Julie G --

I created the following formula to do what I think you are wanting to do,
which is to track the progress of any task:

IIf([% Complete]=100,"Complete",IIf([Finish Variance]>0,"Late","On Track"))

I created this formula in the Text1 field and renamed the field Progress. I
have tested this formula in a sample project, and the formula calculates
everything correctly. The first part of the formula determines if the task
is finished, testing for Percent Complete = 100%, and if so, it displays
"Complete" in the field. The second half of the formula tests the Finish
Variance field for a value greater than 0. Finish Variance is a calculated
field using the formula Finish - Baseline Finish. If the value is positive,
the task is slipping and the field displays "Late." Otherw Anything else
means the task is either on schedule, or may finish early, and the field
displays "On Track."

What I did not understand from your last post is whether the Forecast Finish
date is actually the value in the default Finish field, or if it is a custom
Finish field in which the PM enters a date manually. If the Forecast Finish
field is actually the default Finish field, then the above formula should
work fine for you. Otherwise, you will need to tell me more about the
Forecast Finish field.

Dale,

The formula I am trying to use is as follows:
IIf([Actual Finish]<>"NA","Complete",IIf([Text6]
0,"Late","On Track"))

Text6 is a variance field I have set up to calculate the
variance between a forecast date (Forecast Finish) and a
Baseline Finish date. The Forecast Finish date is one of
the Project Finish fields.

If I use 'IIf([Text6]>0,"Late","On Track")' then this
seems to work fine, it's only when I try to add the
additional If statement of 'IF [Actual Finish] ..' that
the formula fails. Again if I use the 'IIf([Actual Finish]

-----Original Message-----
Julie G --

In Project 2002, what fields do you want to test in your formula using the
IF statements? It looks like you might be testing for Finish Variance, but
I can't tell for sure. Let me know.

--
Dale A. Howard [MVP]
Enterprise Project Trainer/Consultant
Denver, Colorado
http://www.msprojectexperts.com
"We wrote the book on Project Server"





.

 

[Julie G]

Dale,

Many thanks for your help, I have tried out the formula
and given it a quick test and all now seems to be working
fine. Is there any specific reason why using my original
formula failed?

Thanks Again.

-----Original Message-----
Julie G --

I created the following formula to do what I think you are wanting to do,
which is to track the progress of any task:

IIf([% Complete]=100,"Complete",IIf([Finish Variance]
0,"Late","On Track"))

I created this formula in the Text1 field and renamed the field Progress. I
have tested this formula in a sample project, and the formula calculates
everything correctly. The first part of the formula determines if the task
is finished, testing for Percent Complete = 100%, and if so, it displays
"Complete" in the field. The second half of the formula tests the Finish
Variance field for a value greater than 0. Finish Variance is a calculated
field using the formula Finish - Baseline Finish. If the value is positive,
the task is slipping and the field displays "Late." Otherw Anything else
means the task is either on schedule, or may finish early, and the field
displays "On Track."

What I did not understand from your last post is whether the Forecast Finish
date is actually the value in the default Finish field, or if it is a custom
Finish field in which the PM enters a date manually. If the Forecast Finish
field is actually the default Finish field, then the above formula should
work fine for you. Otherwise, you will need to tell me more about the
Forecast Finish field.

Dale,

The formula I am trying to use is as follows:
IIf([Actual Finish]<>"NA","Complete",IIf([Text6]
0,"Late","On Track"))

Text6 is a variance field I have set up to calculate the
variance between a forecast date (Forecast Finish) and a
Baseline Finish date. The Forecast Finish date is one of
the Project Finish fields.

If I use 'IIf([Text6]>0,"Late","On Track")' then this
seems to work fine, it's only when I try to add the
additional If statement of 'IF [Actual Finish] ..' that
the formula fails. Again if I use the 'IIf([Actual Finish]
<>"NA","Complete"...' it seems to work fine on it's own.

-----Original Message-----
Julie G --

In Project 2002, what fields do you want to test in

your
formula using the

IF statements? It looks like you might be testing for Finish Variance, but
I can't tell for sure. Let me know.

--
Dale A. Howard [MVP]
Enterprise Project Trainer/Consultant
Denver, Colorado
http://www.msprojectexperts.com
"We wrote the book on Project Server"


"Julie G" <[email protected]> wrote

in
message

I am trying to customize a field in Project 2002, in
Excel the formula is: =IF(D2<>"NA","Complete",IF
(E2>0,"Late", "On Track"))

but I can not get this formula working in project, I only
seem to be able to use one IF statement i.e 'IF
(E2>0,"Late", "On Track"))' If I try nesting an IF
statement or using an 'OR' the second IF statement does
not work.

Any ideas how to overcome this?


.

.

 

[RickD]

Hi Dale; I must be having a bad hair day today! Why is it that this formula
works for a local project, but not when Enterprise Custom Fields are used?

Julie G --

I created the following formula to do what I think you are wanting to do,
which is to track the progress of any task:

IIf([% Complete]=100,"Complete",IIf([Finish Variance]>0,"Late","On Track"))

I created this formula in the Text1 field and renamed the field Progress. I
have tested this formula in a sample project, and the formula calculates
everything correctly. The first part of the formula determines if the task
is finished, testing for Percent Complete = 100%, and if so, it displays
"Complete" in the field. The second half of the formula tests the Finish
Variance field for a value greater than 0. Finish Variance is a calculated
field using the formula Finish - Baseline Finish. If the value is positive,
the task is slipping and the field displays "Late." Otherw Anything else
means the task is either on schedule, or may finish early, and the field
displays "On Track."

What I did not understand from your last post is whether the Forecast Finish
date is actually the value in the default Finish field, or if it is a custom
Finish field in which the PM enters a date manually. If the Forecast Finish
field is actually the default Finish field, then the above formula should
work fine for you. Otherwise, you will need to tell me more about the
Forecast Finish field.

Dale,

The formula I am trying to use is as follows:
IIf([Actual Finish]<>"NA","Complete",IIf([Text6]
0,"Late","On Track"))

Text6 is a variance field I have set up to calculate the
variance between a forecast date (Forecast Finish) and a
Baseline Finish date. The Forecast Finish date is one of
the Project Finish fields.

If I use 'IIf([Text6]>0,"Late","On Track")' then this
seems to work fine, it's only when I try to add the
additional If statement of 'IF [Actual Finish] ..' that
the formula fails. Again if I use the 'IIf([Actual Finish]

-----Original Message-----
Julie G --

In Project 2002, what fields do you want to test in your formula using the
IF statements? It looks like you might be testing for Finish Variance, but
I can't tell for sure. Let me know.

--
Dale A. Howard [MVP]
Enterprise Project Trainer/Consultant
Denver, Colorado
http://www.msprojectexperts.com
"We wrote the book on Project Server"


I am trying to customize a field in Project 2002, in
Excel the formula is: =IF(D2<>"NA","Complete",IF
(E2>0,"Late", "On Track"))

but I can not get this formula working in project, I only
seem to be able to use one IF statement i.e 'IF
(E2>0,"Late", "On Track"))' If I try nesting an IF
statement or using an 'OR' the second IF statement does
not work.

Any ideas how to overcome this?


.

 

[RickD]

To expand upon my rather vague posting, I get "error" on every task when
implementing the exact formula using Server and Enterprise Text fields.

I also attempted to use traffic lights to identify whether a task was early
or on time, or late. I used the Finish Variance as the basis of my traffic
light and got very strange results. I changed the view to print values and
the results were in deed strange.

I set Enterprise Duration1 to [Finish Variance]. The value displayed was
5.87 days. I inserted a colum to display Finish Variance, and the value was
-1.13 days.

Hi Dale; I must be having a bad hair day today! Why is it that this formula
works for a local project, but not when Enterprise Custom Fields are used?

Julie G --

I created the following formula to do what I think you are wanting to do,
which is to track the progress of any task:

IIf([% Complete]=100,"Complete",IIf([Finish Variance]>0,"Late","On Track"))

I created this formula in the Text1 field and renamed the field Progress. I
have tested this formula in a sample project, and the formula calculates
everything correctly. The first part of the formula determines if the task
is finished, testing for Percent Complete = 100%, and if so, it displays
"Complete" in the field. The second half of the formula tests the Finish
Variance field for a value greater than 0. Finish Variance is a calculated
field using the formula Finish - Baseline Finish. If the value is positive,
the task is slipping and the field displays "Late." Otherw Anything else
means the task is either on schedule, or may finish early, and the field
displays "On Track."

What I did not understand from your last post is whether the Forecast Finish
date is actually the value in the default Finish field, or if it is a custom
Finish field in which the PM enters a date manually. If the Forecast Finish
field is actually the default Finish field, then the above formula should
work fine for you. Otherwise, you will need to tell me more about the
Forecast Finish field.

Dale,

The formula I am trying to use is as follows:
IIf([Actual Finish]<>"NA","Complete",IIf([Text6]
0,"Late","On Track"))

Text6 is a variance field I have set up to calculate the
variance between a forecast date (Forecast Finish) and a
Baseline Finish date. The Forecast Finish date is one of
the Project Finish fields.

If I use 'IIf([Text6]>0,"Late","On Track")' then this
seems to work fine, it's only when I try to add the
additional If statement of 'IF [Actual Finish] ..' that
the formula fails. Again if I use the 'IIf([Actual Finish]
<>"NA","Complete"...' it seems to work fine on it's own.


-----Original Message-----
Julie G --

In Project 2002, what fields do you want to test in your
formula using the
IF statements? It looks like you might be testing for
Finish Variance, but
I can't tell for sure. Let me know.

--
Dale A. Howard [MVP]
Enterprise Project Trainer/Consultant
Denver, Colorado
http://www.msprojectexperts.com
"We wrote the book on Project Server"


message
I am trying to customize a field in Project 2002, in
Excel the formula is: =IF(D2<>"NA","Complete",IF
(E2>0,"Late", "On Track"))

but I can not get this formula working in project, I
only
seem to be able to use one IF statement i.e 'IF
(E2>0,"Late", "On Track"))' If I try nesting an IF
statement or using an 'OR' the second IF statement does
not work.

Any ideas how to overcome this?


.

 

[RickD]

Sorry folks. Forget about both of my posts. I have some kind of data
corruption in the specific project I was using. A new project I added behaves
correctly to identify status and health (schedule, budget and effort).

Julie G --

I created the following formula to do what I think you are wanting to do,
which is to track the progress of any task:

IIf([% Complete]=100,"Complete",IIf([Finish Variance]>0,"Late","On Track"))

I created this formula in the Text1 field and renamed the field Progress. I
have tested this formula in a sample project, and the formula calculates
everything correctly. The first part of the formula determines if the task
is finished, testing for Percent Complete = 100%, and if so, it displays
"Complete" in the field. The second half of the formula tests the Finish
Variance field for a value greater than 0. Finish Variance is a calculated
field using the formula Finish - Baseline Finish. If the value is positive,
the task is slipping and the field displays "Late." Otherw Anything else
means the task is either on schedule, or may finish early, and the field
displays "On Track."

What I did not understand from your last post is whether the Forecast Finish
date is actually the value in the default Finish field, or if it is a custom
Finish field in which the PM enters a date manually. If the Forecast Finish
field is actually the default Finish field, then the above formula should
work fine for you. Otherwise, you will need to tell me more about the
Forecast Finish field.

Dale,

The formula I am trying to use is as follows:
IIf([Actual Finish]<>"NA","Complete",IIf([Text6]
0,"Late","On Track"))

Text6 is a variance field I have set up to calculate the
variance between a forecast date (Forecast Finish) and a
Baseline Finish date. The Forecast Finish date is one of
the Project Finish fields.

If I use 'IIf([Text6]>0,"Late","On Track")' then this
seems to work fine, it's only when I try to add the
additional If statement of 'IF [Actual Finish] ..' that
the formula fails. Again if I use the 'IIf([Actual Finish]

-----Original Message-----
Julie G --

In Project 2002, what fields do you want to test in your formula using the
IF statements? It looks like you might be testing for Finish Variance, but
I can't tell for sure. Let me know.

--
Dale A. Howard [MVP]
Enterprise Project Trainer/Consultant
Denver, Colorado
http://www.msprojectexperts.com
"We wrote the book on Project Server"


I am trying to customize a field in Project 2002, in
Excel the formula is: =IF(D2<>"NA","Complete",IF
(E2>0,"Late", "On Track"))

but I can not get this formula working in project, I only
seem to be able to use one IF statement i.e 'IF
(E2>0,"Late", "On Track"))' If I try nesting an IF
statement or using an 'OR' the second IF statement does
not work.

Any ideas how to overcome this?


.