Can Smartsheet forecast end date base on % complete input

Vivien Chong

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?

Genevieve P.

Hi @Vivien Chong

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.

Hi @Vivien Chong

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

Vivien Chong

@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.

Genevieve P.

Hi @Vivien Chong

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

JL2022

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

Genevieve P.

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

JL2022

@Genevieve P. you are a HERO!

Genevieve P.

Haha no problem at all! 🙂

I've done the same thing before, too.

Venn Cannon

This is awesome, Super happy,

StephanieStepney

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?

Genevieve P.

Hi @StephanieStepney

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

StephanieStepney

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

Genevieve P.

Glad to hear it! 🙂