Check and Uncheck boxes based on recurring tasks being complete or not

Hi Community,

I have a list of tasks that each need to be done either monthly, quarterly or annually. When the task is complete, a checkbox needs to be checked off to indicate that it was taken care of. (This checkbox is used in multiple reports that help us see what is outstanding.)

However, being that these are recurring tasks, I want the box to automatically uncheck, either a few days, weeks or a month before the task is due again.

Ex: a task is due on the 15th of each month. on 2/15 the task was completed, so the box is manually checked off. The next month, on 3/10, I want the box to uncheck so that it appears on my "upcoming tasks" report.


Currently I have four columns that I'm working with:

  1. Frequency - dropdown list - (Monthly, Quaterly, yearly)
  2. Last Task - Date column - Whenever they do the task they put the date in
  3. Next Task - Column Formula - Looks at the Frequency Column and Last Task column and calculates when the Task is due again
  4. Completed - Check box column.


Please let me know if you have any good ideas or workarounds!


Thanks!

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!