A Way to Schedule Recurring Events - Well... sort of

Community Resources
1

Disclaimer, this method does not get ALL of your recurring events to show up on the calendar collectively but it offers a way to automate the placement of the next event on the calendar every week without additional work. Uses many columns.

The way I set it up:

  • State the full duration of the event with Full Start Date and Full End Date columns
  • Add boolean columns for days Sunday through Saturday
  • Add If-Then-Else columns to convert a selected day to a number value, 0 through 6
  • Add Start Time and End Time columns for each day
  • Add template columns for each day, Sunday through Saturday, to collect the information:{day}|{start}|{end}
  • Add If Then Else columns to omit residual template info from unselected days
  • Add an Array column to collect the info from the ITE columns
  • Add a Joined list column to extract the Array column details
  • Add a JavaScript column and input the code provided below, p1 is your Joined list, p2 is your Full End Date column, p3 is your Full Start Date column
  • Add a Split text column to comma separate the JS output and retrieve all upcoming dates and times
  • Add a Single Value column to pull your first future event
  • Add another Split text column to → separate your future event details into its start and end time components
  • Add a Single Value column to pull your next upcoming event’s Start Date and Time (first item)
  • Add a Single Value column to pull your next upcoming event’s End Date and Time (last item)

You’re done. Format your dates as desired.

JS code:

// p1 = weekday schedule (12-hour times), e.g. "0|9:30 AM|11:00 AM,2|2:00 PM|3:30 PM"
// p2 = end date, e.g. "2025-12-15"
// p3 = start date, e.g. "2025-09-16" (optional, defaults to today)

let weekdayInput = p1 ?? "0|9:30 AM|11:00 AM"; 
let endDate = new Date(p2 ?? "2100-01-01T23:59:59");
let startDate = new Date(p3 ?? new Date());
let now = new Date();

// Exclude past dates
if (startDate < now) startDate = now;

function parseTime12h(timeStr) {
  // e.g. "3:15 PM"
  let [time, modifier] = timeStr.trim().split(" ");
  let [hours, minutes] = time.split(":").map(x => parseInt(x, 10));
  if (isNaN(minutes)) minutes = 0;

  if (modifier.toUpperCase() === "PM" && hours < 12) {
    hours += 12;
  }
  if (modifier.toUpperCase() === "AM" && hours === 12) {
    hours = 0;
  }
  return [hours, minutes];
}

function getCourseSchedule(weekdayInput, startDate, endDate) {
  let schedule = [];
  let entries = weekdayInput.split(",").map(e => e.trim());

  entries.forEach(entry => {
    let [weekday, startTime, endTime] = entry.split("|");
    weekday = parseInt(weekday);
    let [sHour, sMinute] = parseTime12h(startTime);
    let [eHour, eMinute] = parseTime12h(endTime);

    // Find first occurrence on or after startDate
    let d = new Date(startDate);
    let offset = (weekday - d.getDay() + 7) % 7;
    d.setDate(d.getDate() + offset);
    d.setHours(sHour, sMinute, 0, 0);

    // Loop until end date
    while (d <= endDate) {
      let start = new Date(d);
      let end = new Date(d);
      end.setHours(eHour, eMinute, 0, 0);

      let format = (dt) => {
        let y = dt.getFullYear();
        let m = String(dt.getMonth() + 1).padStart(2, "0");
        let day = String(dt.getDate()).padStart(2, "0");
        let h = String(dt.getHours()).padStart(2, "0");
        let min = String(dt.getMinutes()).padStart(2, "0");
        return `${y}-${m}-${day}T${h}:${min}`;
      };

      schedule.push(`${format(start)} → ${format(end)}`);

      d.setDate(d.getDate() + 7); // next week
      d.setHours(sHour, sMinute, 0, 0); // reset time
    }
  });

  // Sort all dates
  schedule.sort();
  return schedule.join(",");
}

return getCourseSchedule(weekdayInput, startDate, endDate);

Images:

Template column

image
image347×445 15.2 KB

Days array

image

3 Likes
2

Alternative JS code for if you want to move on to the next scheduled item as soon as one expires during the day, instead of waiting until the end of the day for the schedule to update.

// p1 = weekday schedule (12-hour times), e.g. "0|9:30 AM|11:00 AM,2|2:00 PM|3:30 PM"
// p2 = end date, e.g. "2025-12-15"
// p3 = start date, e.g. "2025-09-16" (optional, defaults to today)

let weekdayInput = p1 ?? "0|9:30 AM|11:00 AM"; 
let endDate = new Date(p2 ?? "2100-01-01T23:59:59");
let startDate = new Date(p3 ?? new Date());
let now = new Date();

// Exclude past dates
if (startDate < now) startDate = now;

function parseTime12h(timeStr) {
  // e.g. "3:15 PM"
  let [time, modifier] = timeStr.trim().split(" ");
  let [hours, minutes] = time.split(":").map(x => parseInt(x, 10));
  if (isNaN(minutes)) minutes = 0;

  if (modifier.toUpperCase() === "PM" && hours < 12) {
    hours += 12;
  }
  if (modifier.toUpperCase() === "AM" && hours === 12) {
    hours = 0;
  }
  return [hours, minutes];
}

function getCourseSchedule(weekdayInput, startDate, endDate) {
  let schedule = [];
  let entries = weekdayInput.split(",").map(e => e.trim());

  entries.forEach(entry => {
    let [weekday, startTime, endTime] = entry.split("|");
    weekday = parseInt(weekday);
    let [sHour, sMinute] = parseTime12h(startTime);
    let [eHour, eMinute] = parseTime12h(endTime);

    // Find first occurrence on or after startDate
    let d = new Date(startDate);
    let offset = (weekday - d.getDay() + 7) % 7;
    d.setDate(d.getDate() + offset);
    d.setHours(sHour, sMinute, 0, 0);

    // Loop until end date
    while (d <= endDate) {
      let start = new Date(d);
      let end = new Date(d);
      end.setHours(eHour, eMinute, 0, 0);

      // Skip if this class has already ended (today & past time)
      if (end > now) {
        let format = (dt) => {
          let y = dt.getFullYear();
          let m = String(dt.getMonth() + 1).padStart(2, "0");
          let day = String(dt.getDate()).padStart(2, "0");
          let h = String(dt.getHours()).padStart(2, "0");
          let min = String(dt.getMinutes()).padStart(2, "0");
          return `${y}-${m}-${day}T${h}:${min}`;
        };
        schedule.push(`${format(start)} → ${format(end)}`);
      }

      d.setDate(d.getDate() + 7); // move to next week
      d.setHours(sHour, sMinute, 0, 0); // reset time
    }
  });

  // Sort all dates
  schedule.sort();
  return schedule.join(",");
}

return getCourseSchedule(weekdayInput, startDate, endDate);
1 Like