Formulas

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

Hello Airtable community! I’ve got a new IF formula that’s giving me trouble - I’m trying to format titles from our database for a distributor that needs the components in a certain order, which changes based on other parameters. To begin with, I was able to set up a statement that only targeted records where the format is listed as “Issue” and then get the root of the new title created with existing fields like so:UPPER(IF({Format Type}="Issue",CONCATENATE({Title w/o Article}," ","#"," ",{CVR A, B, C}," ",{Cover Artists}," ","VAR"),""))That gives me, as an example, MADE UP TITLE #1 CVR A PABLO PICASSO VAR and also returns an empty field for anything that’s not an ‘Issue’. The problem with this formula is that if it’s CVR A, then there shouldn’t be a ‘VAR’ at the end. So I tried to modify that with an IF(AND( statement, and every version I’ve tried breaks down, like this:UPPER(IF(AND({Format Type}="Issue",{CVR A, B, C}!="CVR A", CONCATENATE({Title w/o Article}," ","#",{Issue #}," ",{CV

I run my market garden business on airtable. The market price for my vegetables change monthly or seasonly. At the moment I use a formula field to calculate the return on my production each month . I have a table for each vegetable price but when my prices change how do I stop the new price altering all my previous months. Do I need to use a script?

I was looking for something like this, but couldn’t find an airtable formula so I thought I would share mine. This only covers the last 3 Japanese era’s.I would be great if something like this could be added to the Set_Locales function for Japanese, but I didn’t see any others that changed the year numbers so it might not be possible.Administratively I have deal with a lot of dates that need to passed to the local government and for birthdays we need to use the era years instead of the Gregorian years.And if anyone has a better one please share.IF(Birthday,SUBSTITUTE(SUBSTITUTE(IF(    IS_BEFORE({Birthday},DATETIME_PARSE("Jan 8, 1989"))=1,"昭和"&(DATETIME_FORMAT({Birthday},"YYYY")-1925)&"年",        IF(        IS_BEFORE({Birthday},DATETIME_PARSE("May 1, 2019")),"平成"&(DATETIME_FORMAT({Birthday},"YYYY")-1988)&"年","令和"&(DATETIME_FORMAT({Birthday},"YYYY")-2018)&"年"))&DATETIME_FORMAT(Birthday,"M月D日"),"令和1年","令和元年"),"平成1年","平成元年"),BLANK())

Hey all,so weirdly enough, when I setup the formula for a total time calculated it shows this: 120 minutes, correct?However the resultant display shows this:Just curious, why is it showing up as 0:02 instead of 120, even though in the sample it shows it’ll be 2?

My primary field is an employee’s full name in this format: Last, First. The catch is that some employees have a middle initial or a middle name, so it looks like this: Last, First Middle.I want to create two separate columns that separately extract the last name and the first name. I want the separate first name column to include the middle name/initial (if they have one).I’ve been able to create the last name field with this formula: LEFT({Person Name}, (FIND(",", {Person Name}, 1)-1))However, when I use that same formula for the first name field except with the RIGHT function it doesn’t pull out the first name correctly for everyone. RIGHT({Person Name}, (FIND(",", {Person Name}, 1)-1))Any ideas?Solved it: SUBSTITUTE( {Person Name}, LEFT( {Person Name}, FIND( ",", {Person Name} ) + 1 ), "" )

Is there a way to extract a users email address from their account info?Context...I have a form built in "interfaces" and have toggled on "see who submitted response".Ideally I'm trying to extract the domains for the users as a method to sort with different views.Ex. forms submitted by "domainA.com"forms submitted by "domainB.com"

Okay guys, ive seen a similar post about this but didn’t answer what i was looking for. I am wanting to take a url (google.com) and add it to a “open URL” button then another record have another url (youtube.com) and add it to another “Open URL” button. What formula could i use to target the url inside of the button field? SCREENSHOT: https://snipboard.io/A3ucNd.jpg

Hi everyone,I always found Airtable formulas a bit tricky, so I built a Chrome extension called Airtable AI Formula Builder. It lets you describe what you want in plain English (e.g. “combine first name and last name”), and it instantly generates the correct Airtable formula.👉 Extension link: Ai formula builder It’s a freemium tool: you can try it once for free to see how it works, and then unlock unlimited usage with a small upgrade.This is the very first version (MVP), and I’d love your feedback: What formula types do you use the most? What extra features would make this more useful for you?Thanks a lot, every bit of feedback helps me improve it 🙏

We have an intake form to generate URLs with UTMs parameters. I have created a new form with additional functionality. During testing, I need both the old and new forms to work.My current Formula field concatenates the URL (Root Link) with the UTM parameters and values. There is also a check to determine if the URL provided in the Root Link field contains a "?". If it does, then the concatenation output is handled differently:IF(FIND("?", {Root Link})=0,{Root Link}&{Source Parameter}&{Source Input}&{Medium Parameter}&{Medium Input}&{Campaign Parameter}&{Campaign Input (Child Campaign Name_Linked)}&{Content Parameter}&{Content Input},{Root Link}&"&utm_source="&{Source Input}&{Medium Parameter}&{Medium Input}&{Campaign Parameter}&{Campaign Input (Child Campaign Name_Linked)}&{Content Parameter}&{Content Input})My new form has the option to create some additional link types based on content. If you choose that option, the