Skip to content
ProjectControls.com
Microsoft Project

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

In the Open Standard
David FennemoreMember for 10 years 10 months
Created
3
4
0

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??

Save Follow Follow forum Report
Reply New topic

3 replies

Oldest first
  1. 0
    Jerome Odeh ·
    #1

    Hi David,You need nested IIF

    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.

     

    Report
  2. 0
    David Fennemore ·
    #2

    WELL DONE - Thank You

    WELL DONE - Thank You

    Report
  3. 0
    David Fennemore ·
    #3

    WELL DONE - Thank You

    WELL DONE - Thank You

    Report

Log in or join to reply

ProjectControls.com is free to join. Members can post questions, reply, vote and follow discussions.

Log inJoin free

Existing members: your original username and password still work. Forgot password?