Formulas

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

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

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:

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)}=

 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}," "

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,

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(

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 P

In airtable form the column should be visible when 2 column values together should be 100%. 1st Question:For example, as shown in the screenshot below, in the Airtable Form, when a user updates Tag 1 and Tag 2 so that their total equals 100%, Tag 3 and Tag 4 should no longer be visible in the form. If the tags do not total 100%, all three remaining tags (Tag 2, Tag 3, and Tag 4) should remain visible. 2nd Question:Is there an option or way in Airtable forms to prevent users from submitting the form if the total Tag does not equal 100%? 

@ScottWorld , I went through a few options to look at ways to just highlight the duplicate entries as they get entered into a database. I noted u came up with a simple one but there were no steps written down. We have a unique patient ID which is in the primary column , we dont want to duplicate a patient file. Every time we enter we wish to have a look and see if there is a duplicate entry and delete if its highlighted. makes sense?

Just wanted to pitch this, and see if you are seeing this as much of a need as I see it. I am a heavy Notion user, which I quite a lot but I overall preffer Airtable except for one thing. NOTION Formulas as far superior. To give an example, you can use dot notation in Notion formulas  sum((Pagos.filter(and(current.Tipo=="Cuota",formatDate(current.Date,"MMMM")==(formatDate(today(),"MMMM")))).map(current.Monto)).first())!-->Here I am getting fro my list of members, all the categories they belong to, and from there all the events there are, and from there all the dates. All within one single formula. This would be so great in Airtable.If I have a level hierarchy Company, Office and ContactThere is no way to bring the contact email to the Company record

I’m trying to get text and currency to combine and show properly with 2 decimal places even if they are 0.00.I have a product field (with text), a value field (with Currency)If i did a simple Formula {Product} & “ - $” & {Value} the result makes $18.30 from {Value} turn into $18.3 and $20.00 turn into $20, removing any 0’s after the decimal.Through research and help we can do the following equation:CONCATENATE({Product}, " - $", IF({Value}, ROUND({Value}, 2) & IF(FIND(".", ROUND({Value}, 2) & " ") = LEN(ROUND({Value}, 2) & "") - 1, "0", ""), "-.--")," "&{/Unit})This will put in 2 decimal places unless they are .00, then it reverts to suppressing the two 0’s after the decimal place, how do i get them back?

Hi 👋How can I get the average of the top/bottom 20%? For example, I have this table:Name | Sales Sabrina | $10 John | $5 Maria | $20 Ann | $100 Ben | $5 Sabrina | $10 John | $5 Maria | $20 Ann | $50 Ben | $1I want to see:Top 20% (average sales from top 20% sales): $75Average: $23Bottom 20% (average sales from lowest 20% sales): $3 Thanks!