Formulas

I was told Airtables had better database like features than Notion, but cant find them I have 3 tables, A → B → C , and want to get data from A in C but cant find a wayFor example I have a clients table, a contracts table and a monthly payments table, in payments table i link to the contract to be paid (and ofc contracts links to clients), clients table include a field security_deposit. I want to calculate the amount to pay for the given month, and include a checkbox to mark its the first payment. Another thing (that Notion can actually do) is to reference a column of a linked table in a formula, I cant find away to reference data by formula in Airtables.For example, in Notion using gui i cant do what i want, but I can use a formula to achieve it.This would be something like: if( first, contract.map(current.client.map(current.security_deposit)), 0 ) 

Hi,We’re trying to analyse operational and financial data longitudinally, over time, where e.g. weekly sales averages would be shown in a table in an interface.I know I can have averages with Numbers in a dashboard, but it’s not possible to calculate averages in formulas (like in Excel, one can sum vertically across a certain field). This would e.g. allow us a rolling 6 month sales average.Another need is to be able to create projections, based on historical data. For this one would need the earlier mentioned longitudinal averages, and then have some coefficients that one can change and make scenarios. Any ideas how to achieve that?Multiple columns each consolidating data for a certain week? That becomes difficult since neither weeks or month can be defined as “after/before” a certain number, only the previous and the next are available directly.rgds,Björn

Hello, I inherited an Airtable base that essential keeps track of new subscribers. It was set up to calculate the enrollment date (ex: 7/24/2025) and extrapolate the year registered into a new column. However, currently the column mostly remains blank and/or it has enrolled everyone in the year 2022. This information is then supposed to be reflected on another table with their information. I am new to using Airtable and any information or direct you can provide is greatly appreciated on how to input an effective dynamic date formula. 

Hello there! I need some assistance understanding why these return different results. DATETIME_DIFF(“8/18/2025”, TODAY(), “m”) = 0MONTH(“8/18/2025”) - MONTH(TODAY()) = 1 Thanks in advance.-K

I have a name field that sometimes has one name and sometimes has two. If there are two names, they are seperated by a comma.What is a formula I can use to show only the first names, with an ‘and’ in the middle if there are two.John Doe                                    JohnJohn Doe, Josh Frank             John and Josh

Hi, hi. Happy Midsummer. I hope you are all wonderful. I have the most simple task that I cannot figure out for the past ten days. I'll attach an image here. I set up my inventory tracking system. I included a button to receive packages of inventory and a button to delete used packages of inventory. I cannot connect these buttons to update my current inventory column. My buttons open beautifully to the form, but when I input two boxes were received, nothing is updated in my current inventory. When I update two boxes were used, nothing updates as well.Let me know what additional information I can give you if you have a possible code. I've tried a bunch, and it seems like such an easy task, but nothing is sinking on my end for me to make it usable.Thank you. Thank you for your help. I appreciate you so much.Lisa  

Hi, I want to track employee annual leave via Airtable. They’ll input Date From & Date To (including time). There will be individual calculation fields for each leave type (e.g sick, annual leave etc) and an approval status. So this formula would be wrapped in IF conditions; IF Type = ‘Annual Leave” and Status = “Approved” this calculation will apply. Formula needs toonly calculate weekdays (ignore weekends) understand 1 day = 7.8 hours only needs to count hours if the time differs between date from & date to fields. Understand working hours are 8:30-5pm (time zones differ) So outputs for the following would be;17 June 2025 3:00pm → 17 June 2025 5:00pm → 2.0​​​​​​​17 June 2025 3:00pm → 18 June 2025 3:00pm → 7.8​​​​​​​17 June 2025 3:00pm → 18 June 2025 5:00pm → 9.8​​​​​​​17 June 2025 3:00pm → 24 June 2025 3:00pm → 46.8 I’m open to doing this more simply via the base design if the formula is too difficult to write (e.g having the employee select from a drop down to say ‘full days

Hello! Recently took over Airtable and am getting familiar with it. I set up an easy lookup between linked tables, but am having difficulty with a little more complex scenario. I have two tables that are linked, let’s call them ‘Operations’ and ‘Account Management’.Each table has fields that are: Partner, Territory and Business Line.I’m trying to set up a lookup in the Operations Table, where if all three of the values match in the linked tables (match on Partner, Territory & Business Line) - then return the value in the ‘Account Manager’ field which is stored in the Account Management table. Assuming this is possible, any ideas on the easiest way?

Hi useful people, Our Airtable database contains multiple services with attendance data. We also log the individual names of each attendee. Airtable can easily gather the total about of attendees across a period of time - my question is, how does Airtable gather the individual data? For example, Airtable may tell us that there were 1000 attendees across a calendar year, but how many many of those are the same people? What we’re looking for is the amount of individuals who have accessed the service in the calendar year, not a total attendance tally. I hope that makes sense… thank you in advance.

I am new to airtable and am having trouble.  I have a cattle ranch and am working on a database to manage their reproduction.  I would like to calculate each cows conception rate based on the number of times she has become pregnant vervus the total number of opportunities she has mated.  For example, if she has been with a bull 10 times and has become pregnant 7 times in her lifetime, then her conception rate is 70%.I have each cows breeding records in the data.  I also have a field titled “pregnancy status.” If i use rollup for this field with:Source= Cattle ID#Cattle field to rollup= Pregnancy StatusPregnancy status field is: pregnant or non-pregnantHow would I create a formula that would look up the pregnancy status for each mating and then calculate the percentage pregnant for the cow’s lifetime? Thank you so much for any help you can give me!  

Hello, I have a JF table which contains dates of public holidays and an APPRO table which contains a date field. I can't test if the date field in the APPRO table is a public holiday. Does anyone have a solution?

I have a date time difference formula formatted to decimal with 2 places but not returning any values less that one. My formula is as follows: DATETIME_DIFF({End Time},{Start Time},'hours')What am I missing? If I recall correctly this should be able to return hours less than 1 (e.g. 0.5 for half an hour difference)

bonjour la communauté, la nouvelle mise à jour des champs de formule permet des sorties à sélection unique pour les champs de formule. J'ai un champs ou est concaténé plusieurs informations incluant une sélection unique. J'ai testé cette nouvelle fonctionnalité et apparaît la sélection unique en couleur, mais les autres informations concaténer n'apparaissent plus.merci de votre retour 😉

Hi, can someone please help with my formula? I’d like to calculate the number of days between two dates, but if the dates are the same then they should display 0. The formula below works to show the ones with the same date as 0, however it then shows the ones with different dates as being one figure too little. DATETIME_DIFF(  {Post Sale Date},  {Auction Date/Time (Local)},  'days') To fix this I added +1 to the formula, which of course, solves the latter issue but not the issue for same dates need to show 0. DATETIME_DIFF(  {Post Sale Date},  {Auction Date/Time (Local)},  'days') +1 Can someone please assist 😊

I'm working with a database structure that has three tables: Publications, Artworks, and Individuals. I'm running into an issue with a Rollup field and hoping someone can help me find a solution.My Table Structure:Artworks table: Has a field that references one or more Individuals Publications table: Has a field that references one or more Artworks Publications table: I want to create a Rollup field that shows all Individuals associated with the Artworks in each PublicationThe Problem:When I use a Rollup field with an ARRAYUNIQUE() formula to get the individuals associated with the artworks, I'm getting unexpected results.For example, if a Publication contains 10 Artworks, and each Artwork has 1, 2, or 3 associated Individuals (sometimes different ones), the Rollup field returns each combination of individuals rather than each individual individually.What I Want:I want the Rollup to return each unique Individual associated with any of the Artworks in the Publication, not the combinatio

I am importing three different reports into the same table.  One report’s DOB field provides the date as we wish it to appear, MM/DD/YYYY.  The other two reports provide a DOB format of DD-MMM-YY.  I am using a formula to convert the DD-MMM-YY format into MM/DD/YYYY and then using an automation to move the DOB Calculation into the DOB field.  Some of the converted dates are future years.  The formula correctly converted 23-Sep-97 into 9/23/1997, but 29-Jan-56 became 1/29/2056.  How do I get the formula to create DOBs in the correct century?Some years convert correctly, others not so much.Current formula. 

TODAY() and some similar functions are broken because they're stuck on Greenwich Mean Time. What it's meant to do is return a value that changes once per day, at the start of the day. It does this by setting the time part of the current date and time to 0 but, because Airtable's internal time zone is GMT/UTC and yours probably isn't, the result of the function will never be the start of your day. Any formulas that rely on it will have flawed results. The effects depend on how far from GMT your time zone is, and how accurate your formulas need to be. During summer in New Zealand, for example, with an offset of +13 hours, it behaves like a YESTERDAY() for more than half of each day. In California it becomes TOMORROW() early every evening. It can wreck reports and automations.Changing the time zone in the formatting of any formula that uses it has no effect on the underlying value. Verify the problem yourself: create a formula field with only TODAY() as the content and set its formatting

Hey folks, I’m trying to figure something out.I’ve got an old CSV doc that I’ve integrated. Unfortunately, it’s a mess and I’m only a “enough to get by” kind of Airtable coder. I’m a bit stumped.So, I’m trying to make a formula that combines multiple cells to make a condensed long text field for mailing addresses (if possible). Unfortunately, many of these individual cells are empty and there are 562 lines of them. There’s no good rhyme or reason as to what is empty, either. Sometimes it’s address 1 and address 2, sometimes there’s a city and state and nothing else, sometimes all of the information is filled out, so making a straight hierarchal system like I’m more used to seeing doesn’t really work.Example:IF(address 1, <all the info>,   (IF(address 2, <address 2 down>,    (IF(address 3, <address 3  down>,)))Here’s my best effort at what I want to do:Any advice? Thanks in advance!

In one of my first projects, I have a table with a multiselect field "names".Can I add a new column with some formula that shows the name that was last added or removed in the "names"-field ? (with automatic update for every change in that names-field) 

Something as simple as retrieving data from previous record and then mathematically operating this field (even within boundaries of cell) seems to take hours. If finding information or operating this platform is so difficult then whats the value.

Hi, We’re battling getting contact duplicates from Meta lead campaigns. Basically people forget they’ve already signed up, and send in a new registration. Since these are Meta, we cannot control anything upstream. A first step is to get our people, who only work in interfaces, alerted that there’s a duplicate to a contact. It would therefore be enough, for now, to achieve a field that tells the user the amount that the current user’s e-mail address can be found in the total base (~2.000 contacts now). I’m trying an approach where I link all contacts to one record in another table, then lookup all e-mail addresses. Back in my main table I link to the second table and lookup the e-mail addresses. I would the like to count the instances of the field “email” in the lookup field where all e-mail addresses are. FIND finds only the first and gives the position, SEARCH does the same. COUNT and COUNTALL do not work on text. How could I get the count? Rgds. Björn

Hi, can someone people assist with my formula? This is what I’m currently working with;IF({Post Sale Date},{Post Sale Date},{Event Date}) I want to expand on this to check the ‘Result’ field so it functions as below. IF ‘Post Sale Date’ exists then use ‘Post Sale Date’IF ‘Post Sale Date’ is empty and ‘Result’ is not ‘SUTH’ then leave blank.IF ‘Post Sale Date’ is empty and ‘Result’ is ‘SUTH’ then use ‘Event Date’ Any suggestions would be much appreciated! Thank you

Hello! I am working to create a style number. If a style has two different colors, it will need two records, but the same style number.I want the number to start with a certain number unique to the division, then another number unique to  the brand, then the third would be a number in sequential order. I have fields for Division, Brand, and Autonumber.   I used the below formula.  SWITCH(Division, "DIV1", "2", "DIV2","3", "DIV3", "4", "DIV54,"5",0)&SWITCH(Brand,"Brand1","2","Brand2","3","Brand3","4","Brand","5",0)&Autonumber The problem is I can’t use Autonumber because two rows may be the same style but different color.  As they are the same style, they need to have the same number. What could I use instead of Autonumber? Possibly the answer isn’t a formula, open to any suggestions.Thanks!

Hello airtable community,  I am facing a problem while working on my CRM base. I have an auto “created date” field to track when a new deal was created. I then use this field in the interface to create a dashboard so this field is important. The problem I am facin now is that some of my team members have not filled up this table with their deals and are lacking behind. They are now going to update it but when they will create a new deal the “created date” field will be updated as today’s date even though the deal might have actually happened months ago. In this way my dashboard will not reflect my actual dealflow. How can I have a field that will basically fill up itself as a general rule but which I could still modify if i need to? I hope this is clear. Thanks,

I have a nested IF formula that is working well, but is excruciatingly hard to maintain because it’s so many levels deep. It would be easier to maintain if I could use IFS(), but that doesn’t seem to exist in Airtable. Is there anything else similar I could use? The logic is (in pseudocode) : IF regex_match(MYFIELD, expr1) then regex_extract(MYFIELD,pattern1)ELSIF regex_match(MYFIELD, expr2) then regex_extract(MYFIELD,pattern2)ELSIF …. etc for far too many more patterns …ELSE blank()ENDIFI don’t think I can use SWITCH or CASE (if these even exist in Airtable) because each if statement clause is different. Essentially, I have a string that may contain one and only one of a dozen substrings, and I need to detect and extract these substrings whenever they show up. Fun times.