Formula issue

[schemr]

hi,
i am tryin to write a formula but i have a question based on it. what im
tryin to write is this: If current date is > finish date by a certain number
of days display yellow, if its bigger by a higher number of days show red,
etc. i hope the question is easy to understand. leave comment if the question
isnt clear and i will try to clarify. thanx in advance

schemr

 

[Brian K - Project MVP]

hi,
i am tryin to write a formula but i have a question based on it. what im
tryin to write is this: If current date is > finish date by a certain
number
of days display yellow, if its bigger by a higher number of days show red,
etc. i hope the question is easy to understand. leave comment if the
question
isnt clear and i will try to clarify. thanx in advance

schemr

IIf([% Complete]=100,"Complete",IIf((ProjDateDiff( datevalue([Finish]),
Datevalue([Current Date]), [Project Calendar])/480)>4,"Late by 5 or
more",IIf((ProjDateDiff( datevalue([Finish]), Datevalue([Current Date]),
[Project Calendar])/480)>0,"Late by 1-4","Not Late")))

 

[schemr]

Thank You Very Much Brian K......VERY Helpful and much appreciated.. wasnt
expecting the actual code but just the functions needed......wanted to fig
out myself but hey...now i can work on all the OTHER PROBLEMS i have on my
desk.....once again Thank you. ....

schemr

hi,
i am tryin to write a formula but i have a question based on it. what im
tryin to write is this: If current date is > finish date by a certain
number
of days display yellow, if its bigger by a higher number of days show red,
etc. i hope the question is easy to understand. leave comment if the
question
isnt clear and i will try to clarify. thanx in advance

schemr

IIf([% Complete]=100,"Complete",IIf((ProjDateDiff( datevalue([Finish]),
Datevalue([Current Date]), [Project Calendar])/480)>4,"Late by 5 or
more",IIf((ProjDateDiff( datevalue([Finish]), Datevalue([Current Date]),
[Project Calendar])/480)>0,"Late by 1-4","Not Late")))

 

[Brian K - Project MVP]

Thank You Very Much Brian K......VERY Helpful and much appreciated.. wasnt
expecting the actual code but just the functions needed......wanted to fig
out myself but hey...now i can work on all the OTHER PROBLEMS i have on my
desk.....once again Thank you. ....

schemr

Glad to help. But if you want you should break it down and see how it
works. make some changed to it and see what happens. play around so you
wll have a firmer grasp on it. Good luck with that other stuff on your
desk!

 

[Marc]

I just came across this post and I would love to use this formula, so I
inserted it into my Project Plan. However, it doesn't work. The only values
I get are either '0' or '#ERROR'. My company uses Project 2000... could that
be the issue?

 

[Marc]

Nevermind! I figured it out and I am all set.... thanks for the formula!!

 

[Janet BN]

Hi Marc,

I'm having a brain freeze and I suspect it's the same original problem you
had. How can you divide a text field by 480?

I'm obviously missing something and I know it has been a long time since you
worked on this, but can you tell me the answer. I've been working on this
for a while now and running out of time.

Any help appreciated,

Cheers,

 

[John]

Hi Marc,

I'm having a brain freeze and I suspect it's the same original problem you
had. How can you divide a text field by 480?

I'm obviously missing something and I know it has been a long time since you
worked on this, but can you tell me the answer. I've been working on this
for a while now and running out of time.

Any help appreciated,

Cheers,

Janet,
First of all, what exactly are you trying to do?

As far as dividing a text field by "480", it depends on what is in the
text field. If it is strictly text (e.g. "Bob"), then obviously not. On
the other hand, if the text field is a customized field with a formula,
it is a simple matter of putting the correct formula in the custom
field. For example, let's say you wish to use a custom text field to
convert the Duration field, (which is actually stored internally in
minutes), to hours, the formula would be:
Text1=[Duration]/60

Hope this helps.
John
Project MVP

 

[Janet BN]

Thanks John,
I was trying to use the formula Brian K wrote in response to schemr (see
other threads). Basically, I am trying to calculate the days since the task
was supposed to have started.
We are building a template with lots of slack built in, but need to be able
to identify if the task hasn't started, how long since it should have. So we
can manage the slack across multiple projects - upwards of 50 at a time
(Construction).

Brian K's formula is supposed to do that I think, but I keep getting #ERROR
and cannot see where it is - my assumption was the "Project Calendar" field.
Formula below.

IIf([% Complete]=100,"Complete",IIf((ProjDateDiff( datevalue([Finish]),
Datevalue([Current Date]), [Project Calendar])/480)>4,"Late by 5 or
more",IIf((ProjDateDiff( datevalue([Finish]), Datevalue([Current Date]),
[Project Calendar])/480)>0,"Late by 1-4","Not Late")))

Any help appreciated.

Hi Marc,

I'm having a brain freeze and I suspect it's the same original problem you
had. How can you divide a text field by 480?

I'm obviously missing something and I know it has been a long time since you
worked on this, but can you tell me the answer. I've been working on this
for a while now and running out of time.

Any help appreciated,

Cheers,

Janet,
First of all, what exactly are you trying to do?

As far as dividing a text field by "480", it depends on what is in the
text field. If it is strictly text (e.g. "Bob"), then obviously not. On
the other hand, if the text field is a customized field with a formula,
it is a simple matter of putting the correct formula in the custom
field. For example, let's say you wish to use a custom text field to
convert the Duration field, (which is actually stored internally in
minutes), to hours, the formula would be:
Text1=[Duration]/60

Hope this helps.
John
Project MVP

 

[John]

Thanks John,
I was trying to use the formula Brian K wrote in response to schemr (see
other threads). Basically, I am trying to calculate the days since the task
was supposed to have started.
We are building a template with lots of slack built in, but need to be able
to identify if the task hasn't started, how long since it should have. So we
can manage the slack across multiple projects - upwards of 50 at a time
(Construction).

Brian K's formula is supposed to do that I think, but I keep getting #ERROR
and cannot see where it is - my assumption was the "Project Calendar" field.
Formula below.

IIf([% Complete]=100,"Complete",IIf((ProjDateDiff( datevalue([Finish]),
Datevalue([Current Date]), [Project Calendar])/480)>4,"Late by 5 or
more",IIf((ProjDateDiff( datevalue([Finish]), Datevalue([Current Date]),
[Project Calendar])/480)>0,"Late by 1-4","Not Late")))

Any help appreciated.

Janet,
You're right, the formula is flawed. I'm surprised that didn't come out
in the original thread, although I admit I didn't actually read the full
thread. Brian's formula includes a field that doesn't exist and you're
also right that it is the "Project Calendar" - no such field. However,
in the formula, the default value is the active project's calendar so it
can be left out entirely unless you want to specify a specific calendar
on which to base the difference in time. If the calendar is specified,
it should be listed as a string (e.g. "standard" for the standard
calendar).

Assuming you do not have any need for a specific calendar, use the
following formula (I tested it, it now works):

IIf([%Complete]=100,"Complete",IIf((ProjDateDiff(datevalue([Finish]),Date
value([Current Date]))/480)>4,"Late by 5 or
more",IIf((ProjDateDiff(datevalue([Finish]),Datevalue([Current
Date]))/480)>0,"Late by 1-4","Not Late")))

Hope this helps.
John
Project MVP

Hi Marc,

I'm having a brain freeze and I suspect it's the same original problem
you
had. How can you divide a text field by 480?

I'm obviously missing something and I know it has been a long time since
you
worked on this, but can you tell me the answer. I've been working on
this
for a while now and running out of time.

Any help appreciated,

Cheers,

Janet,
First of all, what exactly are you trying to do?

As far as dividing a text field by "480", it depends on what is in the
text field. If it is strictly text (e.g. "Bob"), then obviously not. On
the other hand, if the text field is a customized field with a formula,
it is a simple matter of putting the correct formula in the custom
field. For example, let's say you wish to use a custom text field to
convert the Duration field, (which is actually stored internally in
minutes), to hours, the formula would be:
Text1=[Duration]/60

Hope this helps.
John
Project MVP

 

[Janet BN]

Thanks John, finally got there.

Thanks John,
I was trying to use the formula Brian K wrote in response to schemr (see
other threads). Basically, I am trying to calculate the days since the task
was supposed to have started.
We are building a template with lots of slack built in, but need to be able
to identify if the task hasn't started, how long since it should have. So we
can manage the slack across multiple projects - upwards of 50 at a time
(Construction).

Brian K's formula is supposed to do that I think, but I keep getting #ERROR
and cannot see where it is - my assumption was the "Project Calendar" field.
Formula below.

IIf([% Complete]=100,"Complete",IIf((ProjDateDiff( datevalue([Finish]),
Datevalue([Current Date]), [Project Calendar])/480)>4,"Late by 5 or
more",IIf((ProjDateDiff( datevalue([Finish]), Datevalue([Current Date]),
[Project Calendar])/480)>0,"Late by 1-4","Not Late")))

Any help appreciated.

Janet,
You're right, the formula is flawed. I'm surprised that didn't come out
in the original thread, although I admit I didn't actually read the full
thread. Brian's formula includes a field that doesn't exist and you're
also right that it is the "Project Calendar" - no such field. However,
in the formula, the default value is the active project's calendar so it
can be left out entirely unless you want to specify a specific calendar
on which to base the difference in time. If the calendar is specified,
it should be listed as a string (e.g. "standard" for the standard
calendar).

Assuming you do not have any need for a specific calendar, use the
following formula (I tested it, it now works):

IIf([%Complete]=100,"Complete",IIf((ProjDateDiff(datevalue([Finish]),Date
value([Current Date]))/480)>4,"Late by 5 or
more",IIf((ProjDateDiff(datevalue([Finish]),Datevalue([Current
Date]))/480)>0,"Late by 1-4","Not Late")))

Hope this helps.
John
Project MVP

Hi Marc,

I'm having a brain freeze and I suspect it's the same original problem
you
had. How can you divide a text field by 480?

I'm obviously missing something and I know it has been a long time since
you
worked on this, but can you tell me the answer. I've been working on
this
for a while now and running out of time.

Any help appreciated,

Cheers,

Janet,
First of all, what exactly are you trying to do?

As far as dividing a text field by "480", it depends on what is in the
text field. If it is strictly text (e.g. "Bob"), then obviously not. On
the other hand, if the text field is a customized field with a formula,
it is a simple matter of putting the correct formula in the custom
field. For example, let's say you wish to use a custom text field to
convert the Duration field, (which is actually stored internally in
minutes), to hours, the formula would be:
Text1=[Duration]/60

Hope this helps.
John
Project MVP

 

[John]

Thanks John, finally got there.

Janet,
You're welcome.
John

Thanks John,
I was trying to use the formula Brian K wrote in response to schemr (see
other threads). Basically, I am trying to calculate the days since the
task
was supposed to have started.
We are building a template with lots of slack built in, but need to be
able
to identify if the task hasn't started, how long since it should have.
So we
can manage the slack across multiple projects - upwards of 50 at a time
(Construction).

Brian K's formula is supposed to do that I think, but I keep getting
#ERROR
and cannot see where it is - my assumption was the "Project Calendar"
field.
Formula below.

IIf([% Complete]=100,"Complete",IIf((ProjDateDiff( datevalue([Finish]),
Datevalue([Current Date]), [Project Calendar])/480)>4,"Late by 5 or
more",IIf((ProjDateDiff( datevalue([Finish]), Datevalue([Current Date]),
[Project Calendar])/480)>0,"Late by 1-4","Not Late")))

Any help appreciated.

Janet,
You're right, the formula is flawed. I'm surprised that didn't come out
in the original thread, although I admit I didn't actually read the full
thread. Brian's formula includes a field that doesn't exist and you're
also right that it is the "Project Calendar" - no such field. However,
in the formula, the default value is the active project's calendar so it
can be left out entirely unless you want to specify a specific calendar
on which to base the difference in time. If the calendar is specified,
it should be listed as a string (e.g. "standard" for the standard
calendar).

Assuming you do not have any need for a specific calendar, use the
following formula (I tested it, it now works):

IIf([%Complete]=100,"Complete",IIf((ProjDateDiff(datevalue([Finish]),Date
value([Current Date]))/480)>4,"Late by 5 or
more",IIf((ProjDateDiff(datevalue([Finish]),Datevalue([Current
Date]))/480)>0,"Late by 1-4","Not Late")))

Hope this helps.
John
Project MVP

:

Hi Marc,

I'm having a brain freeze and I suspect it's the same original
problem
you
had. How can you divide a text field by 480?

I'm obviously missing something and I know it has been a long time
since
you
worked on this, but can you tell me the answer. I've been working on
this
for a while now and running out of time.

Any help appreciated,

Cheers,

Janet,
First of all, what exactly are you trying to do?

As far as dividing a text field by "480", it depends on what is in the
text field. If it is strictly text (e.g. "Bob"), then obviously not. On
the other hand, if the text field is a customized field with a formula,
it is a simple matter of putting the correct formula in the custom
field. For example, let's say you wish to use a custom text field to
convert the Duration field, (which is actually stored internally in
minutes), to hours, the formula would be:
Text1=[Duration]/60

Hope this helps.
John
Project MVP