Automating future due dates, counting cell changes

Using Smartsheet as a task management system for a small team, want to be able to enter tasks, set due dates and notifications, and record completion

For Recurring tasks I want to automate the future due dates once it the task has been recorded as completed

Current set up

  1. Enter form with a 'due date' and if recurring, enter the period (i.e. Weekly, Quarterly)
  2. Based on period selected, workflow enters 'number of days' in a new column (i.e. Weekly = 5)
  3. Column Formula to take the 'Due Date' and add the 'number of days' to get 'Next due date'
  4. Also have a workflow to change the status to 'pending' (from 'complete' if applicable) the 'number of days' prior to the 'next due date'

Problem is this will only work once. I am thinking I can add a 'cycle' column starting at 1 which can +1 each time the status changes to 'Complete' then do a column formula to multiply the 'number of days' by the 'cycles' to keep feeding that 'next due date' column accurately.

  1. Anyone know how to count how many times a cell value changes, and enter that number in a cell
  2. OR, anyone have a better way to achieve the same outcome for recurring tasks?

Thank you!


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!