Formulas

So I’m looking to employ a formula to provide a word count for my SEO keyphrases, which range from 2 to 8 words. Below is the formula that I thought would work, but I’ve had no such luck…does anyone have any thoughts as to what’s going on here? Thank you in advance! IF(({Keyphrase}=BLANK()),0,(LEN({Keyphrase}) - LEN(SUBSTITUTE({Keyphrase},’ ', ‘ ’))+1))

What is the calculation behind the Timeline Utilization summary?  I am trying to create a formula column that displays a resources utilization in the Resources table, to be used as a metric on a landing page.I tried ((({Task Count} * 8) * {% Commitment} ) / (40 * {% Availability)) , but it does not match what AirTable is showing on the Timeline view.  The task count is the number of tasks for the current day. Is there a way to simply access the calculated value? Thank you,

Am I overcomplicating this?I have an automation to create a time stamp in a Date Completed field when a Status single-select field is changed to Done. The Date Completed field is populated with the full date and time, but I need to utilize this information in a Reporting interface for my management so I’d like to be able to have them select the month from a Dropdown Filter. To that end, I’ve created Month Completed field that uses a simple formula to just show the 3-letter code for the month, based on the Date Completed field.The issue I’m having now is that I’m not able to use that 3-letter code (because it’s a formula field?) as a filter on the Dashboard.I could create another automation that’ll convert the formula field into plain text or even a single-select field, but I feel like I’ve now created a whole bunch of steps to do something that might be accomplished in a simpler method. Note that the Month Completed info isn’t being used anywhere else. The Date Completed will likely al

NEVERMIND - I filtered out the empty ratings. Thanks!

I have an Airtable that is populated from Jotform submissions. The Jotform has a configurable list that for someone to fill in their schedule for the week. The configurable list can’t be split in Jotform so I’m trying to split information in Airtable. I want all of the days of the week listed in the configurable list cell to be extracted in a Days Open column but can only figure out how to extract the first instance using regexextract.  For example the configurable list will come in as:Day Open: Monday, Opening Date: 2025-06-16, Closing Date: 2025-10-27, Start Time: 09:00 AM, End Time: 02:00 PMDay Open: Saturday, Opening Date: 2025-08-09, Closing Date: 2025-08-30, Start Time: 10:00 AM, End Time: 04:00 PM and I want Monday, Saturday to show up in the days they are open. 

Hi.We are using Airtable to display data about our system landscape. We want to share this data with our organization, and have therefore crated links and added these to our internal CMS system.If the link is shared to somebody outside our organization, the information should not be available. We tired to handle this with the “Access restricted to our email domain”, but found out that it requires that the use is an airtable user. If we don’t make any restrictions that link can be accessed by anyone how has it.In the perfect solution, we would like that only our Active directory users have access to the shared link. Is there any way to solve this?Thanks in advance.  

Hi there, I’m trying to put together an expenses claim form for my team including mileage claims. We previously uses Google API Distance Matrix on JotForm to calculate the distance an employee travels between two locations. I wondered whether there was a way I could add something like this to an Airtable form? The distance that came out between the two locations then calculated how much that employee is owed for the petrol used. Any advice would be appreciated. 

Trying to create a condition via formula which will mean that a client won't be able to be quoted via airtable extension with the following:Formula:IF(Type="Client", IF({On Hold}="On Hold">"Good", "✅ Good to Quote","❌On Hold - Do not Quote"))We are hoping to then enable the On hold note to propagate through to an extension we use to send quotes onto clients There is a field with single text which we use to fill out the client and then a options single select for on hold/good

Hello Airtable Community,I’m receiving an array from a Fillout form via webhook into Make (see attached image)I would like to use this array in a Search Records module for Airtable to find all records where the primary field matches any of the values in the array.What’s the correct formula syntax in Make’s Airtable module to achieve this?Thanks in advance   

We’ve created an airtable base for submissions from teams, and then those need to be reviewed by assigned reviewers.  In the form, the review must select their own name. In another field, they need to select the project they are reviewing. I would like to filter the projects that are options to be just the ones the review is assigned to.  Is there a formula to say the “Assigned Review” field matches the name selected in the review name field?  As a work around, I was going to use filter by View -- but I’m also not seeing that option anymore. Was that moved? 

I am trying to set up an email automation that sends an email the next day at 9am after a status change. I have created a formula field: IF(  {Status Changed},  SET_TIMEZONE(    DATEADD(      DATETIME_PARSE(DATETIME_FORMAT({Status Changed}, 'YYYY-MM-DD') & "T09:00:00"),      1,      'days'    ),    'Europe/London'  ))The automation is triggered using a status condition and when the formula field is ‘today’. and This creates the correct time and date in the formula field, but it is not triggering the automation.  

I am looking to set up an automation based on when my date and time field are NOW Does not look like the automations date filter allows for NOW or takes formula code on it so how could I use a calculation field to look for any date that is now then tick a box or mark another field as True so that I can then base my automation filter off a column marked True? Not sure of the code that when time is NOW mark this fields as True etc…? Tried this but now working IF({Due Back Time Reminder} = NOW(), “True”, “False”)

I have this formula that is working: IF({Start date},DATETIME_FORMAT(DATEADD({Start date},14, 'days'),'MM/DD/YYYY'),"") I want to incorporate these conditions to it:IF(Category = "Playbooks",14, 'days')IF(Category = "How-to-Guides",3,’days')  

I have a very simple Cash Dispensement program. For every Cash or Credit Card purchase enter the Date, Vendor, Receipt Amount and distribute the expense to “thing #1”, “thing #2” and “thing #3”. A separate “Balance” column adds #1, #2 and #3 and if it’s the same as “Receipt Amount” there is a check box with a check.  Items without a check indicate a problem. I go back and correct the dollar amounts as needed.I have created an automation that updates when one of the four fields are updated. Here is the formula for the “Balance” field:{Receipt Amount} = {#1 Amount} + {#2 Amount} + {#3 Amount}. If TRUE I get a check mark, if False I get a blank in the check box.This worked perfectly when the field was first created.  As I correct the numbers the the Balance field doesn’t update.  When I erase part of the formula sometimes it corrects, sometimes NOT!My personal feeling is the action is being timed out by an Internet connection or high traffic and it’s not completing the update. I live in a

I am hoping to use a formula to capture clock-in time for employees. Intended functionality is that if the box is checked, log the shift start time as actual start time. If the box is unchecked, override the shift start time with actual time captured in another column. Search results said the formula to use is IF({Checked in on time?}, {Shift Start}, {Check in time if late}). The formula captures a time, but it does not match either date field.Any thoughts on why the formula is miscalculating?

I think I have painted myself into a corner here. Appreciate any advice.We’ve migrated from another system that generated autonumbers for our Leads.I’ve imported all the  leads them into my Leads table. It is critical I keep the old data - it can’t be changed as all our  work for these clients has been against that specific ID number.You can see from my Screenshot that column A has the ID number (now a text field), column B has the customer name, and column C is a joining of the two (but just a text field for now, no formula/concatenation used).I have tossed in a 4th autonumber column to try and create a formula that carries on the numbering from column A (Lead Number). But now I’m stumped. Having an ‘m’ at the beginning of the Lead number does not help - otherwise I presume a simple sum formula to add the lead number to the autonumber would suffice. It’s like I need historical numbers to be text, but all future records to be a formula.Any ideas? Thanks in advance. 

Can you tell me formula to make bullet list from comma separated text?eg text: "text1, text2".expected output:• text1• text2

I have a `rollup` column, `A` that returns a `date`.  for example, 2024-07-24.I have a `lookup` column, `B`, that returns an array of dates.  For example, 2018-07-31, 2021-07-30, 2024-10-08Is there a way to  determines if any of the dates in column `B` are within 30 days (before or after) of the date in column `A`?  I need the formula to return a boolean.

Problem: I have a membership table with members listed by name. I want to keep people from typing their information into the membership form on our website that feeds into the table twice. There is no way that I can see for me to set a field to not allow duplicate entries. I would like to set the phone number as the unique identifier for each member. I see no way to select or set this field to not allow duplicates. I know I could that in Access by setting it as primary, but I can’t do that in Airtable.

I’m trying to create a system that generates simple codes from employees’ SSNs for internal use. The SSN is stored as numbers, e.g., 123-45-6789.I want to create a new formula field that converts each number into a letter using a reverse alphabet mapping:Z = 1, Y = 2, X = 3 … A = 26For example: 1 → Z 2 → Y 0 → J (or another letter of choice) Is there a formula in Airtable that can do this conversion directly?For reference, numberstoletters.com does similar conversions, but I want to implement it directly in Airtable.

Formula field {Allocation Form Recipients} = IF({Send Allocation Form},{Conditional Recipients Rollup (from Payment Schedule)}) where {Conditional Recipients ...} is a rollup field and {Send Allocation Form} is a checkbox field. Formula field {Approval Email Subject Tag} = IF( AND({Term Deal},{Allocation Form Recipients}), 'Recoupment Allocation', 'Payment' ) where {Term Deal} field is a rollup and {Allocation Form Recipients} is a formula field which I explained in #1. When the {Term Deal} rollup is empty and the {Allocation Form Recipients} formula field is also empty, the {Approval Email Subject Tag} should show 'Payment' but it shows 'Recoupment Allocation'. I tried to troubleshoot this by creating 2 separate formula fields stated below: a. _check = IF({Term Deal},1,0) b. _check copy = IF({Allocation Form Recipients},1,0) Surprisingly, these 2 formula fields work as expected. Meaning where Term Deal is blank _check is 0. When {Allocation Form Recipients} is blank, _check copy is 0.

Hi AirTable Community, I’ve been struggling with WORKDAY_DIFF formula. My team tracks reports using Airtable. We set an attendance date (AD) and a report type that are set/selected. Based on the report type, I have a formula that will add a certain number of days to the AD to calculate the draft due date (DDD). There is also an option to assign a due date should we need to override the calculated date.  Formula as follows:IF({Assigned Draft Due Date}, {Assigned Draft Due Date}, SWITCH({Report Type / Tasks}, 'ACM - ASMP', WORKDAY({Attendance Date}, 5), 'ACM - Audit/Survey Report', WORKDAY({Attendance Date}, 8),    etc., etc. for every report type.  My operations team also want a ‘Days Overdue Field’ so that they can quickly see if a report is overdue. The WORKDAY_DIFF field I know includes both the start date and the end date in its calculations, I can negate this by minusing 1 day in the formula. But it seems no matter what settings I use - I tried setting ‘Use same timezone for all co

I’m running a program for rebates related to project work on homes. The issue I’m running into is how to track the rebates over multiple projects. It’s not as simple as just keeping a running total unfortunately, there are several factors that determine what the available rebate amount is and how it is applied.  I have it all figured out for single projects with everything calculating correctly, so I know exactly what the rebate would be if the full rebate limit is available. However, when I have more than 1 project and a rebate is being spread out between multiple projects is where I’m having issues. I’ll try my best to describe everything below. Unfortunately, due to the sheer complexity of the program and base, plus lots of personal data, I can’t create a copy of the base to share for someone to look into directly, but let me know if there is anything I can provide that would help. Here are the details:Rebates are by address There are individual rebate limits, plus a total rebate li

Is there a way for Record IDs to be visible after a user completes a form? Maybe in the message that appears after hitting the submit button to act as a “confirmation code” that can be copied and pasted elsewhere?Current state: Team member completes airtable form. After completing the form, team member is redirected to message thanking them for the submission. Goal:After completing the form, a record ID is generated from the completed response and the user is able to copy and paste that record ID number to be shared/associated.  To my knowledge, the record ID is generated after the form is completed and is visible in the Data is there anyway to make this visible sooner and so the user doesn’t need to view the data to pull record ID info? 

I’m really struggling with an Australian Mobile Number Formula to automatically reformat with the below behaviour:PLEASE HELP!