I’ve worked on this problem a few times but not in the context of Airtable, so my contribution could be worth little to zero value. With such a low expectation, let me press on with a few observations.
#1 When, Not Who
This problem is not about who; it is only about when, specifically and hopefully about lots of when’s. Don’t get distracted by existing schedule details for each individual. Do not get mired in the minutia of every person’s details. Instead, focus on occupied times regardless of who may overlap other people’s occupied time slots.
#2 Clearly State the Requirement
Build an algorithm, that given every member’s meeting schedule, will merge all intersecting meeting times together, making it easier to see when everyone is free ( and by free we mean not in a meeting).
#3 The Inputs
The inputs required for this solution are incredibly simple - an array of objects containing all original meeting times. This could easily be a roll-up table for any given day for all meetings regardless of the person scheduled, right?
[
{startTime: 0, endTime: 1},
{startTime: 3, endTime: 5},
{startTime: 4, endTime: 8},
{startTime: 10, endTime: 12},
{startTime: 9, endTime: 10},
]
#4 Overlapping “Merged” Meetings
Two meetings A and B are considered intersecting if B’s starting time is earlier or equal to A’s ending time. That sounds simple enough but be cautious - codifying this is a bit more difficult in practice. It requires precision handling of dates and times computations which Airtable is totally suited to handle.
#5 The Outputs
An array of objects containing all meeting times merged.
[
{startTime: 0, endTime: 1},
{startTime: 3, endTime: 8},
{startTime: 9, endTime: 12},
]
#6 Rendering the Opposite
The final step is to render the gaps; a list of every hour that does not fall into the occupied time slots. This could be done as as a view designed to look like a calendar of 24 hourly time slots with a filter that removes any slot that falls within the merged times.
Go build that in Airtable. :winking_face:
