Recurring Tasks in Notion

The Context

For the new year, I’ve been designing a task system to keep me organised and focused on achieving my goals.
As part of this I took a look at a Notion template from Thomas Frank and saw that he had a way of creating recurring tasks, which I thought was cool, but his example was too complex for my use-case.

The Requirements

I wanted a Notion database with a Next Occurrence formula, which takes in a Due Date (a Date field representing initial occurrence, which is also used as the due date for non-reoccurring tasks), and a Repeat Interval (a Select field which can be Daily|Alternate Days|Weekly|Fortnightly|Monthly).
If a Repeat Interval is specified, then the task is “recurring” and a Next Occurrence should be calculated to find which day it should show up in my task list.
If the Due Date is today or in the future then the Next Occurrence just uses the Due Date, as the first occurrence hasn’t happened yet.
If the Due Date is in the past, then the Next Occurrence should count forward from the Due Date until it hits a date that is today or in the future.
The answer was surprisingly fiddly to get right, so I thought I would share the solution.

The Solution

The idea is to calculate the number of intervals (represented by the Repeat Interval) there are between today and the Due Date (in the past). Then add that number of days/ weeks/ months (depending on the Repeat Interval), adding twice that amount for the cases of Alternate Days or Fortnightly.
Because dateBetween always returns an integer (floored), you cannot simply apply ceil to ensure that the calculated Next Occurrence is in the future. For example, if the Repeat Interval is Weekly and the Due Date is yesterday, dateBetween(now(), prop("Due Date"), "weeks") is 0, which means that there is a detected interval of 0 weeks in the past (so adds 0 weeks to the Due Date ❌). The same flaw does not exist for dateBetween(now(), prop("Due Date"), "days") (used for Daily and Alternate Days), so I cannot just do a +1. Perhaps there is a more elegant solution, but I didn’t want to dedicate more time to it.
To account for this I simply check if the new calculated Next Occurrence is still in the past, and if so, add one to the number of occurrences I need to add to it.

The Code


lets(
	today, now().formatDate("YYYY-MM-DD").parseDate(),
  
	dueDate, prop("Due Date"),
  
	intervalType, prop("Repeat Interval"),
  
	periodType, if(intervalType == "Daily", 
		"days",
		if(intervalType == "Alternate Days", 
			"days",
			if(intervalType == "Weekly", 
				"weeks",
				if(intervalType == "Fortnightly", 
					"weeks",
					if(intervalType == "Monthly", 
						"months",
						"Invalid Interval Type"
					)
				)
			)
		)
	),
  
	repeatInterval, if(or(intervalType == "Fortnightly", intervalType == "Alternate Days"), 2, 1),
  
	intervalsBetweenFirstDueAndNow, dateBetween(today, dueDate, periodType) / repeatInterval,
  
	isDueDateInFutureOrToday, dateBetween(today, dueDate, "days") <= 0,
	
	isCalculatedNextDateInPast, dateBetween(today, dateAdd(dueDate, ceil(intervalsBetweenFirstDueAndNow) * repeatInterval, periodType), "days") > 0,
  
	if(or(empty(dueDate), empty(intervalType)),
		parseDate(""),
		if(isDueDateInFutureOrToday,
			dueDate,
			if (
				isCalculatedNextDateInPast,
				dateAdd(dueDate, (ceil(intervalsBetweenFirstDueAndNow) + 1) * repeatInterval, periodType),
				dateAdd(dueDate, ceil(intervalsBetweenFirstDueAndNow) * repeatInterval, periodType)
			)
		)
	)
)

Learnings

  • lets(name, val, name, val, ..., expression) allows you to declare variables to split your function down into smaller chunks. It then returns the expression evaluated passed in as the last argument.
    • let is similar but only works for a single variable.
  • ceil is required for Alternate Days as the intervalsBetweenFirstDueAndNow can be 0.5 (adding 0.5 to a day remains the same day).
 
I’m not sure if I will actually use this feature but it was a fun challenge.