In Excel I am able to enter a formula into a single cell directly. What my mission is here using MS Project, is to calculate the days between the start of a task against the current date. Using the column approach is not optimal. I would like this calculation to only target the parent tasks and not the children. In addition, if one variable is null in the formula I am seeing the word '#ERROR' displayed. I've tried using an IIF statement to replace the #ERROR with an empty string or space without success. Saving Changes...
I understand that you are customizing the project plan in MS Project and do not want to use the inbuild Current Date column in MS Project. Are you trying to calculate in the column where you have inserted the dates? Did you try inserting 0 where you have null values?
MS Project can auto-calculate the Duration depending on the "Start" against the "Finish" dates. Also, please make sure the dates are there at the parent level.
...
1 reply by Albert Markert
Nov 09, 2023 1:39 PM
Albert Markert
...
Hello Nadia,
I inserted 0's in place of the empty cells and it returns a number, still not what I was hoping for. Here is the simple formula: IIf(([Start]""),Now()-[Start],""). I was hoping this would leave an empty cell if the start date was not filled in. With this new info ... any thoughts?
See the problem is the duration calculated only address the difference from the initial start date to the finish date. As you know that is a snap shot of the children tasks with respect to their individual dates totaled at the parent level. But I am looking for the start date to the current date. Trying to show the delay in completing the task and for how long is has taken from the start to now.
I understand that you are customizing the project plan in MS Project and do not want to use the inbuild Current Date column in MS Project. Are you trying to calculate in the column where you have inserted the dates? Did you try inserting 0 where you have null values?
MS Project can auto-calculate the Duration depending on the "Start" against the "Finish" dates. Also, please make sure the dates are there at the parent level.
Hello Nadia,
I inserted 0's in place of the empty cells and it returns a number, still not what I was hoping for. Here is the simple formula: IIf(([Start]""),Now()-[Start],""). I was hoping this would leave an empty cell if the start date was not filled in. With this new info ... any thoughts?
See the problem is the duration calculated only address the difference from the initial start date to the finish date. As you know that is a snap shot of the children tasks with respect to their individual dates totaled at the parent level. But I am looking for the start date to the current date. Trying to show the delay in completing the task and for how long is has taken from the start to now. Saving Changes...
These kinds of problems are always very difficult to solve without seeing how the data is organized and the formulas. For instance are you are using the DateDiff function, why do you have null variables in the first place? You can always export to Excel where you can easily find and replace blanks with zeros, and have many formula and logical operation options which may or may not work depending on the data, structure, and how the calculations will be used.
...
1 reply by Albert Markert
Nov 09, 2023 2:43 PM
Albert Markert
...
0's was a suggestion by the previous responder. No longer used ... trial and error. I don't want to export into another tool, very distracting when presenting to the executive branch. If this tool cannot provide what I need I may use something else. MS Project has been around for decades ... figured there was a way to modify the plan and add in some custom formulas outside the standard approach. I was using SmartSheet previously and it could handle all custom formulas and calculations easily. Due to funding concerns the choice was made to select the enterprise suite of tools from MS.
These kinds of problems are always very difficult to solve without seeing how the data is organized and the formulas. For instance are you are using the DateDiff function, why do you have null variables in the first place? You can always export to Excel where you can easily find and replace blanks with zeros, and have many formula and logical operation options which may or may not work depending on the data, structure, and how the calculations will be used.
0's was a suggestion by the previous responder. No longer used ... trial and error. I don't want to export into another tool, very distracting when presenting to the executive branch. If this tool cannot provide what I need I may use something else. MS Project has been around for decades ... figured there was a way to modify the plan and add in some custom formulas outside the standard approach. I was using SmartSheet previously and it could handle all custom formulas and calculations easily. Due to funding concerns the choice was made to select the enterprise suite of tools from MS. Saving Changes...
Perhaps I am misinterpreting the question, but wouldn't this formula work: IIf([Summary]=Yes,[Current Date]-[Start],0) ?
Kiron
...
1 reply by Albert Markert
Nov 10, 2023 10:36 AM
Albert Markert
...
It should really pivot on the existence of the start date. Not sure how selecting a different field will change the outcome. Even with 0 as the alternate action in the conditional it will still show '#ERROR'
Perhaps I am misinterpreting the question, but wouldn't this formula work: IIf([Summary]=Yes,[Current Date]-[Start],0) ?
Kiron
It should really pivot on the existence of the start date. Not sure how selecting a different field will change the outcome. Even with 0 as the alternate action in the conditional it will still show '#ERROR' Saving Changes...
From what I have discovered and the kind suggestion from those who responded I am figuring out this is not possible and I will need to use the column formula and accept the #ERROR or the '0' presentation. Saving Changes...
"When one door closes another door opens; but we often look so long and so regretfully upon the closed door that we do not see the ones which open for us."
