Problem: Airtable's built-in formulas on any plan cannot directly determine specific overlaps between date ranges across different tables.
Workarounds (Free Plan):
- Warning Field: In the "Projects" table, create a formula field to flag if the last 3 days of a project fall within ANY of the "Compliance Challenge Dates". This won't give the exact overlap but signals potential review delays.
- Filtering/Sorting: Use Airtable's filtering and sorting to isolate flagged projects and prioritize those with the closest end dates.
Solutions Requiring Paid Plans:
- Scripting Block (Pro Plan): Write JavaScript code to directly compare date ranges and pinpoint exact overlaps.
- Automation + External Service (Pro Plan): Trigger automation to send project data to a service like Zapier or Integromat for date comparisons, then update Airtable with results.
Since the best way to achieve your exact goal requires paid Airtable features (Scripting or Automation + external tools), I'll outline the conceptual steps for both approaches.
Assumptions
- You have a "Projects" table with "Start Date" and "End Date" columns.
- You have a "Compliance Challenge Dates" table with "Name", "Start Date", and "End Date" columns.
Approach 1: Scripting Block (Pro Plan or Higher)
- Create a Scripting Block: Add a Scripting Block to your Airtable base.
- Write JavaScript Code: Here's the core logic:
let projectsTable = base.getTable('Projects');
let challengesTable = base.getTable('Compliance Challenge Dates');
let outputField = projectsTable.getField('Compliance Challenge'); // Adjust the field name
// Get all challenge dates
let challengeRecords = await challengesTable.selectRecordsAsync();
// Iterate through project records
let projectQuery = await projectsTable.selectRecordsAsync();
projectQuery.records.forEach(project => {
let endDate = project.getCellValue('End Date');
let lastThreeDaysStart = new Date(endDate.getTime() - (3 * 24 * 60 * 60 * 1000));
// Check overlaps with each challenge
let overlappingChallenge = challengeRecords.records.find(challenge =>
(challenge.getCellValue('Start Date') <= lastThreeDaysStart && challenge.getCellValue('End Date') >= lastThreeDaysStart)
|| (challenge.getCellValue('Start Date') <= endDate && challenge.getCellValue('End Date') >= endDate)
);
// Update output field
let outputValue = overlappingChallenge ? overlappingChallenge.getCellValue('Name') : '';
await projectsTable.updateRecordAsync(project, {
[outputField.name] : outputValue
});
});
Approach 2: Automation + External Service (Pro Plan or Higher)
- Set up an Automation: Create an automation triggered when a record in the "Projects" table is updated (or on a schedule).
- Connect to External Service: Use Zapier or Integromat as the middleman. Your automation should send the following to this service:
- Project end date
- All Compliance Challenge records (names, start dates, end dates)
- External Service Logic:
- Calculate the "last three days of the project" range.
- Compare this range against each Compliance Challenge date range.
- If an overlap is found, return the Challenge Name.
- Update Airtable: The external service sends the Challenge Name (if any) back to the Airtable automation, which updates the appropriate project record.
Important Notes:
- These are outlines; you'll need to adjust field names and set up the services appropriately.
- The Scripting Block is more self-contained, while the Automation approach adds some complexity but might be more adaptable if you use those services for other tasks.
Warning: Some of the content is created and generated using the help of AI tools. But that's not an issue if that helps to get the solution. 👍
