none
Custom Field formula returning #Error RRS feed

  • Question

  • Hi

    I hope someone can help

    I am trying to make a enterprise custom field formula at project level which returns the product launch date based on a task level enterprise custom field.

    I have created a task level custom field which I use to identify the task which is the product launch task using "Yes" from a lookup table.

    I then created a Project level custom field with a formula:

    IIf([Launch Task] = "Yes", [Finish], "")

    which I hoped would give me the finish date of the task with "Yes" in the launch date column, however it is giving me a #error.

    Can anyone see the error in my formula?

    Thanks for your help in advance

    Matt

    Thursday, January 4, 2018 6:31 PM

Answers

  • Matt22365 --

    Good news.  Since I have Project Online, I ran a quick test and can confirm that what you want to do CAN be done.  Here is how I handled this, and you will need to do this as well:

    1.  I created an enterprise custom task Flag field named Product Launch Task.  I specified a Yes value in this field for the task in the project that contains the launch date.

    2.  I created an enterprise custom task Date field named Task Launch Date.  I entered a formula for the field and then selected the Rollup (Maximum) value in the Calculation for Summary Rows section so that the date calculated in this field will roll up to every summary task in the project, including the Project Summary Task.  You MUST select the Rollup value in order for this to work.  The formula I entered in the field was:

    IIf([Product Launch Task], [Finish], ProjDateValue("NA"))

    3.  I created an enterprise custom project Date field named Project Launch Date.  In this field I entered the following formula:

    [Task Launch Date]

    After creating all of the above, I opened a project and set a Yes value in the Product Launch Task field for one of the tasks.  I saw the Task Launch Date automatically calculated for only this task, while all of the other tasks still had an NA value in them.  I then clicked Project > Project Information and examined the Project Launch Date field.  Guess what?  The correct date was in this field.

    So, you can do what you wish, but you need to be careful to follow ALL of the steps I have detailed above.  Hope this helps.


    Dale A. Howard [MVP]

    • Marked as answer by Matt22365 Thursday, January 4, 2018 10:26 PM
    Thursday, January 4, 2018 9:15 PM
    Moderator

All replies

  • Matt22365,

    The short answer is, sorry, can't do. Since the Launch Task field exists at task level, it cannot be accessed with a Project level custom field, just like a resource custom field can't be accessed in a task field formula. In essence, the error message is telling you, "I don't see a Launch Task field at Project level".

    So what can you do? I don't use Project Server so I don't know if there is another way to do it but a simple macro will find the Launch Task field and enter the finish date into your Project level custom field. If you give me your field names, I'll write the code for you.

    John

    Thursday, January 4, 2018 8:01 PM
  • Hi John

    Thank you for your response.

    The field names are as the formula suggests Launch task. Identifier of the only task being a launch task is Yes and the column I need the date from is Finish

    Could this be set up to automatically update upon opening the project?

    Thanks for your help

    Matt

    Thursday, January 4, 2018 8:34 PM
  • Matt22365 --

    Good news.  Since I have Project Online, I ran a quick test and can confirm that what you want to do CAN be done.  Here is how I handled this, and you will need to do this as well:

    1.  I created an enterprise custom task Flag field named Product Launch Task.  I specified a Yes value in this field for the task in the project that contains the launch date.

    2.  I created an enterprise custom task Date field named Task Launch Date.  I entered a formula for the field and then selected the Rollup (Maximum) value in the Calculation for Summary Rows section so that the date calculated in this field will roll up to every summary task in the project, including the Project Summary Task.  You MUST select the Rollup value in order for this to work.  The formula I entered in the field was:

    IIf([Product Launch Task], [Finish], ProjDateValue("NA"))

    3.  I created an enterprise custom project Date field named Project Launch Date.  In this field I entered the following formula:

    [Task Launch Date]

    After creating all of the above, I opened a project and set a Yes value in the Product Launch Task field for one of the tasks.  I saw the Task Launch Date automatically calculated for only this task, while all of the other tasks still had an NA value in them.  I then clicked Project > Project Information and examined the Project Launch Date field.  Guess what?  The correct date was in this field.

    So, you can do what you wish, but you need to be careful to follow ALL of the steps I have detailed above.  Hope this helps.


    Dale A. Howard [MVP]

    • Marked as answer by Matt22365 Thursday, January 4, 2018 10:26 PM
    Thursday, January 4, 2018 9:15 PM
    Moderator
  • Hi Dave

    That works brilliantly, thank you very much! I now need to figure out how it works for future reference.

    Thanks again

    Matt

    Thursday, January 4, 2018 10:26 PM
  • Dale,

    Thanks for jumping in. Your approach works around one of the custom field limitations of Project. Kudos!

    John

    Thursday, January 4, 2018 10:38 PM
  • Hi Dale

    Thank you again for your solution to my #error issue. I was wondering if you could help me take the formula a little further with another custom field please?

    Essentially my end goal is a scatter graph (in Power BI) which plots the highest risk score from all projects vs how many weeks until launch.

    I have sorted the risk custom field but I need the X axis to be weeks until launch.

    I created a version of this which plots risk against how many weeks until the risk needs to be completed but to really show what I want I need the risks to be plotted against the launch date rather than the task finish date.

    My attempt is ([Task Launch Date]  -  Today())  \  7)  *  -1, where task launch date is the field you helped me create earlier, Today() is todays date, /7 to convert to weeks and *-1 to switch the sign of the numbers. The -1 is there as I want 0 days til launch to be on the far right of the x axis and (as far as I know) there is no way to reverse the order of the x axis on power BI.

    This formula states there is an error, any ideas?

    Thanks again for your help

    Matt

    Friday, January 5, 2018 8:24 AM
  • Hi

    I managed to sort this one myself and thought I would let you know the solution before you spent time on helping me.

    The correction was changing the Today() to Now(), the formula then worked creating a project custom field which identified the difference in time between today and the launch task date.

    Thanks for your time

    Matt

    Friday, January 5, 2018 11:54 AM