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

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    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

Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    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
    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.
    Genevieve P. Employee Admin

    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
    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.
    Genevieve P. Employee Admin

    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.
    Genevieve P. Employee Admin

    Haha no problem at all! 🙂

    I've done the same thing before, too.

  • This is awesome, Super happy,

  • StephanieStepney
    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.
    Genevieve P. Employee Admin

    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
    StephanieStepney ✭✭✭✭✭
    edited 10/19/22

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

  • Genevieve P.
    Genevieve P. Employee Admin

    Glad to hear it! 🙂

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!