Formulas

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. 

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!

Hello,I'd like my formula to spit out a paragraph of text. Some of the text within the formula, I'd like to include hyperlinks. Is this possible?Here's an example:If field A = option 1, spit out this text:Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua.If field A = option 2, spit out this text:Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua.Again, my main question is: is it possible to hyperlink a piece of text in a formula?Thank you!

Hey guys! We have a free account and I want share products for my clients to approve - is there a way that they can click select when i send them this view? Thanks in advance 

I’m using this formula: WEEKNUM({Registered})Where “Registered” is the date a user registered on my website. This assigns each record a week number according to the Jan-Dec calendar.I want August 17-23, 2025 to be my Week 1 and January 25-31, 2026 to be my last week (Week 24). How can I change this?

Hi Airtable Community!I’m trying to set up a formula that will convert a printed price to its digital price equivalent, but the rules for those calculations are wonky. I was able to successfully tell the formula that for items under 10.99, it should subtract 1 or 2 from the printed price (depending on the original format), but my attempts to code a SWITCH clause for any titles over $10.99 (and thus subject to a specific price grid) are just returning empty values.I’m trying to argue that if a book is hardcover and greater than 10.99, then the digital price from a grid should be swapped in based on the print price. Same for if the book is a paperback, but the grid values are different, so it requires its own clauses.Here’s what I’ve got so far: IF({US Price (Printed)}="",CONCATENATE(""), IF(AND({US Price (Printed)}!="",{US Price (Printed)}<'10.98',{Format Type}="Hardcover"),{US Price (Printed)}-'2.00', IF(AND({US Price (Printed)}!="",{US Price (Printed)}>'10.98',{Format Type}="Har

 Working on row status for projects based on conditional fields pulled from external sources but am bad at formulas. I think I am close. Any help on this? . Issues with greater than zero and start dates in the pastIF( {Effort Current Total Estimate}=BLANK(), 'Scoping'IF( AND( {Effort Current Total Estimate} > '0', {Is Approved} = "checked" ), "Dev Pending"IF( AND( NOW()>{Target Start Date}, {Has Human Interface Impact}= "checked" ), "In Design"IF( {Substate}='Nominate','Intake',IF( {Substate}='Investigate','Engage',IF( {Substate}='Fix','In Dev',IF( {State}='Integrate','Feature Complete',IF( {Substate}='Review','Feature Complete',IF( {State}='Verify','With QA',IF( {State}='Closed','Closed',IF( {Substate}='Review','Feature Complete',))))))))))) 

Hi Everyone Does any one know of a formula that could deduce the Week Number in the Year from a given date field? Any help would be greatly appreciated. Stephen