Help

Recurring Events in Calendar with separate Date and Time fields set to specific timezone

543 1
cancel
Showing results for 
Search instead for 
Did you mean: 
Jeff_A
5 - Automation Enthusiast
5 - Automation Enthusiast

Posting this in case this can help anyone solve an issue or in case there is anyone who can think of how to do it better. I was asked to create a corporate event calendar that housed all 5 corporate event calendars in a single base and included 3 specific items:

  1. Enforce all event times have a time zone of PST
  2. Allow easy All Day or Continuous All Day Meetings
  3. Create recurring events that can be created daily, weekly, bi-weekly, monthly, quarterly, semi-annually or annually. Additionally there was a caveat that one calendar type needed to include weekends while the others did not

To solve for this I created a single table called Internal Calendars and included the following fields:

  • Event Occurrence - Single Select that allows the user to pick the type of scheduling for their event(s): All Day, Continuous All Day, Conference Event, Recurring Event and Holiday
  • Start Day - Date field used to select the date an event will start.
  • Start Time - Single Select that has times in 30-minute intervals. 
  • End Day - Date field for event End. 
  • End Time - Single Select field with the same settings as Start Time. 
  • Frequency - Single Select for Recurring Event occurrences with the available options
  • Run Until - Date field used to calculate when to stop calculating recurring events
  • Other fields for the event details

Additionally I used record templates so I could create custom forms for each calendar type to pre-fill some of the other fields not related to the solution but so I could also filter the times they can pick in form as well as using conditional visibility so fields like Start/End Time, Frequency and Run Until only show for relative occurrence selections. For example if you select All Day you only see the Start Day field, if you select Continuous All Day you only see Start Day and End Day but when Recurring Event is select it shows all fields.

Once I set all of those fields I created an automation on record creation it would set the time field options to 12:00 AM and 11:59 PM if either All Day types were chosen or handled the recurring event functionality outlined further down.

I struggled with how to create a formula that combined the two dates while enforcing the same time zone for everyone and auto-adjusting for Daylight Savings time, but I believe I got it solved (will be able to test it in a few weeks when DST ends and make any adjustments) and wanted to share. The formula below is for the event start and is the same for event end just with the End fields instead:

IF(AND({Start Day} >= DATETIME_PARSE(YEAR({Start Day}) & "-03-" & (8 + (7 - WEEKDAY(DATETIME_PARSE(YEAR({Start Day}) & "-03-08"))))),{Start Day} < DATETIME_PARSE(YEAR({Start Day}) & "-11-" & (1 + (7 - WEEKDAY(DATETIME_PARSE(YEAR({Start Day}) & "-11-01")))))),DATEADD({🕒 Start}, -1, 'hour','MM/DD/YYYY h:mm A'),DATEADD({🕒 Start}, 0, 'hour','MM/DD/YYYY h:mm A'))

Finally with my calculated date fields being done and visible on the calendar correctly I was then able to create the automation action for when Recurring Event is selected. For this action I used the Run a Script option and designed the script to take do a couple of things:

  • Looks at the selected Frequency option and Run Until date field which it uses to calculate the number of events needed. 
  • Additionally it is set so if the Start Day is on a Weekend it includes all days of the week into the calculation but if Start Day is a work day it only calculates using weekdays. Note: I am working to add additional holiday skipping into my calculations but will be much later on.

Finally once the above calculations and conditions are completed it creates the recurring event records carrying over the static fields we selected as well as setting the Event Occurrence field to Recurrence (so calendar Admins can easily determine the original event vs the created events).

// Get the triggering record's details
let table = base.getTable("Internal Calendars");
let records = await table.selectRecordsAsync();
let record = records.getRecord(input.config().recordId);

let runUntil = new Date(record.getCellValue("Run Until"));
let startDay = new Date(record.getCellValue("Start Day"));
let endDay = new Date(record.getCellValue("End Day"));
let frequency = record.getCellValue("Frequency").name;

// Determine if Start Day is a weekend
let isWeekend = (startDay.getDay() === 0 || startDay.getDay() === 6);

while (startDay <= runUntil) {
    // Only create records after the initial date
    if (startDay > new Date(record.getCellValue("Start Day"))) {
        let newRecord = {
            "Title": record.getCellValue("Title"),
            "Purpose": record.getCellValue("Purpose"),
            "Start Day": startDay.toISOString().split('T')[0],
            "Start Time": record.getCellValue("Start Time"),
            "End Day": endDay.toISOString().split('T')[0],
            "End Time": record.getCellValue("End Time"),
            "Event Occurrence": { id: "selG7czNrd7PEfF4n" },
            "Frequency": record.getCellValue("Frequency")
    
      };
        
        // Create the new record
        await table.createRecordAsync(newRecord);
    }

     // Increment startDay and endDay based on frequency
    switch (frequency) {
        case "Daily":
            if (isWeekend) {
                // Include weekends
                startDay.setDate(startDay.getDate() + 1);
                endDay.setDate(endDay.getDate() + 1);
            } else {
                // Skip weekends
                do {
                    startDay.setDate(startDay.getDate() + 1);
                    endDay.setDate(endDay.getDate() + 1);
                } while (startDay.getDay() === 0 || startDay.getDay() === 6);
            }
            break;
        case "Weekly":
            if (isWeekend) {
                // Include weekends
                startDay.setDate(startDay.getDate() + 7);
                endDay.setDate(endDay.getDate() + 7);
            } else {
                // Skip weekends
                do {
                    startDay.setDate(startDay.getDate() + 7);
                    endDay.setDate(endDay.getDate() + 7);
                } while (startDay.getDay() === 0 || startDay.getDay() === 6);
            }
            break;
        case "Bi-Weekly":
            if (isWeekend) {
                // Include weekends
                startDay.setDate(startDay.getDate() + 14);
                endDay.setDate(endDay.getDate() + 14);
            } else {
                // Skip weekends
                do {
                    startDay.setDate(startDay.getDate() + 14);
                    endDay.setDate(endDay.getDate() + 14);
                } while (startDay.getDay() === 0 || startDay.getDay() === 6);
            }
            break;
        case "Monthly":
            if (isWeekend) {
                // Include weekends
                startDay.setMonth(startDay.getMonth() + 1);
				endDay.setMonth(endDay.getMonth() + 1);
            } else {
                // Skip weekends
                do {
                    startDay.setMonth(startDay.getMonth() + 1);
					endDay.setMonth(endDay.getMonth() + 1);
                } while (startDay.getDay() === 0 || startDay.getDay() === 6);
            }
            break;
        case "Quarterly":
            if (isWeekend) {
                // Include weekends
                startDay.setMonth(startDay.getMonth() + 3);
				endDay.setMonth(endDay.getMonth() + 3);
            } else {
                // Skip weekends
                do {
                    startDay.setMonth(startDay.getMonth() + 3);
					endDay.setMonth(endDay.getMonth() + 3);
                } while (startDay.getDay() === 0 || startDay.getDay() === 6);
            }
            break;
        case "Semi-Annually":
            if (isWeekend) {
                // Include weekends
				startDay.setMonth(startDay.getMonth() + 6);
				endDay.setMonth(endDay.getMonth() + 6);
            } else {
                // Skip weekends
                do {
					startDay.setMonth(startDay.getMonth() + 6);
					endDay.setMonth(endDay.getMonth() + 6);
                } while (startDay.getDay() === 0 || startDay.getDay() === 6);
            }
            break;
        case "Annually":
            if (isWeekend) {
                // Include weekends
				startDay.setFullYear(startDay.getFullYear() + 1);
				endDay.setFullYear(endDay.getFullYear() + 1);
            } else {
                // Skip weekends
                do {
					startDay.setFullYear(startDay.getFullYear() + 1);
					endDay.setFullYear(endDay.getFullYear() + 1);
                } while (startDay.getDay() === 0 || startDay.getDay() === 6);
            }
            break;
    }
}​

This was the first script automation I have ever written and also the first time I tried using separate fields for my date and time selections. I hope this is able to help anyone trying to figure out something similar for recurring calendar events but also feel free to let me know if I made any mistakes in my formulas/script or if it can be simplified further.

 

 

1 Reply 1
Hamda
4 - Data Explorer
4 - Data Explorer

Thank you so much Jeff for this insightful post.
I have been looking at a formula for recurring events with different frequencies but am struggling to have an end date for the recurrence. I like your idea of "Run Until" but I am still unsure of how to apply it as an Airtable formula or automation. 

Do I have to build the table from scratch with coding script or do you know if there is a way to do it with existing Airtable formulas? 

Appreciate your support.