Formula: percentage based on dates


  • I'd like to pop this to the top of the queue again.

    The formula that I am using is

    =IFERROR(IF(ISBLANK(Start@row), "0", IF(TODAY() > Finish@row, 1, IF(TODAY() < Start@row, 0, IF(AND(TODAY() > Start@row, TODAY() < Finish@row), ((1 / (Finish@row - Start@row)) * (Finish@row - TODAY()) / 1), IF(TODAY() > Start@row, 1, IF(TODAY() < Finish@row, 0, "")))))), "")

    This works well in that the percentage does change based upon the relationship between the start date and today's date. However, what puzzles me is that, if I were to extend out the end date for the task, I would expect the percentage complete to decrease. However, in this formulation, the longer the task duration, the MORE complete this formula shows the task to be in the % complete column.

    For instance: a 3 day task, one day in, shows 42% complete. Extend the end date to make that task 11 days and I get an 87% completion result.

    Been trying the math I know to compensate for this but without luck. Hoping someone with greater skills can advise.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @Todd Smelser Try this...

    =IFERROR(MAX(MIN(1, (TODAY() - Start@row) / (Finish@row - Start@row)), 0), "")

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!