I am trying to create a formula that will calculate the # of days it is taking to resolve a task in comparison with the Target Resolution Date. I want the formula to reflect the health of a task based on if a task has been resolved within a timely manner according to the "Target Resolution Date". I would like to see a relative number in the "Target Resolutions Status" column. If the target resolution date has not passed yet, and the "resolution date" column is blank, the formula would calculate how many days until said target resolution date. Also, if the Target resolution date has passed and the "Resolution Date" column is blank, the formula would calculate (negatively) how many days past due a tasks' target resolution date was. I have been messing around with formulas using IF, ISBLANK, and NETWORKDSAY, but haven't had any luck.

I've attached a snapshot of the columns I am referring to. I would appreciate input!


  • SolutionSal

    Hello! I don't have enough information to write a full formula. I created a similar example that may help if you replace the field names.

    =IFERROR(IF(NETWORKDAYS([Target Date]@row, [Actual Date]@row) > 0, ("Due in " + NETWORKDAYS([Target Date]@row, [Actual Date]@row) + " Days"), (ABS(NETWORKDAYS([Target Date]@row, [Actual Date]@row)) + " Days Late")), "")

    Hope this helps!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!