Skip to main content

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:

  1. Gets the End date of the project.
  2. Subtracts three days.
  3. Uses the range of dates between three days before the end of the project and the end of the project (let's call it the "last three days of the project")
  4. Checks all "Compliance Challenge Dates" tab entries and sees if there's any overlap between the date range of any of the entries and the last three days of the project.
  5. If yes, return the name/primary key of the overlapping challenge entry in the "Compliance Challenge Dates" tab.

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!

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)

  1. Create a Scripting Block: Add a Scripting Block to your Airtable base.
  2. 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)

  1. Set up an Automation: Create an automation triggered when a record in the "Projects" table is updated (or on a schedule).
  2. 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)
  3. 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.
  4. 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. 👍

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 

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)

  1. Create a Scripting Block: Add a Scripting Block to your Airtable base.
  2. 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)

  1. Set up an Automation: Create an automation triggered when a record in the "Projects" table is updated (or on a schedule).
  2. 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)
  3. 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.
  4. 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. 👍


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?

Reply


Terms & Conditions