Correct Formula

[(Charles Ray) cray]

I have a formula that I need some help with. In the formula if it freturns a
negative value, I need it to return 0 and if it is greater than 100 I need it
to return 100. The formula is below:

ProjDateDiff([Start],[Status Date])/[Duration]*100

 

[DavidC]

Hi,

You need to do a logic check on the formula. Try this:

IIf(ProjDateDiff([Start],[Status
Date])/[Duration]*100<0,0,IIf(ProjDateDiff([Start],[Status
Date])/[Duration]*100>100,100,ProjDateDiff([Start],[Status
Date])/[Duration]*100))

NOTE that it is capital I, capital I for the IIf statement.

Hope this helps.

Regards

DavidC

 

[(Charles Ray) cray]

David, I tried the formula inconjuntion with the main fromula and it doesn't
work. What am I doing wrong? This is the formula:
ProjDateDiff([Start],[Status Date])/[Duration]*100 And
IIf(ProjDateDiff([Start],[Status
Date])/[Duration]*100<0,0,IIf(ProjDateDiff([Start],[Status
Date])/[Duration]*100>100,100))

Thanks,

Charles

Hi,

You need to do a logic check on the formula. Try this:

IIf(ProjDateDiff([Start],[Status
Date])/[Duration]*100<0,0,IIf(ProjDateDiff([Start],[Status
Date])/[Duration]*100>100,100,ProjDateDiff([Start],[Status
Date])/[Duration]*100))

NOTE that it is capital I, capital I for the IIf statement.

Hope this helps.

Regards

DavidC

I have a formula that I need some help with. In the formula if it freturns a
negative value, I need it to return 0 and if it is greater than 100 I need it
to return 100. The formula is below:

ProjDateDiff([Start],[Status Date])/[Duration]*100

 

[DavidC]

Hi Charles,

Just use the formula from 'IIF' on. The error you will be getting is
because of the first portion you have up to and including 'AND' with all that
you have shown, it is not recognised as a valid statement..

Regards

DavidC

David, I tried the formula inconjuntion with the main fromula and it doesn't
work. What am I doing wrong? This is the formula:
ProjDateDiff([Start],[Status Date])/[Duration]*100 And
IIf(ProjDateDiff([Start],[Status
Date])/[Duration]*100<0,0,IIf(ProjDateDiff([Start],[Status
Date])/[Duration]*100>100,100))

Thanks,

Charles

Hi,

You need to do a logic check on the formula. Try this:

IIf(ProjDateDiff([Start],[Status
Date])/[Duration]*100<0,0,IIf(ProjDateDiff([Start],[Status
Date])/[Duration]*100>100,100,ProjDateDiff([Start],[Status
Date])/[Duration]*100))

NOTE that it is capital I, capital I for the IIf statement.

Hope this helps.

Regards

DavidC

I have a formula that I need some help with. In the formula if it freturns a
negative value, I need it to return 0 and if it is greater than 100 I need it
to return 100. The formula is below:

ProjDateDiff([Start],[Status Date])/[Duration]*100

 

[(Charles Ray) cray]

David, That returns either 0, 100, or error.

Hi Charles,

Just use the formula from 'IIF' on. The error you will be getting is
because of the first portion you have up to and including 'AND' with all that
you have shown, it is not recognised as a valid statement..

Regards

DavidC

David, I tried the formula inconjuntion with the main fromula and it doesn't
work. What am I doing wrong? This is the formula:
ProjDateDiff([Start],[Status Date])/[Duration]*100 And
IIf(ProjDateDiff([Start],[Status
Date])/[Duration]*100<0,0,IIf(ProjDateDiff([Start],[Status
Date])/[Duration]*100>100,100))

Thanks,

Charles

Hi,

You need to do a logic check on the formula. Try this:

IIf(ProjDateDiff([Start],[Status
Date])/[Duration]*100<0,0,IIf(ProjDateDiff([Start],[Status
Date])/[Duration]*100>100,100,ProjDateDiff([Start],[Status
Date])/[Duration]*100))

NOTE that it is capital I, capital I for the IIf statement.

Hope this helps.

Regards

DavidC

:

I have a formula that I need some help with. In the formula if it freturns a
negative value, I need it to return 0 and if it is greater than 100 I need it
to return 100. The formula is below:

ProjDateDiff([Start],[Status Date])/[Duration]*100

 

[DavidC]

Hi,

Sorry forgot the last part of the formula when the IIF statement is finally
false.

The formula should be:

IIf(ProjDateDiff([Start],[Status
Date])/[Duration]*100<0,0,IIf(ProjDateDiff([Start],[Status
Date])/[Duration]*100>100,100,ProjDateDiff([Start],[Status
Date])/[Duration]*100))

Regards

DavidC

David, That returns either 0, 100, or error.

Hi Charles,

Just use the formula from 'IIF' on. The error you will be getting is
because of the first portion you have up to and including 'AND' with all that
you have shown, it is not recognised as a valid statement..

Regards

DavidC

David, I tried the formula inconjuntion with the main fromula and it doesn't
work. What am I doing wrong? This is the formula:
ProjDateDiff([Start],[Status Date])/[Duration]*100 And
IIf(ProjDateDiff([Start],[Status
Date])/[Duration]*100<0,0,IIf(ProjDateDiff([Start],[Status
Date])/[Duration]*100>100,100))

Thanks,

Charles

:

Hi,

You need to do a logic check on the formula. Try this:

IIf(ProjDateDiff([Start],[Status
Date])/[Duration]*100<0,0,IIf(ProjDateDiff([Start],[Status
Date])/[Duration]*100>100,100,ProjDateDiff([Start],[Status
Date])/[Duration]*100))

NOTE that it is capital I, capital I for the IIf statement.

Hope this helps.

Regards

DavidC

:

I have a formula that I need some help with. In the formula if it freturns a
negative value, I need it to return 0 and if it is greater than 100 I need it
to return 100. The formula is below:

ProjDateDiff([Start],[Status Date])/[Duration]*100

 

[(Charles Ray) cray]

David,
That did it. The only problem is the milestone dates with a duration od 0
it returns an error, but the rest look good. Do you want to tackle another
formula for me? You seem to be light years ahead of me on this. Thanks so
much.

This is out of a post that Mike sent me on the above formula relative a the
progress of a task based on the % complete.

It's not real clear what you mean by, "complete through date". If I
assume that is the equivalent date for the "normal" progress based on
the entered % complete value, then a similar formula (using ProjDateAdd)
to generate a date based on the % complete be used.


Charles

Hi,

Sorry forgot the last part of the formula when the IIF statement is finally
false.

The formula should be:

IIf(ProjDateDiff([Start],[Status
Date])/[Duration]*100<0,0,IIf(ProjDateDiff([Start],[Status
Date])/[Duration]*100>100,100,ProjDateDiff([Start],[Status
Date])/[Duration]*100))

Regards

DavidC

David, That returns either 0, 100, or error.

Hi Charles,

Just use the formula from 'IIF' on. The error you will be getting is
because of the first portion you have up to and including 'AND' with all that
you have shown, it is not recognised as a valid statement..

Regards

DavidC

:

David, I tried the formula inconjuntion with the main fromula and it doesn't
work. What am I doing wrong? This is the formula:
ProjDateDiff([Start],[Status Date])/[Duration]*100 And
IIf(ProjDateDiff([Start],[Status
Date])/[Duration]*100<0,0,IIf(ProjDateDiff([Start],[Status
Date])/[Duration]*100>100,100))

Thanks,

Charles

:

Hi,

You need to do a logic check on the formula. Try this:

IIf(ProjDateDiff([Start],[Status
Date])/[Duration]*100<0,0,IIf(ProjDateDiff([Start],[Status
Date])/[Duration]*100>100,100,ProjDateDiff([Start],[Status
Date])/[Duration]*100))

NOTE that it is capital I, capital I for the IIf statement.

Hope this helps.

Regards

DavidC

:

I have a formula that I need some help with. In the formula if it freturns a
negative value, I need it to return 0 and if it is greater than 100 I need it
to return 100. The formula is below:

ProjDateDiff([Start],[Status Date])/[Duration]*100

 

[DavidC]

Hi,

The error had me for a while. Remember you are dividing the result from the
difference between the start date and the status date, by the duration. If
the duration is 0 then you are trying to get an answer for a sum divided by 0.

First thing then is to eliminate the 0 duration activities (not delete them
merely elimnate them as far as the formula is concnerned.) Hence add the
following:

IIF([Duration]=0,0, remainder of formula.

The resultant formula is thus :
IIf([Duration]=0,0,IIf(ProjDateDiff([Start],[Status
Date])/[Duration]*100<0,0,IIf(ProjDateDiff([Start],[Status
Date])/[Duration]*100>100,100,ProjDateDiff([Start],[Status
Date])/[Duration]*100)))

As for the other question, you will have to excuse me but I am unsure what
you are trying to achieve. I think I understand the answer given but if you
could explain what you want to achieve then I can have a look at a solution.
It sounds like you want to calculate a value based on how complete the task
is?

Regards

DavidC

David,
That did it. The only problem is the milestone dates with a duration od 0
it returns an error, but the rest look good. Do you want to tackle another
formula for me? You seem to be light years ahead of me on this. Thanks so
much.

This is out of a post that Mike sent me on the above formula relative a the
progress of a task based on the % complete.

It's not real clear what you mean by, "complete through date". If I
assume that is the equivalent date for the "normal" progress based on
the entered % complete value, then a similar formula (using ProjDateAdd)
to generate a date based on the % complete be used.


Charles

Hi,

Sorry forgot the last part of the formula when the IIF statement is finally
false.

The formula should be:

IIf(ProjDateDiff([Start],[Status
Date])/[Duration]*100<0,0,IIf(ProjDateDiff([Start],[Status
Date])/[Duration]*100>100,100,ProjDateDiff([Start],[Status
Date])/[Duration]*100))

Regards

DavidC

David, That returns either 0, 100, or error.

:

Hi Charles,

Just use the formula from 'IIF' on. The error you will be getting is
because of the first portion you have up to and including 'AND' with all that
you have shown, it is not recognised as a valid statement..

Regards

DavidC

:

David, I tried the formula inconjuntion with the main fromula and it doesn't
work. What am I doing wrong? This is the formula:
ProjDateDiff([Start],[Status Date])/[Duration]*100 And
IIf(ProjDateDiff([Start],[Status
Date])/[Duration]*100<0,0,IIf(ProjDateDiff([Start],[Status
Date])/[Duration]*100>100,100))

Thanks,

Charles

:

Hi,

You need to do a logic check on the formula. Try this:

IIf(ProjDateDiff([Start],[Status
Date])/[Duration]*100<0,0,IIf(ProjDateDiff([Start],[Status
Date])/[Duration]*100>100,100,ProjDateDiff([Start],[Status
Date])/[Duration]*100))

NOTE that it is capital I, capital I for the IIf statement.

Hope this helps.

Regards

DavidC

:

I have a formula that I need some help with. In the formula if it freturns a
negative value, I need it to return 0 and if it is greater than 100 I need it
to return 100. The formula is below:

ProjDateDiff([Start],[Status Date])/[Duration]*100

 

[(Charles Ray) cray]

David, Thanks for all your help with this. What I want to do with the next
formula is show based on the % Complete value what that date is. For
example. I have a task that is 27% complete on the status date of 1/15/06,
the task is actually "Complete Through" 1/3/06. This will be a quick
reference for how far behind or ahead a particular task is. Most of our
schedules are 1500 to 5000 task long. Quick references are must.

Thanks Again,

Charles

Hi,

The error had me for a while. Remember you are dividing the result from the
difference between the start date and the status date, by the duration. If
the duration is 0 then you are trying to get an answer for a sum divided by 0.

First thing then is to eliminate the 0 duration activities (not delete them
merely elimnate them as far as the formula is concnerned.) Hence add the
following:

IIF([Duration]=0,0, remainder of formula.

The resultant formula is thus :
IIf([Duration]=0,0,IIf(ProjDateDiff([Start],[Status
Date])/[Duration]*100<0,0,IIf(ProjDateDiff([Start],[Status
Date])/[Duration]*100>100,100,ProjDateDiff([Start],[Status
Date])/[Duration]*100)))

As for the other question, you will have to excuse me but I am unsure what
you are trying to achieve. I think I understand the answer given but if you
could explain what you want to achieve then I can have a look at a solution.
It sounds like you want to calculate a value based on how complete the task
is?

Regards

DavidC

David,
That did it. The only problem is the milestone dates with a duration od 0
it returns an error, but the rest look good. Do you want to tackle another
formula for me? You seem to be light years ahead of me on this. Thanks so
much.

This is out of a post that Mike sent me on the above formula relative a the
progress of a task based on the % complete.

It's not real clear what you mean by, "complete through date". If I
assume that is the equivalent date for the "normal" progress based on
the entered % complete value, then a similar formula (using ProjDateAdd)
to generate a date based on the % complete be used.


Charles

Hi,

Sorry forgot the last part of the formula when the IIF statement is finally
false.

The formula should be:

IIf(ProjDateDiff([Start],[Status
Date])/[Duration]*100<0,0,IIf(ProjDateDiff([Start],[Status
Date])/[Duration]*100>100,100,ProjDateDiff([Start],[Status
Date])/[Duration]*100))

Regards

DavidC

:

David, That returns either 0, 100, or error.

:

Hi Charles,

Just use the formula from 'IIF' on. The error you will be getting is
because of the first portion you have up to and including 'AND' with all that
you have shown, it is not recognised as a valid statement..

Regards

DavidC

:

David, I tried the formula inconjuntion with the main fromula and it doesn't
work. What am I doing wrong? This is the formula:
ProjDateDiff([Start],[Status Date])/[Duration]*100 And
IIf(ProjDateDiff([Start],[Status
Date])/[Duration]*100<0,0,IIf(ProjDateDiff([Start],[Status
Date])/[Duration]*100>100,100))

Thanks,

Charles

:

Hi,

You need to do a logic check on the formula. Try this:

IIf(ProjDateDiff([Start],[Status
Date])/[Duration]*100<0,0,IIf(ProjDateDiff([Start],[Status
Date])/[Duration]*100>100,100,ProjDateDiff([Start],[Status
Date])/[Duration]*100))

NOTE that it is capital I, capital I for the IIf statement.

Hope this helps.

Regards

DavidC

:

I have a formula that I need some help with. In the formula if it freturns a
negative value, I need it to return 0 and if it is greater than 100 I need it
to return 100. The formula is below:

ProjDateDiff([Start],[Status Date])/[Duration]*100

 

[(Charles Ray) cray]

David,

Something went wrong. My project Summary is almost 2 years long and started
7/5/05. It show 0 as do other task. Some look correct, while others look
way off. I copied the formula and pasted it.

Charles

Hi,

The error had me for a while. Remember you are dividing the result from the
difference between the start date and the status date, by the duration. If
the duration is 0 then you are trying to get an answer for a sum divided by 0.

First thing then is to eliminate the 0 duration activities (not delete them
merely elimnate them as far as the formula is concnerned.) Hence add the
following:

IIF([Duration]=0,0, remainder of formula.

The resultant formula is thus :
IIf([Duration]=0,0,IIf(ProjDateDiff([Start],[Status
Date])/[Duration]*100<0,0,IIf(ProjDateDiff([Start],[Status
Date])/[Duration]*100>100,100,ProjDateDiff([Start],[Status
Date])/[Duration]*100)))

As for the other question, you will have to excuse me but I am unsure what
you are trying to achieve. I think I understand the answer given but if you
could explain what you want to achieve then I can have a look at a solution.
It sounds like you want to calculate a value based on how complete the task
is?

Regards

DavidC

David,
That did it. The only problem is the milestone dates with a duration od 0
it returns an error, but the rest look good. Do you want to tackle another
formula for me? You seem to be light years ahead of me on this. Thanks so
much.

This is out of a post that Mike sent me on the above formula relative a the
progress of a task based on the % complete.

It's not real clear what you mean by, "complete through date". If I
assume that is the equivalent date for the "normal" progress based on
the entered % complete value, then a similar formula (using ProjDateAdd)
to generate a date based on the % complete be used.


Charles

Hi,

Sorry forgot the last part of the formula when the IIF statement is finally
false.

The formula should be:

IIf(ProjDateDiff([Start],[Status
Date])/[Duration]*100<0,0,IIf(ProjDateDiff([Start],[Status
Date])/[Duration]*100>100,100,ProjDateDiff([Start],[Status
Date])/[Duration]*100))

Regards

DavidC

:

David, That returns either 0, 100, or error.

:

Hi Charles,

Just use the formula from 'IIF' on. The error you will be getting is
because of the first portion you have up to and including 'AND' with all that
you have shown, it is not recognised as a valid statement..

Regards

DavidC

:

David, I tried the formula inconjuntion with the main fromula and it doesn't
work. What am I doing wrong? This is the formula:
ProjDateDiff([Start],[Status Date])/[Duration]*100 And
IIf(ProjDateDiff([Start],[Status
Date])/[Duration]*100<0,0,IIf(ProjDateDiff([Start],[Status
Date])/[Duration]*100>100,100))

Thanks,

Charles

:

Hi,

You need to do a logic check on the formula. Try this:

IIf(ProjDateDiff([Start],[Status
Date])/[Duration]*100<0,0,IIf(ProjDateDiff([Start],[Status
Date])/[Duration]*100>100,100,ProjDateDiff([Start],[Status
Date])/[Duration]*100))

NOTE that it is capital I, capital I for the IIf statement.

Hope this helps.

Regards

DavidC

:

I have a formula that I need some help with. In the formula if it freturns a
negative value, I need it to return 0 and if it is greater than 100 I need it
to return 100. The formula is below:

ProjDateDiff([Start],[Status Date])/[Duration]*100

 

[DavidC]

Hi,

See if I have this right. A task with say a 10 day duration is 25% complete
as at the status date which is say five days into the task. The task is
therefore 2.5 days late.

If this is the correct interpretation, then it confirms Mike's suggestion of
using the Dateadd function. I am guessing that you are not that interested
in work effort left as that is a function of resources applied to a duration.
You simply need to know if a task is behind or ahead of schedule with a
value rather than the graphical interface that is available through using the
reschedule function on the Gantt chart.

So on that basis, try this:
In custom field Text1 enter this formula:

[Duration]*[% Complete]/100/480

(Note the 480 is 60 minutes in the hour times the number of hours set in the
calendar for that task. In my case I have an 8 hour workin day set for that
task.)

In a custom date field add this formula

Projdateadd([Start],[Text1])

Regards

DavidC

David, Thanks for all your help with this. What I want to do with the next
formula is show based on the % Complete value what that date is. For
example. I have a task that is 27% complete on the status date of 1/15/06,
the task is actually "Complete Through" 1/3/06. This will be a quick
reference for how far behind or ahead a particular task is. Most of our
schedules are 1500 to 5000 task long. Quick references are must.

Thanks Again,

Charles

Hi,

The error had me for a while. Remember you are dividing the result from the
difference between the start date and the status date, by the duration. If
the duration is 0 then you are trying to get an answer for a sum divided by 0.

First thing then is to eliminate the 0 duration activities (not delete them
merely elimnate them as far as the formula is concnerned.) Hence add the
following:

IIF([Duration]=0,0, remainder of formula.

The resultant formula is thus :
IIf([Duration]=0,0,IIf(ProjDateDiff([Start],[Status
Date])/[Duration]*100<0,0,IIf(ProjDateDiff([Start],[Status
Date])/[Duration]*100>100,100,ProjDateDiff([Start],[Status
Date])/[Duration]*100)))

As for the other question, you will have to excuse me but I am unsure what
you are trying to achieve. I think I understand the answer given but if you
could explain what you want to achieve then I can have a look at a solution.
It sounds like you want to calculate a value based on how complete the task
is?

Regards

DavidC

David,
That did it. The only problem is the milestone dates with a duration od 0
it returns an error, but the rest look good. Do you want to tackle another
formula for me? You seem to be light years ahead of me on this. Thanks so
much.

This is out of a post that Mike sent me on the above formula relative a the
progress of a task based on the % complete.

It's not real clear what you mean by, "complete through date". If I
assume that is the equivalent date for the "normal" progress based on
the entered % complete value, then a similar formula (using ProjDateAdd)
to generate a date based on the % complete be used.


Charles

:

Hi,

Sorry forgot the last part of the formula when the IIF statement is finally
false.

The formula should be:

IIf(ProjDateDiff([Start],[Status
Date])/[Duration]*100<0,0,IIf(ProjDateDiff([Start],[Status
Date])/[Duration]*100>100,100,ProjDateDiff([Start],[Status
Date])/[Duration]*100))

Regards

DavidC

:

David, That returns either 0, 100, or error.

:

Hi Charles,

Just use the formula from 'IIF' on. The error you will be getting is
because of the first portion you have up to and including 'AND' with all that
you have shown, it is not recognised as a valid statement..

Regards

DavidC

:

David, I tried the formula inconjuntion with the main fromula and it doesn't
work. What am I doing wrong? This is the formula:
ProjDateDiff([Start],[Status Date])/[Duration]*100 And
IIf(ProjDateDiff([Start],[Status
Date])/[Duration]*100<0,0,IIf(ProjDateDiff([Start],[Status
Date])/[Duration]*100>100,100))

Thanks,

Charles

:

Hi,

You need to do a logic check on the formula. Try this:

IIf(ProjDateDiff([Start],[Status
Date])/[Duration]*100<0,0,IIf(ProjDateDiff([Start],[Status
Date])/[Duration]*100>100,100,ProjDateDiff([Start],[Status
Date])/[Duration]*100))

NOTE that it is capital I, capital I for the IIf statement.

Hope this helps.

Regards

DavidC

:

I have a formula that I need some help with. In the formula if it freturns a
negative value, I need it to return 0 and if it is greater than 100 I need it
to return 100. The formula is below:

ProjDateDiff([Start],[Status Date])/[Duration]*100

 

[(Charles Ray) cray]

That works great. One question, why won't the formula work on the Summary
Task? They show as errors.

Charles

Hi,

See if I have this right. A task with say a 10 day duration is 25% complete
as at the status date which is say five days into the task. The task is
therefore 2.5 days late.

If this is the correct interpretation, then it confirms Mike's suggestion of
using the Dateadd function. I am guessing that you are not that interested
in work effort left as that is a function of resources applied to a duration.
You simply need to know if a task is behind or ahead of schedule with a
value rather than the graphical interface that is available through using the
reschedule function on the Gantt chart.

So on that basis, try this:
In custom field Text1 enter this formula:

[Duration]*[% Complete]/100/480

(Note the 480 is 60 minutes in the hour times the number of hours set in the
calendar for that task. In my case I have an 8 hour workin day set for that
task.)

In a custom date field add this formula

Projdateadd([Start],[Text1])

Regards

DavidC

David, Thanks for all your help with this. What I want to do with the next
formula is show based on the % Complete value what that date is. For
example. I have a task that is 27% complete on the status date of 1/15/06,
the task is actually "Complete Through" 1/3/06. This will be a quick
reference for how far behind or ahead a particular task is. Most of our
schedules are 1500 to 5000 task long. Quick references are must.

Thanks Again,

Charles

Hi,

The error had me for a while. Remember you are dividing the result from the
difference between the start date and the status date, by the duration. If
the duration is 0 then you are trying to get an answer for a sum divided by 0.

First thing then is to eliminate the 0 duration activities (not delete them
merely elimnate them as far as the formula is concnerned.) Hence add the
following:

IIF([Duration]=0,0, remainder of formula.

The resultant formula is thus :
IIf([Duration]=0,0,IIf(ProjDateDiff([Start],[Status
Date])/[Duration]*100<0,0,IIf(ProjDateDiff([Start],[Status
Date])/[Duration]*100>100,100,ProjDateDiff([Start],[Status
Date])/[Duration]*100)))

As for the other question, you will have to excuse me but I am unsure what
you are trying to achieve. I think I understand the answer given but if you
could explain what you want to achieve then I can have a look at a solution.
It sounds like you want to calculate a value based on how complete the task
is?

Regards

DavidC

:

David,
That did it. The only problem is the milestone dates with a duration od 0
it returns an error, but the rest look good. Do you want to tackle another
formula for me? You seem to be light years ahead of me on this. Thanks so
much.

This is out of a post that Mike sent me on the above formula relative a the
progress of a task based on the % complete.

It's not real clear what you mean by, "complete through date". If I
assume that is the equivalent date for the "normal" progress based on
the entered % complete value, then a similar formula (using ProjDateAdd)
to generate a date based on the % complete be used.


Charles

:

Hi,

Sorry forgot the last part of the formula when the IIF statement is finally
false.

The formula should be:

IIf(ProjDateDiff([Start],[Status
Date])/[Duration]*100<0,0,IIf(ProjDateDiff([Start],[Status
Date])/[Duration]*100>100,100,ProjDateDiff([Start],[Status
Date])/[Duration]*100))

Regards

DavidC

:

David, That returns either 0, 100, or error.

:

Hi Charles,

Just use the formula from 'IIF' on. The error you will be getting is
because of the first portion you have up to and including 'AND' with all that
you have shown, it is not recognised as a valid statement..

Regards

DavidC

:

David, I tried the formula inconjuntion with the main fromula and it doesn't
work. What am I doing wrong? This is the formula:
ProjDateDiff([Start],[Status Date])/[Duration]*100 And
IIf(ProjDateDiff([Start],[Status
Date])/[Duration]*100<0,0,IIf(ProjDateDiff([Start],[Status
Date])/[Duration]*100>100,100))

Thanks,

Charles

:

Hi,

You need to do a logic check on the formula. Try this:

IIf(ProjDateDiff([Start],[Status
Date])/[Duration]*100<0,0,IIf(ProjDateDiff([Start],[Status
Date])/[Duration]*100>100,100,ProjDateDiff([Start],[Status
Date])/[Duration]*100))

NOTE that it is capital I, capital I for the IIf statement.

Hope this helps.

Regards

DavidC

:

I have a formula that I need some help with. In the formula if it freturns a
negative value, I need it to return 0 and if it is greater than 100 I need it
to return 100. The formula is below:

ProjDateDiff([Start],[Status Date])/[Duration]*100

 

[DavidC]

Hi,

Sorry about the delay. I did respond, (I thought) but see that it didn't go
through. When I logged off last night I had a message showing that the
microsoft site was down so hence it looks like I missed the bus so to speak.

Anyway I did look at the issue of the summary task not showing a value only
errors. I did have the same issue, but then I recreated the summary task and
it disappeared. The problem does not seem to occur with any new summary task
created after the fields have the formulae attached, but rather for summary
tasks created before the formulae are attached. Maybe, the easiest at this
stage might be to simply (if that is the right word for this situation)
re-create the summary tasks.

The way I have done this in the past is to create a new task with the same
name as the summary task, make the 'old' summary task a task under the 'new'
summary task, then outdent, all the tasks under the 'old' summary task hence
making them tasks under the 'new' summary, then delete the 'old' summary task.

A bit tedious but it has worked for me in the past when I have had problems
which seem not to have a logical cause.

Also I notice that the Project summary task does not calculate the formula
at all.

Regards

DavidC

That works great. One question, why won't the formula work on the Summary
Task? They show as errors.

Charles

Hi,

See if I have this right. A task with say a 10 day duration is 25% complete
as at the status date which is say five days into the task. The task is
therefore 2.5 days late.

If this is the correct interpretation, then it confirms Mike's suggestion of
using the Dateadd function. I am guessing that you are not that interested
in work effort left as that is a function of resources applied to a duration.
You simply need to know if a task is behind or ahead of schedule with a
value rather than the graphical interface that is available through using the
reschedule function on the Gantt chart.

So on that basis, try this:
In custom field Text1 enter this formula:

[Duration]*[% Complete]/100/480

(Note the 480 is 60 minutes in the hour times the number of hours set in the
calendar for that task. In my case I have an 8 hour workin day set for that
task.)

In a custom date field add this formula

Projdateadd([Start],[Text1])

Regards

DavidC

David, Thanks for all your help with this. What I want to do with the next
formula is show based on the % Complete value what that date is. For
example. I have a task that is 27% complete on the status date of 1/15/06,
the task is actually "Complete Through" 1/3/06. This will be a quick
reference for how far behind or ahead a particular task is. Most of our
schedules are 1500 to 5000 task long. Quick references are must.

Thanks Again,

Charles

:

Hi,

The error had me for a while. Remember you are dividing the result from the
difference between the start date and the status date, by the duration. If
the duration is 0 then you are trying to get an answer for a sum divided by 0.

First thing then is to eliminate the 0 duration activities (not delete them
merely elimnate them as far as the formula is concnerned.) Hence add the
following:

IIF([Duration]=0,0, remainder of formula.

The resultant formula is thus :
IIf([Duration]=0,0,IIf(ProjDateDiff([Start],[Status
Date])/[Duration]*100<0,0,IIf(ProjDateDiff([Start],[Status
Date])/[Duration]*100>100,100,ProjDateDiff([Start],[Status
Date])/[Duration]*100)))

As for the other question, you will have to excuse me but I am unsure what
you are trying to achieve. I think I understand the answer given but if you
could explain what you want to achieve then I can have a look at a solution.
It sounds like you want to calculate a value based on how complete the task
is?

Regards

DavidC

:

David,
That did it. The only problem is the milestone dates with a duration od 0
it returns an error, but the rest look good. Do you want to tackle another
formula for me? You seem to be light years ahead of me on this. Thanks so
much.

This is out of a post that Mike sent me on the above formula relative a the
progress of a task based on the % complete.

It's not real clear what you mean by, "complete through date". If I
assume that is the equivalent date for the "normal" progress based on
the entered % complete value, then a similar formula (using ProjDateAdd)
to generate a date based on the % complete be used.


Charles

:

Hi,

Sorry forgot the last part of the formula when the IIF statement is finally
false.

The formula should be:

IIf(ProjDateDiff([Start],[Status
Date])/[Duration]*100<0,0,IIf(ProjDateDiff([Start],[Status
Date])/[Duration]*100>100,100,ProjDateDiff([Start],[Status
Date])/[Duration]*100))

Regards

DavidC

:

David, That returns either 0, 100, or error.

:

Hi Charles,

Just use the formula from 'IIF' on. The error you will be getting is
because of the first portion you have up to and including 'AND' with all that
you have shown, it is not recognised as a valid statement..

Regards

DavidC

:

David, I tried the formula inconjuntion with the main fromula and it doesn't
work. What am I doing wrong? This is the formula:
ProjDateDiff([Start],[Status Date])/[Duration]*100 And
IIf(ProjDateDiff([Start],[Status
Date])/[Duration]*100<0,0,IIf(ProjDateDiff([Start],[Status
Date])/[Duration]*100>100,100))

Thanks,

Charles

:

Hi,

You need to do a logic check on the formula. Try this:

IIf(ProjDateDiff([Start],[Status
Date])/[Duration]*100<0,0,IIf(ProjDateDiff([Start],[Status
Date])/[Duration]*100>100,100,ProjDateDiff([Start],[Status
Date])/[Duration]*100))

NOTE that it is capital I, capital I for the IIf statement.

Hope this helps.

Regards

DavidC

:

I have a formula that I need some help with. In the formula if it freturns a
negative value, I need it to return 0 and if it is greater than 100 I need it
to return 100. The formula is below:

ProjDateDiff([Start],[Status Date])/[Duration]*100

 

[(Charles Ray) cray]

David, Thanks so much for your help with this. You have been a life saver.
It6 really would be nice if Microsoft added these features to the Program.
It would be so useful as a presentation tool for my clients that aren't
versed in the program and have only a basic knowledge of a schedule.

Regards,

Charles

Hi,

Sorry about the delay. I did respond, (I thought) but see that it didn't go
through. When I logged off last night I had a message showing that the
microsoft site was down so hence it looks like I missed the bus so to speak.

Anyway I did look at the issue of the summary task not showing a value only
errors. I did have the same issue, but then I recreated the summary task and
it disappeared. The problem does not seem to occur with any new summary task
created after the fields have the formulae attached, but rather for summary
tasks created before the formulae are attached. Maybe, the easiest at this
stage might be to simply (if that is the right word for this situation)
re-create the summary tasks.

The way I have done this in the past is to create a new task with the same
name as the summary task, make the 'old' summary task a task under the 'new'
summary task, then outdent, all the tasks under the 'old' summary task hence
making them tasks under the 'new' summary, then delete the 'old' summary task.

A bit tedious but it has worked for me in the past when I have had problems
which seem not to have a logical cause.

Also I notice that the Project summary task does not calculate the formula
at all.

Regards

DavidC

That works great. One question, why won't the formula work on the Summary
Task? They show as errors.

Charles

Hi,

See if I have this right. A task with say a 10 day duration is 25% complete
as at the status date which is say five days into the task. The task is
therefore 2.5 days late.

If this is the correct interpretation, then it confirms Mike's suggestion of
using the Dateadd function. I am guessing that you are not that interested
in work effort left as that is a function of resources applied to a duration.
You simply need to know if a task is behind or ahead of schedule with a
value rather than the graphical interface that is available through using the
reschedule function on the Gantt chart.

So on that basis, try this:
In custom field Text1 enter this formula:

[Duration]*[% Complete]/100/480

(Note the 480 is 60 minutes in the hour times the number of hours set in the
calendar for that task. In my case I have an 8 hour workin day set for that
task.)

In a custom date field add this formula

Projdateadd([Start],[Text1])

Regards

DavidC

:

David, Thanks for all your help with this. What I want to do with the next
formula is show based on the % Complete value what that date is. For
example. I have a task that is 27% complete on the status date of 1/15/06,
the task is actually "Complete Through" 1/3/06. This will be a quick
reference for how far behind or ahead a particular task is. Most of our
schedules are 1500 to 5000 task long. Quick references are must.

Thanks Again,

Charles

:

Hi,

The error had me for a while. Remember you are dividing the result from the
difference between the start date and the status date, by the duration. If
the duration is 0 then you are trying to get an answer for a sum divided by 0.

First thing then is to eliminate the 0 duration activities (not delete them
merely elimnate them as far as the formula is concnerned.) Hence add the
following:

IIF([Duration]=0,0, remainder of formula.

The resultant formula is thus :
IIf([Duration]=0,0,IIf(ProjDateDiff([Start],[Status
Date])/[Duration]*100<0,0,IIf(ProjDateDiff([Start],[Status
Date])/[Duration]*100>100,100,ProjDateDiff([Start],[Status
Date])/[Duration]*100)))

As for the other question, you will have to excuse me but I am unsure what
you are trying to achieve. I think I understand the answer given but if you
could explain what you want to achieve then I can have a look at a solution.
It sounds like you want to calculate a value based on how complete the task
is?

Regards

DavidC

:

David,
That did it. The only problem is the milestone dates with a duration od 0
it returns an error, but the rest look good. Do you want to tackle another
formula for me? You seem to be light years ahead of me on this. Thanks so
much.

This is out of a post that Mike sent me on the above formula relative a the
progress of a task based on the % complete.

It's not real clear what you mean by, "complete through date". If I
assume that is the equivalent date for the "normal" progress based on
the entered % complete value, then a similar formula (using ProjDateAdd)
to generate a date based on the % complete be used.


Charles

:

Hi,

Sorry forgot the last part of the formula when the IIF statement is finally
false.

The formula should be:

IIf(ProjDateDiff([Start],[Status
Date])/[Duration]*100<0,0,IIf(ProjDateDiff([Start],[Status
Date])/[Duration]*100>100,100,ProjDateDiff([Start],[Status
Date])/[Duration]*100))

Regards

DavidC

:

David, That returns either 0, 100, or error.

:

Hi Charles,

Just use the formula from 'IIF' on. The error you will be getting is
because of the first portion you have up to and including 'AND' with all that
you have shown, it is not recognised as a valid statement..

Regards

DavidC

:

David, I tried the formula inconjuntion with the main fromula and it doesn't
work. What am I doing wrong? This is the formula:
ProjDateDiff([Start],[Status Date])/[Duration]*100 And
IIf(ProjDateDiff([Start],[Status
Date])/[Duration]*100<0,0,IIf(ProjDateDiff([Start],[Status
Date])/[Duration]*100>100,100))

Thanks,

Charles

:

Hi,

You need to do a logic check on the formula. Try this:

IIf(ProjDateDiff([Start],[Status
Date])/[Duration]*100<0,0,IIf(ProjDateDiff([Start],[Status
Date])/[Duration]*100>100,100,ProjDateDiff([Start],[Status
Date])/[Duration]*100))

NOTE that it is capital I, capital I for the IIf statement.

Hope this helps.

Regards

DavidC

:

I have a formula that I need some help with. In the formula if it freturns a
negative value, I need it to return 0 and if it is greater than 100 I need it
to return 100. The formula is below:

ProjDateDiff([Start],[Status Date])/[Duration]*100