Tracking Project Completion Date = Todays Date + Total Project Calendar Days Remaining

Kendrick Hanny

A huge thank you to the Community of Smartsheet Contributors that have made Smartsheet a successful tool and viable project management solution.

Here is my Question:

I need help to create a couple of formulas to help me auto track the "Tracking Project Completion Date".

Here is my proposal:

To do this I would need the first formula to read the [Status] column and return the [Start Date] for the first reference of "Not Started". The second formula would need to read the [Status] column and return the [Due Date] for the last reference of "Not Started'. The third formula would need to pull Today's Date + (second formula - first formula). This would give me the today's date + the total number of calendar days (not working days) needed to complete the project = estimated "Tracking Project Completion Date".

The third formula would be linked to a summary sheet so that the key stake holders can see how far ahead or behind the project is. This would allow the team to adjust due dates or parallel paths on tasks in order to achieve the target launch date or move it out to allow for more time. When the summary sheet is opened it should refresh the "Tracking Project Completion Date" cell that it is linked to.

The challenge with the enabled dependencies is that there is no way to auto adjust the due date based on when the task was completed. Additionally, if a task is delayed by 3 or 4 weeks by missing components, etc., it will push the entire project back but not be reflected in the last date in the "Due date" column unless someone manually goes in and updates the due dates to account for the delay.

The proposed "Tracking Project Completion Date" formula would allow us to catch projects that are behind schedule in a summary sheet and adjust dates as needed to meet target launch dates or move the project out. I am aware that I can use automation to inform others of tasks that are past due, but I need the sheet to track down to the calendar day an estimated completion date for the project if the remaining tasks were started today and completed in their predecessor succession.

Any help or ideas on formulas would be greatly appreciated. I have tried to find a similar solution in the community forum for a long time. Thank you!!

Project Sheet:

Kendrick Hanny

@Andrée Starå and @Paul Newcome I reviewed some additional formulas that you had both posted and was able to answer the question. Thank you both for the great support to the community!!

I learned that formula's that require a date output need to be in a column with a date format.

Here is my adaptation based on both of your various postings:

Formula 1 (in a Date helper Column):

=MIN(COLLECT([Start Date]:[Start Date], Status:Status, "Not Started"))

Formula 2 (in a Date helper Column):

=MAX(COLLECT([Start Date]:[Start Date], Status:Status, "Not Started"))

Formula 3 (in a Date helper Column):

=TODAY()

Formula 4 (Remaining Calendar days in Project - in a Text/Number helper Column):

=[Max/Min]67 - [Max/Min]66 (Formula 2 Output - Formula 1 Output) = Remaining Calendar days in Project

Formula 5

= Formula 3 Output (Today ()) + Formula 4 Output (Remaining Calendar days in Project) = Tracking Project Completion Date

Formula 6

= Target Project Completion Goal (manual input at start of project) - Formula 5 (Tracking Project Completion Date) = Calendar Day's + or - that Project is behind or ahead

There might be a better way to consolidate formula's above but I am happy that the desired data output was achieved.

Kendrick Hanny

The parent row has a formula in the status column that auto adjusts the status based on the children status's below. In some instances, if the formula pulled the start date or due date of the parent row it should not matter as it is a summary of the children start dates below.

Kendrick Hanny

@Andrée Starå Would I need to combine a COUNTIF formula with a MIN(COLLECT, MAX(COLLECT formula? Thanks. Have a great weekend.

Kendrick Hanny

@Andrée Starå and @Paul Newcome I reviewed some additional formulas that you had both posted and was able to answer the question. Thank you both for the great support to the community!!

I learned that formula's that require a date output need to be in a column with a date format.

Here is my adaptation based on both of your various postings:

Formula 1 (in a Date helper Column):

=MIN(COLLECT([Start Date]:[Start Date], Status:Status, "Not Started"))

Formula 2 (in a Date helper Column):

=MAX(COLLECT([Start Date]:[Start Date], Status:Status, "Not Started"))

Formula 3 (in a Date helper Column):

=TODAY()

Formula 4 (Remaining Calendar days in Project - in a Text/Number helper Column):

=[Max/Min]67 - [Max/Min]66 (Formula 2 Output - Formula 1 Output) = Remaining Calendar days in Project

Formula 5

= Formula 3 Output (Today ()) + Formula 4 Output (Remaining Calendar days in Project) = Tracking Project Completion Date

Formula 6

= Target Project Completion Goal (manual input at start of project) - Formula 5 (Tracking Project Completion Date) = Calendar Day's + or - that Project is behind or ahead

There might be a better way to consolidate formula's above but I am happy that the desired data output was achieved.

Andrée Starå

@Kendrick Hanny

Excellent!

Glad you got it working!

You're more than welcome!

✅Please support the Community by marking your post with the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!