Can I change the predecessor field properties to use the Projected Finish Date when the preceding task is not complete but then use the Actual Finish Date when the preceding task is complete? This way I can keep the original project finish date of the preceding task intact (and you'll be able to see if that task finished early or late) while the succeeding task's start date is properly updated. For example, I have a task for approving a budget and a task for ordering parts. Parts cannot be ordered until a budget is approved. The original duration of the budget task was 30 days. However, the budget took 45 days to get approved. I would like to keep the original duration (and therefore project finish date) values so that others can see the budget was late. Doing this however, shows that the parts order was also late because it was only supposed to take 5 days (and right now it's start date is based on the budget's projected finish, not actual finish). Help!!!
Question
Can a predecessor use the Projected Finish Date for incomplete tasks and the Actual Finish Date for complete tasks?
This topic has been closed for replies.
This is a perfect opportunity to make use of QuickBase's ability to create Formula Fields, which can change their value based on your data. We'll create a new Formula - Work Date field that says "If this task is complete, my value is the Actual Finish Date, but if this task isn't complete, then my value is the Calculated Finish Date.". Then, we'll change the Predecessor field to use this new formula field.
To start off with, I'm assuming that you have some sort of Project Management app already. It sounds like you do, but let's briefly go over the fields that are involved (note that your field names might be a little different):
* Predecessors, a predecessor field* Status, a multiple-choice text field, with the choices "Not Started", "In Process", "On Hold", and "Complete"* Start Date, a Work Date field that says when the task starts. If this task has predecessors, this date cannot be earlier than the latest end date of all of this task's predecessor tasks* Estimated Duration, a Numeric field telling how many days this task isas supposed to take* Estimated Finish Date, a Formula - Work Date field that calculates the finish date from Start Date + Estimated Duration* Actual Finish Date, a Date field that has the date the task was actually completed (if it has been completed)
If you go to Settings > Fields and go into the field properties for the Predecessors field, you'll see it has some special options. A predecessor is told to look at two fields, the start and end date field. Both of these must be Work Date fields. The predecessor says "The value of the start field in a task must be on or after the latest date in the end date field of any of its predecessor tasks".
In this case, we want to have an end date field that changes based on the Status field. Predecessors don't let you do this directly, so we'll have to create a new formula field and use it as the end date field for the predecessor.
In your app, go into the Settings area for your Tasks table. Go to the Fields page, and add a new field. Name this field "End Date for Predecessor", and for Field Type pick "Formula - Work Date" (it's down at the bottom of the list of types). Hit Add Fields and the new field should show up in the list of fields. Click on your new field in the list to edit its properties.
Under the section titled "Formula - Work Date field options", select the option "Edit formula using a text field". Enter this formula into the text box:
If([Status] = "Complete", ToWorkDate([Actual Finish Date]), [Estimated Finish Date])
Your fields might have slightly different names than mine do, so make sure to put the right names inside the square brackets in this formula. Now this gets us closer to what we want - if the task is not complete, this field will just be the estimated finish date (which is calculated from duration and start date). However, if the task is complete, this field will take the value of the Actual Finish Date.
Since this is a "behind the scenes" field, you will probably want to scroll down to the Advanced Options section, and uncheck the checkbox for "Add this field to all new reports".
Save your changes to this field, and go back to the field list. Click on the Predecessors field to edit its properties, find the "End Date Field" option, and change it to our newly-created formula field, "End Date for Predecessor". Save your changes, and you should be all set. Now, if your task is incomplete, future tasks that depend on it will not start until the date in the Estimated Finish Date field. When the task is completed, future tasks will start after the Actual Finish Date instead.
If that was a little bit hard to follow, please let me know and I'd be happy to gather up some screenshots and post them here!
Jeff
To start off with, I'm assuming that you have some sort of Project Management app already. It sounds like you do, but let's briefly go over the fields that are involved (note that your field names might be a little different):
* Predecessors, a predecessor field* Status, a multiple-choice text field, with the choices "Not Started", "In Process", "On Hold", and "Complete"* Start Date, a Work Date field that says when the task starts. If this task has predecessors, this date cannot be earlier than the latest end date of all of this task's predecessor tasks* Estimated Duration, a Numeric field telling how many days this task isas supposed to take* Estimated Finish Date, a Formula - Work Date field that calculates the finish date from Start Date + Estimated Duration* Actual Finish Date, a Date field that has the date the task was actually completed (if it has been completed)
If you go to Settings > Fields and go into the field properties for the Predecessors field, you'll see it has some special options. A predecessor is told to look at two fields, the start and end date field. Both of these must be Work Date fields. The predecessor says "The value of the start field in a task must be on or after the latest date in the end date field of any of its predecessor tasks".
In this case, we want to have an end date field that changes based on the Status field. Predecessors don't let you do this directly, so we'll have to create a new formula field and use it as the end date field for the predecessor.
In your app, go into the Settings area for your Tasks table. Go to the Fields page, and add a new field. Name this field "End Date for Predecessor", and for Field Type pick "Formula - Work Date" (it's down at the bottom of the list of types). Hit Add Fields and the new field should show up in the list of fields. Click on your new field in the list to edit its properties.
Under the section titled "Formula - Work Date field options", select the option "Edit formula using a text field". Enter this formula into the text box:
If([Status] = "Complete", ToWorkDate([Actual Finish Date]), [Estimated Finish Date])
Your fields might have slightly different names than mine do, so make sure to put the right names inside the square brackets in this formula. Now this gets us closer to what we want - if the task is not complete, this field will just be the estimated finish date (which is calculated from duration and start date). However, if the task is complete, this field will take the value of the Actual Finish Date.
Since this is a "behind the scenes" field, you will probably want to scroll down to the Advanced Options section, and uncheck the checkbox for "Add this field to all new reports".
Save your changes to this field, and go back to the field list. Click on the Predecessors field to edit its properties, find the "End Date Field" option, and change it to our newly-created formula field, "End Date for Predecessor". Save your changes, and you should be all set. Now, if your task is incomplete, future tasks that depend on it will not start until the date in the Estimated Finish Date field. When the task is completed, future tasks will start after the Actual Finish Date instead.
If that was a little bit hard to follow, please let me know and I'd be happy to gather up some screenshots and post them here!
Jeff
+4Thank you for offering this solution.
However, there's one huge issue with it and we only discovered it after implementation. While the solution works well for successors and for the immediate predecessor to the currently open task , the "End Date for Predecessor" formula causes ALL completed predecessor tasks to update their Projected Finish Date as well, so the original functionality requested is not met which was to KEEP the original Projected Finish Date of the preceding taskS intact to be able to see if those tasks finished early or late.
The ideal scenario would be to be able to lock the Projected Finish Dates of COMPLETED predecessors so that we could determine whether they were completed early or late, and to only update the Start Date of incomplete successors using the "End Date for Predecessor" formula as outlined above (Projected Finish Date if predecessor is open, or the Actual Finish Date if predecessor is complete).
Can this be done?
Thanks in advance.
Juan
However, there's one huge issue with it and we only discovered it after implementation. While the solution works well for successors and for the immediate predecessor to the currently open task , the "End Date for Predecessor" formula causes ALL completed predecessor tasks to update their Projected Finish Date as well, so the original functionality requested is not met which was to KEEP the original Projected Finish Date of the preceding taskS intact to be able to see if those tasks finished early or late.
The ideal scenario would be to be able to lock the Projected Finish Dates of COMPLETED predecessors so that we could determine whether they were completed early or late, and to only update the Start Date of incomplete successors using the "End Date for Predecessor" formula as outlined above (Projected Finish Date if predecessor is open, or the Actual Finish Date if predecessor is complete).
Can this be done?
Thanks in advance.
Juan
Sign up
Already have an account? Login
Login to the community
Login with Quickbase US – quickbase.com Login with Quickbase EU – quickbase.eu
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.