Can Smartsheet forecast end date base on % complete input
For example:
Start Date: 10 Jan 2022 (Mon)
End Date: 14 Jan 2022 (Fri)
For simple calculation, working day duration is 5 days. Each day should achieve 20%.
Today is 12 Jan 2022 and I should achieved 60%, but I only manage to complete 40%.
Therefore I will need an additional one day to complete the task.
So the forecast end date should be 17 Jan 2022 (working day).
How should I calculate the forecast end date?
Best Answer

Yes, you could set up a third Date column to return the Forecasted End Date based on the actual % Complete of that task!
First, you'd want to state that if Today is less than the Start Date (so the task hasn't started yet), OR if the % Complete is at 100%, return the End Date that's displayed in your project, as that's expected.
=IF(OR(TODAY() < [Start Date]@row, [% Complete]@row = 1), [End Date]@row,
Then we can use the WORKDAY Function to add a number of days on to Today to see the possible end date, based on the percent left.
WORKDAY(TODAY(), formula to get number to add)
To get the number of days to add on, we'll want to take the NETWORKDAYS between the Start and End date of the task, then subtract from this total task duration the NETWORKDAYS of the task multiplied by the % Complete. This will tell us how many days are left to get to 100%!
We'll want to wrap this in a ROUND Function so that the WORKDAY part of the formula has a whole number to add on to Today's date.
Try this:
=IF(OR(TODAY() < [Start Date]@row, [% Complete]@row = 1), [End Date]@row, WORKDAY(TODAY(), ROUND(NETWORKDAYS([Start Date]@row, [End Date]@row)  (NETWORKDAYS([Start Date]@row, [End Date]@row) * [% Complete]@row))))
In my sheet above I also have an Expected % Complete to compare percentages, but it's not needed for the Forecast End Date Formula. If you'd find this helpful though, this is the formula I used:
=IF(TODAY() < [Start Date]@row, 0, IF(TODAY() > [End Date]@row, 1, NETWORKDAYS([Start Date]@row, TODAY()) / NETWORKDAYS([Start Date]@row, [End Date]@row)))
Cheers!
Genevieve
Answers

Yes, you could set up a third Date column to return the Forecasted End Date based on the actual % Complete of that task!
First, you'd want to state that if Today is less than the Start Date (so the task hasn't started yet), OR if the % Complete is at 100%, return the End Date that's displayed in your project, as that's expected.
=IF(OR(TODAY() < [Start Date]@row, [% Complete]@row = 1), [End Date]@row,
Then we can use the WORKDAY Function to add a number of days on to Today to see the possible end date, based on the percent left.
WORKDAY(TODAY(), formula to get number to add)
To get the number of days to add on, we'll want to take the NETWORKDAYS between the Start and End date of the task, then subtract from this total task duration the NETWORKDAYS of the task multiplied by the % Complete. This will tell us how many days are left to get to 100%!
We'll want to wrap this in a ROUND Function so that the WORKDAY part of the formula has a whole number to add on to Today's date.
Try this:
=IF(OR(TODAY() < [Start Date]@row, [% Complete]@row = 1), [End Date]@row, WORKDAY(TODAY(), ROUND(NETWORKDAYS([Start Date]@row, [End Date]@row)  (NETWORKDAYS([Start Date]@row, [End Date]@row) * [% Complete]@row))))
In my sheet above I also have an Expected % Complete to compare percentages, but it's not needed for the Forecast End Date Formula. If you'd find this helpful though, this is the formula I used:
=IF(TODAY() < [Start Date]@row, 0, IF(TODAY() > [End Date]@row, 1, NETWORKDAYS([Start Date]@row, TODAY()) / NETWORKDAYS([Start Date]@row, [End Date]@row)))
Cheers!
Genevieve

@Genevieve P. that's awesome. Let me try the formula.
On the other hand, if it's a project with dependencies, I believe we can see the forecasted end date for each task, and will need other alerts if the task will impact the final delivery of the project? Would welcome any input on this too.

Yes, you're right! If you have Dependencies enabled then your End Dates will shift and change as their Predecessors change.
I would suggest looking into adding a Baseline to your project sheet. Then you can set up an alert if your last task has a Variance that goes beyond a specific number of days. See: Set Baselines on a Project Sheet and Keep Your Projects on Track with Baselines
Cheers,
Genevieve

Hi @Genevieve P.  I tried the formula you provided for forecasted end date but it is returning #INVALID COLUMN VALUE. Can you assist?

Hi @JL2022
Make sure that the column you're typing the formula into is set as a Date type of column. Let me know if that fixed the error!
Cheers,
Genevieve

@Genevieve P. you are a HERO!

Haha no problem at all! 🙂
I've done the same thing before, too.

This is awesome, Super happy,

Hi @Genevieve P. I tried your formula
=IF(TODAY() < [Start Date]@row, 0, IF(TODAY() > [End Date]@row, 1, NETWORKDAYS([Start Date]@row, TODAY()) / NETWORKDAYS([Start Date]@row, [End Date]@row)))
but I keep getting a #DATE EXPECTED
when I tried the other formula
=IF(OR(TODAY() < [Start Date]@row, [% Complete]@row = 1), [End Date]@row, WORKDAY(TODAY(), ROUND(NETWORKDAYS([Start Date]@row, [End Date]@row)  (NETWORKDAYS([Start Date]@row, [End Date]@row) * [% Complete]@row))))
I get really weird end dates. What am I doing wrong?

There are a few things to check here, but the first thing I noticed is that your % Complete column is not set up as a % column.
Right now you have 100 , 25, 10, instead of 1, 0.25, 0.1 and then the format of percent applied to the column. This means that when the formula looks for [% Complete]@row = 1, it looks for 1, and not your cell that says 100.
It also means that it's multiplying your NetWorkdays by the actual number in the % Complete column instead of by the Percent. Try adjusting your values in that column and let me know if that worked for you!
Cheers,
Genevieve

@Genevieve P. YOU ARE A ROCK STAR!!!! Yes, that worked perfect!!!

Glad to hear it! 🙂
Help Article Resources
Categories
Check out the Formula Handbook template!