A bit more formula help needed, please

[Ginger]

First, thanks again for the help and clues that have pushed me along. I
think I’m getting close. : )

I’m still having two error results appearing:
1) If a task is equal (i.e., 40% complete and should be 40%) it says RED. I
have tried a “-.01†on the end, but it seems to cause other errors.
2) When a task is set to 100% complete, it displays RED – even if, for
example, it’s only at 80% of the duration.

I’d want both of these to say GREEN or just be blank.

Text1 (Displays the percentage that should be complete at the current date,
assuming straight-line effort. Displays with a percent sign)
Left(str(IIf([Current Date]<[Start],"0",IIf([Current
Date]>[Finish],"1",ProjDateDiff([Start],[Current
Date])/[Duration]))*100),4)+"%"

I’m using the next two to get the same result without the percent sign,
because I couldn’t get the formula to work with that in it.

Text3
[% Complete]

Text4
Left(str(IIf([Current Date]<[Start],"0",IIf([Current
Date]>[Finish],"1",ProjDateDiff([Start],[Current Date])/[Duration]))*100),4)

Text5
IIf([Text3]>[Text4]*100,"GREEN","RED")


Thanks again, in advance, for your help.

 

[John]

First, thanks again for the help and clues that have pushed me along. I
think I’m getting close. : )

I’m still having two error results appearing:
1) If a task is equal (i.e., 40% complete and should be 40%) it says RED. I
have tried a “-.01†on the end, but it seems to cause other errors.
2) When a task is set to 100% complete, it displays RED – even if, for
example, it’s only at 80% of the duration.

I’d want both of these to say GREEN or just be blank.

Text1 (Displays the percentage that should be complete at the current date,
assuming straight-line effort. Displays with a percent sign)
Left(str(IIf([Current Date]<[Start],"0",IIf([Current
Date]>[Finish],"1",ProjDateDiff([Start],[Current
Date])/[Duration]))*100),4)+"%"

I’m using the next two to get the same result without the percent sign,
because I couldn’t get the formula to work with that in it.

Text3
[% Complete]

Text4
Left(str(IIf([Current Date]<[Start],"0",IIf([Current
Date]>[Finish],"1",ProjDateDiff([Start],[Current Date])/[Duration]))*100),4)

Text5
IIf([Text3]>[Text4]*100,"GREEN","RED")


Thanks again, in advance, for your help.

Ginger,
I didn't analyze what you are doing to see if there is an easier way,
there probably is), but if you use your existing formula for text1, then
using this formula in text2 will give what you want. Note that you CAN
leave the "%" sign in your formula for text1.

Text2=IIf([%
Complete]=100,"",IIf(csng(mid([Text1],1,len([Text1])-1))=0,"green",IIf([%
Complete]>csng(mid([Text1],1,len([Text1])-1)),"ok","red")))

Hope this helps.
John
Project MVP

 

[Ginger]

Thank you so much. : )

First, thanks again for the help and clues that have pushed me along. I
think I’m getting close. : )

I’m still having two error results appearing:
1) If a task is equal (i.e., 40% complete and should be 40%) it says RED. I
have tried a “-.01†on the end, but it seems to cause other errors.
2) When a task is set to 100% complete, it displays RED – even if, for
example, it’s only at 80% of the duration.

I’d want both of these to say GREEN or just be blank.

Text1 (Displays the percentage that should be complete at the current date,
assuming straight-line effort. Displays with a percent sign)
Left(str(IIf([Current Date]<[Start],"0",IIf([Current
Date]>[Finish],"1",ProjDateDiff([Start],[Current
Date])/[Duration]))*100),4)+"%"

I’m using the next two to get the same result without the percent sign,
because I couldn’t get the formula to work with that in it.

Text3
[% Complete]

Text4
Left(str(IIf([Current Date]<[Start],"0",IIf([Current
Date]>[Finish],"1",ProjDateDiff([Start],[Current Date])/[Duration]))*100),4)

Text5
IIf([Text3]>[Text4]*100,"GREEN","RED")


Thanks again, in advance, for your help.

Ginger,
I didn't analyze what you are doing to see if there is an easier way,
there probably is), but if you use your existing formula for text1, then
using this formula in text2 will give what you want. Note that you CAN
leave the "%" sign in your formula for text1.

Text2=IIf([%
Complete]=100,"",IIf(csng(mid([Text1],1,len([Text1])-1))=0,"green",IIf([%
Complete]>csng(mid([Text1],1,len([Text1])-1)),"ok","red")))

Hope this helps.
John
Project MVP

 

[John]

Thank you so much. : )

Ginger,
You're welcome. Just for reference you might want to take a look at the
Status field. It might have useful information for you.

John
Project MVP

First, thanks again for the help and clues that have pushed me along. I
think Iâ¤m getting close. : )

Iâ¤m still having two error results appearing:
1) If a task is equal (i.e., 40% complete and should be 40%) it says RED.
I
have tried a ⤦-.01â¤ù on the end, but it seems to cause other errors.
2) When a task is set to 100% complete, it displays RED ⤳ even if, for
example, itâ¤s only at 80% of the duration.

Iâ¤d want both of these to say GREEN or just be blank.

Text1 (Displays the percentage that should be complete at the current
date,
assuming straight-line effort. Displays with a percent sign)
Left(str(IIf([Current Date]<[Start],"0",IIf([Current
Date]>[Finish],"1",ProjDateDiff([Start],[Current
Date])/[Duration]))*100),4)+"%"

Iâ¤m using the next two to get the same result without the percent sign,
because I couldnâ¤t get the formula to work with that in it.

Text3
[% Complete]

Text4
Left(str(IIf([Current Date]<[Start],"0",IIf([Current
Date]>[Finish],"1",ProjDateDiff([Start],[Current
Date])/[Duration]))*100),4)

Text5
IIf([Text3]>[Text4]*100,"GREEN","RED")


Thanks again, in advance, for your help.

Ginger,
I didn't analyze what you are doing to see if there is an easier way,
there probably is), but if you use your existing formula for text1, then
using this formula in text2 will give what you want. Note that you CAN
leave the "%" sign in your formula for text1.

Text2=IIf([%
Complete]=100,"",IIf(csng(mid([Text1],1,len([Text1])-1))=0,"green",IIf([%
Complete]>csng(mid([Text1],1,len([Text1])-1)),"ok","red")))

Hope this helps.
John
Project MVP