Mar 28, 2024 03:30 PM - edited Mar 28, 2024 03:32 PM
This might not be possible (especially since our nonprofit is on a free plan) but we have a number of projects that require review by our general counsel before they can finish. We have a tab with the start and end dates of each project. Our General Counsel just sent us several ranges of dates when she'll be especially busy and need more time than usual to provide her review.
The base started with just the "Projects" tab, with each project's start and end date. I've created a new tab for "Compliance Challenge Dates" with a name, a start date, and an end date. I would like to create a new field in the Projects tab that:
For example, our GC indicates she's busy 6/1-6/15 ("June board meeting prep") and 12/1-12/15 ("December board meeting prep"). I have a project that ends 6/16. In the row for this project on the Project tab, this field would return "June board meeting prep" because part of the three-day range before the end date of my project would overlap with the June board meeting prep date range.
Can this be done, and if so, can it be done on the free plan? Thank you!
Mar 29, 2024 05:52 AM
Problem: Airtable's built-in formulas on any plan cannot directly determine specific overlaps between date ranges across different tables.
Workarounds (Free Plan):
Solutions Requiring Paid Plans:
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
Approach 1: Scripting Block (Pro Plan or Higher)
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)
Important Notes:
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. 👍
Mar 29, 2024 06:08 AM
Hi @JPatGP,
That functionality could only be achieved through automations.
You could use Airtable’s automations to do that for you, but you would need to use several workarounds to get around Airtable’s date limitations. For example, you would need to create formula fields that convert your dates to a UNIX timestamp number, because Airtable’s automations only let you dynamically search on ranges of numbers, not dates.
However, there are several other limitations in Airtable’s automations as well. For more flexibility & customizability & ease of use, you could turn to Make’s automations and integrations for Airtable.
p.s. If your company has a budget for your project and you’d like to hire an expert Airtable consultant to help you with any of this, please feel free to contact me through my website: Airtable consulting — ScottWorld
Apr 01, 2024 06:35 AM
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.
This sounds like exactly what I'm asking for. Can you please provide the formula that would work for this?