locked
Need custom field formula assistance RRS feed

  • Question

  • I am trying to create two separate custom fields based on a formulas and can't seem to make it work. 1st formula - Want to create a graphical indicator flag based on a task being more then 3 days late from current [Finish] date. So if Finish date is set at 2/1/16 and today's date is 2/5/16, a red flag would be placed in the custom field because it passed the criteria of more then 3 days late. I was thinking I could do an If statement using this thought process (Iif( [Finish] > [current date] + 3, then Red, else Green) but it certainly isn't working. Any help on this is appreciated. 2nd formula I don't even know how to construct but what I am trying to do is determine movement on a new milestone field that I created. So, if Deployment Milestone (setup with lookup table with defined values), is anything other then N/A (which is default value) and the finish date changed, then flag the task. The ultimate goal is to see impacts on overall program if a deployment is missed. I am assuming I would need to use baseline finish to compare against finish date. Again, thoughts on how to accomplish? Thank you and let me know if more information is needed.
    I should also mention this is for MS Project 2010 Professional
    • Edited by tms5555 Tuesday, February 9, 2016 3:35 PM
    Tuesday, February 9, 2016 3:34 PM

Answers

  • For the second formula, you have to define what is your comparison basis for tracking if a milestone finish date changed. You could use the baseline, meaning setting a baseline and tracking any changes for milestones "NA" finish date. Then the formula would be the following (assuming text1 field contains your lookup table with the NA value among the others.

    IIf([Text1]<>"NA" And [Baseline Finish]<>[Finish];1;0)


    Hope this helps,


    Guillaume Rouyre, MBA, MVP, MCC |

    • Marked as answer by tms5555 Tuesday, February 9, 2016 5:16 PM
    Tuesday, February 9, 2016 3:49 PM
  • tms5555,

    Well, a little more information is needed but we can make some assumptions.

    You make no mention, nor does your formula consider whether a task is complete or not. I assume if it is complete, it should have a green flag. So try this formula:

    Text1 = IIf([% Complete]=100,"green",IIf([Current Date]>[Finish]+3,"red","green"))

    Okay, the next issue is, that formula works with calendar days. Is that what you want, or do you really want 3 working days?

    The current date is by default 8:00 am whereas a task's finish date is normally at 5:00 pm. That will impact when the threshold is crossed between red and green. What is your intent?

    With regard to your second formula, again, more information is needed. You say you created a new milestone field with a lookup table. What field did you customize (e.g. Text, number, etc.)? Then you say you want something to happen when the finish date is changed and you also mention the baseline finish. Are you looking for a comparison of the finish date to the baseline finish date? Are you aware there is a Finish Baseline field?

    John

    • Marked as answer by tms5555 Tuesday, February 9, 2016 5:16 PM
    Tuesday, February 9, 2016 4:21 PM

All replies

  • Hi,

    For the first formula, try to use the ProjDateDiff function. This will return the number of days between 2 dates.

    IIf(ProjDateDiff([Current Date];[Finish])>3;1;0)


    Hope this helps,


    Guillaume Rouyre, MBA, MVP, MCC |

    Tuesday, February 9, 2016 3:43 PM
  • For the second formula, you have to define what is your comparison basis for tracking if a milestone finish date changed. You could use the baseline, meaning setting a baseline and tracking any changes for milestones "NA" finish date. Then the formula would be the following (assuming text1 field contains your lookup table with the NA value among the others.

    IIf([Text1]<>"NA" And [Baseline Finish]<>[Finish];1;0)


    Hope this helps,


    Guillaume Rouyre, MBA, MVP, MCC |

    • Marked as answer by tms5555 Tuesday, February 9, 2016 5:16 PM
    Tuesday, February 9, 2016 3:49 PM
  • tms5555,

    Well, a little more information is needed but we can make some assumptions.

    You make no mention, nor does your formula consider whether a task is complete or not. I assume if it is complete, it should have a green flag. So try this formula:

    Text1 = IIf([% Complete]=100,"green",IIf([Current Date]>[Finish]+3,"red","green"))

    Okay, the next issue is, that formula works with calendar days. Is that what you want, or do you really want 3 working days?

    The current date is by default 8:00 am whereas a task's finish date is normally at 5:00 pm. That will impact when the threshold is crossed between red and green. What is your intent?

    With regard to your second formula, again, more information is needed. You say you created a new milestone field with a lookup table. What field did you customize (e.g. Text, number, etc.)? Then you say you want something to happen when the finish date is changed and you also mention the baseline finish. Are you looking for a comparison of the finish date to the baseline finish date? Are you aware there is a Finish Baseline field?

    John

    • Marked as answer by tms5555 Tuesday, February 9, 2016 5:16 PM
    Tuesday, February 9, 2016 4:21 PM
  • Thank you, I will give that a try!
    Tuesday, February 9, 2016 4:26 PM
  • Thank you!
    Tuesday, February 9, 2016 4:27 PM
  • John,

    Yes, the first formula should take into account if task is complete, so thank you on that. It should be working days and not calendar days. The intent date should be based on finish date so taking this into account, are you suggesting maybe it should be 4 days to account for the task finish date being 5pm? Just wanted to make sure I understood what you were asking. For the second formula, I customized a text field. I am aware of the finish baseline field so I am assuming I would need to compare the finish date to the baseline finish date in order to do what is being asked for. The intent being requested by my management is to know when a deployment date is missed into an environment to see how it will impact the individual project as well as the entire program. I couldn't think of another way to give them this then to create a custom text field set as a milestone with the lookup table having the environment names + N/A since many tasks are not associated to a deployment and then use that field to compare against finish date and baseline finish. Any other thoughts on how to accomplish this so it can be reported on, are greatly appreciated. Hopefully I answered all the questions.

    Tuesday, February 9, 2016 4:41 PM
  • tms6666,

    Tell you what, to keep things simple, just go with 4 days instead of 3. Since it is working time, use Guillaume's formula with the 4 day delta. It won't be exact since the finish will be at 5:00 pm and the current date will be 8:00 am the next working day but to make it exact would require a more complex approach.

    For your second formula, it looks like Guillaume has provided a simple approach and I'd go with that.

    John

    Wednesday, February 10, 2016 1:50 AM
  • tms5555,

    I had a couple other thoughts after posting my last reply.

    First, correcting for the 5:00 pm versus 8:00 am issue wouldn't be all that complex. This formula should do it.

    ProjDateDiff([Finish],ProjDateAdd([Current Date],[Minutes Per Day]))/[Minutes Per Day]

    I also realized that Guillaume's first formula has a slight error. Project stores date values in minutes. Therefore the ProjDateDiff formula will yield minutes, not days, so the IF statement will flag  as "red" any finish date that is greater than 3 minutes from the current date.

    With regard to the second formula, I'm guessing you only want to flag tasks that are scheduled to miss the deployment date when they are "late". For example, if the finish date of a task is pulled in (i.e. earlier) it will not put the deployment date in jeopardy, so it shouldn't be flagged. In that case I would suggest the following formula;

    IIf([Text1]<>"NA" And [Finish Variance]>0,1,0)

    Hope this helps.

    John

    Wednesday, February 10, 2016 3:48 PM
  • Thank you for the followup John. I will give these a try. And yes, I only want to flag those dates where it would be considered late. If a deployment came in earlier, this would not be a flag management wants to see. Thanks again!
    Wednesday, February 10, 2016 6:14 PM
  • tms5555,

    You're welcome and thanks for the feedback.

    John

    Wednesday, February 10, 2016 7:15 PM