Guild of Project Controls: Compendium | Roles | Assessment | Certifications | Membership

Tips on using this forum..

(1) Explain your problem, don't simply post "This isn't working". What were you doing when you faced the problem? What have you tried to resolve - did you look for a solution using "Search" ? Has it happened just once or several times?

(2) It's also good to get feedback when a solution is found, return to the original post to explain how it was resolved so that more people can also use the results.

Formula Help comparing multiple finish dates--Anyone up to the challenge? ;)

3 replies [Last post]
David Fennemore
User offline. Last seen 8 years 14 weeks ago. Offline
Joined: 17 Dec 2015
Posts: 4
Groups: None

I want to create a custum text column that will visually show if a tasks finish dates have been slipping or not by comparing Forecast Finish(Finish) to previous Months Finish (Finish1) and to Last weeks Finish(Finish2).

My thoughts were to use the IIF function in MS project to indicate the following:

Finish>Finish1(previous months finish) & >Finish2 (Previous Weeks Finish) = "> >". The first > signifies the task slipped compared to the previous month and the second > signifies that the task has continued to slipped compared to the previous weeks finish date.

Finish < Finish1, < Finish2 = "< <" ----Indicates the task has been moving to complete earlier

Finish > Finish1, <Finish2 = "> <" -----Indicates the task slipped from last month but is being pulled back in

Finish = Finish1, >Finish2 = "= >" -----Indicates that the task had no change from last month but slipped from last week

ETC..........

I have been trying the create the formula for this but for some reason I keep getting and error. Is anyone up to the challenge??

Replies

David Fennemore
User offline. Last seen 8 years 14 weeks ago. Offline
Joined: 17 Dec 2015
Posts: 4
Groups: None

WELL DONE - Thank You

David Fennemore
User offline. Last seen 8 years 14 weeks ago. Offline
Joined: 17 Dec 2015
Posts: 4
Groups: None

WELL DONE - Thank You

Jerome Odeh
User offline. Last seen 7 weeks 4 days ago. Offline
Joined: 19 Jan 2004
Posts: 102

Hi David,

You need nested IIF Statements. The outline nested IIF is:

IIf( Exp1, Ans1, IIf( Exp2, Ans2, IIf( Exp3, Ans3, IIf( Exp4, Ans4, IIf( Exp5, Ans5, IIf( Exp6, Ans6, IIf( Exp7, Ans7, "= =" ) ) ) ) ) ) )

Replace the following in the above outline.

  1. Exp1 = ([Finish]>[Finish1]) And ([Finish]>[Finish2])
  2. Ans1 = "> >"
  3. Exp2 = ([Finish]<[Finish1]) And ([Finish]<[Finish2])
  4. Ans2 = "< <"
  5. Exp3 = ([Finish]>[Finish1]) And ([Finish]<[Finish2])
  6. Ans3 = "> <"
  7. Exp4 = ([Finish]=[Finish1]) And ([Finish]>[Finish2])
  8. Ans4 = "= >"
  9. Exp5 = ([Finish]>[Finish1]) And ([Finish]=[Finish2])
  10. Ans5 = "> ="
  11. Exp6 = ([Finish]=[Finish1]) And ([Finish]<[Finish2])
  12. Ans6 = "= <"
  13. Exp7 = ([Finish]<[Finish1]) And ([Finish]=[Finish2])
  14. Ans7 = "< ="

I think I have covered all possible 8 scenarios for you. See snippet below for working proof.

3213
formula.png

 

3214
gantt.png