Best practice / ideas for dealing with recurring task reminders
I'm creating a sheet which will contain a list of about 200 tasks.
These tasks are all recurring, although not with the same frequencies. Some need to take place annually, some are quarterly, some monthly (and not always on the same day of the month).
I need to create some way to track whether tasks have been completed or if they are still outstanding. The ultimate goal is to have notifications for when tasks are due, and have a dashboard to show recently completed tasks and tasks falling due soon.
I want to somehow automate the fact that the tasks are recurring as I don't want to have to copy down new rows for new tasks. Even for tasks that are quarterly or monthly I'm hoping these can just be one row item in the sheet rather then multiple for each month/quarter...
I'm not 100% sure what the best way to do this is, but was thinking something along the lines of:
- Have two sheets, one with task reminders and one to keep track of completed tasks.
- In the Task Reminders sheet have a Due Date automatically calculated based on an Initial Reference Date column and a Frequency column. This may be complex due to the different dates and frequencies but should be possible.
- When a task is marked as complete copy the row to the Completed Tasks sheet, and automatically recalculate the next due date - although I'm not 100% sure how to do this.
Does anyone have any experience of doing something similar? Any better ways of doing this?