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!

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!