% Complete based on Todays date, Start and End dates

AGillespie
AGillespie ✭
edited 12/09/19 in Formulas and Functions

I'm looking to return a % complete based on the start and end dates and the current date.

So if a task starts on the 17/7 and ends on the 31/7, and today is the 24/7 - the % complete would be 53%. - (I want to have this as a separate column so I can keep dependencies on the actual % complete column)

=(MIN(TODAY(),*finish date*)-*start date*+1)/*finish date*-*start date*+1)

The formula above would work in Excel, but is there a smartsheet alternative? Any help appreciated.

Tags:
· Share on FacebookShare on Twitter

Comments

  • AGillespie
    AGillespie ✭

    Ok, I've found a new problem -

    This formula works if the task has started/ currently being done :

    =(ROUND((MIN(TODAY(), FINISH1) - START1 + 1) / SUM((FINISH1 - START1 + 1) / 100)))

    The formula below works for tasks not yet started, would show "Not Started", and blank if the number was above 0.

    =IF((ROUND((MIN(TODAY(), FINISH2) - START2 + 1) / SUM((FINISH2 - START2 + 1) / 100))) < 0, "Not Started","")

    How do I write a formula to show a number (which would be the possible % complete) when the value is false, and "Not Started" when the value is true.

    · Share on FacebookShare on Twitter
  • 12vanblart
    12vanblart ✭

    In your second formula, if you replace the last `""` with the function you want to calculate, it will return a number instead of a blank value. 

    =IF((ROUND((MIN(TODAY(), FINISH2) - START2 + 1) / SUM((FINISH2 - START2 + 1) / 100))) < 0, "Not Started", (ROUND((MIN(TODAY(), FINISH2) - START2 + 1) / SUM((FINISH2 - START2 + 1) / 100))))

    · Share on FacebookShare on Twitter
  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    =IF((ROUND((MIN(TODAY(), FINISH2) - START2 + 1) / SUM((FINISH2 - START2 + 1) / 100))) <= 0, "Not Started",(ROUND((MIN(TODAY(), FINISH2) - START2 + 1) / SUM((FINISH2 - START2 + 1) / 100))))

     

    Give this a try. If the value is <= 0, it will say "Not Started". Otherwise it will input the value.

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

    · Share on FacebookShare on Twitter
  • AlexK_Emburse
    AlexK_Emburse ✭

    I ended up using this formula, which I like because it requires Start/End dates to be set, and rather than saying "Not Started" I opted for 0% completion.

    I feel this logic is simpler and cleaner as well. I'm sure it could be even better, but I'm no developer.

    =IF(OR(ISBLANK(Start2), ISBLANK(Finish2)), "Add Dates", IF(TODAY() > Finish2, 1, IF(TODAY() < Start2, 0, ((TODAY() - Start2) / (Finish2 - Start2)))))

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!