Formulas

I’ve got a rollup field tied to connected records with dates, and I’m not 100% sure how it works. My goal is to have this rollup field only show the most recent date. So if it is rolling up from two entries, lets say: 1/1/2020, and 10/10/2020, I only want to see 10/10/2020. I’m assuming I use the MAX() attribute for that…but I’m not sure. Can anyone confirm what the appropriate aggregation formula is to display the most recent date of several in a rollup field?

I’m working on a character generator for Dungeons and Dragons and I’m having trouble building a formula to get all the information in one place. This is the view on the form I created: My goal is to create a formula that get rid of the numbers and just displays what Race the player selected (elf, dragonborn, etc.) for easier readability in grid view. When I write the formula to only refer to a single number it works fine. But when I try and have the formula substitute multiple numbers with a blank space it returns an error message. I was using the example formula on the side as a reference which uses => instead of a comma to sepa

Hi I’m trying to find a way to display the email addresses of the collaborators listed in another field. but I can’t seem to find any way to display this. I’m syncing a Client’s table to my teams table, and the client has a collaborators field, they are fine adding in an additional helper field for us but just can’t figure out what this needs to be. Thank you for any help with is.

I am looking to filter properties for owners that live out of state. I get information from my data source in the format below. What’s a Regex formula I can use to separate out the City, State, and 5digit ZIP?

I have a look up field that contains a list of companies’ names. I want to get the most mentioned company in that list. More details: I have three tables: Tenders Entities Companies Tenders table has a linked record from both entities and companies. From the entities table I created a look up table that retrieves the companies column. I’d like to get the name of the company that was mentioned the most. The idea is to answer the following question: Which company gets the most tenders of each entity? regards, Motlaq

I can’t figure out how to summarize a count of unique records across a grouping, either with a rollup or formula or even with a pivot table. I’ve created a sample base here with creator permissions. On the Interests tab, you’ll see the Categories I’m looking to count unique People across. Each of the groups should have 4 unique People. How do get that to show up somewhere? Thanks!

Hi folks, I’m trying to find a numerical value from the “Title” field and have it appear in the “amount” (e.g. 27.21) I’ve used this formula from looking at a few post’s but it only copies the values before the decimal: MID( {Title}, FIND( “For”, {Title} ) 6, 50 ) Another formula I found only copies the values before the decimal: IF({Title}, VALUE(RIGHT({Title}, LEN({Title})- FIND(“0”,“1”,“2”,“3”,“4”,“5”,“6”,“7”,“8”,“9”, {Title}))), 0) What formula do I need to get the full total which in this example is 27.21? Thanks in advance! :winking_face:

Hi, I would like to return all rows that contain a specific term. For example, I have rows that have two terms, [Default, Bar]. Using AND(term='Bar') will not work, but using FIND('Bar',term) will work. Except that it will also return results that have the term “Barber” as well. Is there a way to select rows that contain a term? Like FIND([Bar], term) for example? Thanks

Hello! I am working on writing a formula, and running into a few issues. To preface, I am tracking employee trainings in this database. We have 3 trainings that expire. 2 expire after 2 years and 1 after 4 years. I am trying to write an if/then statement where if the training name matches one of those 3 and it’s within a specific time frame, it will output “Overdue”, or “Up to Date”. Logical statements that I need: If the training name = “First Aid” or “CPI” AND the Days field is greater than 730 days, the training is overdue. If the training name = “Notary Public” And the Days field is greater than 1460, the training is overdue. I’ve been working on it for a bit, and have a first attempt at the logical statement, but it’s not working. Any ideas would be beyond helpful! IF( AND( {Trainings} = “First Aid”, {Trainings} = “CPI” )& IF( {Days} > 730, “Overdue”, “Up to date” )

Hello! I am trying to make a formula to calculate X number of days before a Launch Date. I do not want to exclude weekends, however if the Due Date happens to fall on a weekend, I want to make it that Friday instead. I know about the DATEADD({Launch Date},-7,‘days) formula, and I’ve seen the WORKDAY function but not exactly sure how to make this work. So when calculating the total days before launch I DO want to include any weekends, but if the Due date falls on a weekend, it should add an extra day or two to make it that Friday… Thanks!

Hi, I need to calculate the age of a Deceased. I assume a formula is needed. I have Date of Birth and Date of Death to work with and I need the know the age in Years, Months and Days. Any help would be greatly appreciated.

I obviously don’t know what I’m talking about, so apologies in advance. I was inspired by this Team Schedule using Airtable blog post where they have a formula column that delivers “team member + Status” so I’m trying to create a formula that achieves something similar, but is a little more complex: My columns are: Brand = lookup from another table example “Client 1” Project = lookup from another table example "Awesome Website" Person = lookup from another table example “Betsy” Status = single select example “Working” or “OOO” My goal is to get the formula to deliver [Shortened {Brand} name | {project}] - Status] or {person} - Status where the result EITHER shows: shortened brand name + project + status (example C1 | Awesome Website - Working) OR person + status (example

Hello all, I would like to use an If formula: Everything before a certain date should be “True”, everything after “False”. When I write it like this, it doesn’t work: IF(InvoiceDate<3/8/2021,1,2) (InvoiceDate is a Field in Type “Date” with Format “European”). Do you have any idea and could you help me please? Thank you very much!

Hi there! I’m using an API to retrieve the balance of crypto wallets. The issue is that the balances are returned in Hex decimal format and there isn’t a native function to convert from hex decimal to regular decimal in Airtable. The manual conversion is rather complex and not ideal at all. Wondering if there are any workarounds? Thanks in advance!

Hi guys, Recently, I ran into some weird cases while trying to copy-paste a formula field cell values. When I’m pasting the content, I’m getting the string with quotation marks at the beginning and the ending of the text, while in the field itself I’m not seeing them. Example: You can see there are no quotation marks in the cell itself, but when trying to paste the cell value, I’m getting: “Text text text” Any idea on how to avoid these quotation marks?

Hi Airtable Community! Need some assistance for a date formula that is stumping me. Here is the backstory and the info that accompanies the screen shot I have attached: I have a field that the user chooses for a month called “S1 Month – Choose”. I then have a field next to it called “S1 Month Shown”. This field converts the 1st field to a numeric value using the following formula: IF({S1 Month - Choose}=“January”,“1”,IF({S1 Month - Choose}=“February”,“2”,IF({S1 Month - Choose}=“March”,“3”,IF({S1 Month - Choose}=“April”,“4”,IF({S1 Month - Choose}=“May”,“5”,IF({S1 Month - Choose}=“June”,“6”,IF({S1 Month - Choose}=“July”,“7”,IF({S1 Month - Choose}=“August”,“8”,IF({S1 Month - Choose}=“September”,“9”,IF({S1 Month - Choose}=“October”,“10”,IF({S1 Month - Choose}=“November”,“11”,IF({S1 Month - Choose}=“December”,“12”)))))))))))) I have another field that is being used in this process called “Today” that is a formula field to generate the date of today. Here is th

I want to create a AVERAGE formula using 3 columns that use the single select function. See attached image.

Hello guys, I’m just getting started with Airtable formulas and couldn’t find the right formula for this scenario and will be grateful if you could help here. I’ve 2 columns when based on the value on column A I want to determent if to display a value of column A or column B in the D column. = If column A contains 1 or 2 or 3, then display the value of column A, if contain Y, display value of column B Thanks

Hi there! :grinning_face_with_smiling_eyes: :wave: Is it possible to trim input, by using the _'s ? Or is there a more clever way? Input: John-Doe_recGQNicbCUqYIJ9s_coach-60bf663d8f264a0004816093 Output formula 1: John Doe Output formula 2: recGQNicbCUqYIJ9s Output formula 3: 60bf663d8f264a0004816093 Looking forward to some tips! Thankyou! :smiling_face_with_three_hearts:

Hi, I have a time and date stamp being created in a field (Last Modified Time) for a field that we use to say when an Estimate can be made - sort of a “Clock starts ticking Now!” timestamp. The original field that the Last Modified is watching is filled by a Zapier workflow. I then have a second field that gets time and date stamped (Last Modified Time) with when we check a box for when a user says the Estimate is done. Lets say the 1st field has a date and time stamp of 8/1/21 2:30 pm, and the second field has a time stamp of 8/2/21 3:34 pm. How do I calculate the difference between the two? The goal is 48 hrs, so if the total came out as hours I would be fine with that. I currently have tried the following formulas but it seems to be related more to the fomatting allowed by AT but that is just a guess. Here is what I tried: DATETIME_DIFF({Time Stamp on Estimate Checked}-{Time Stamp - Start Time}) also tried: {Time Stamp on Estimate Checked

I have been messing around with formulas for two days, trying to figure out how to put a checkmark in the column if the post date is today or after. So, for my social media posts (which I don’t have automated from Airtable, I post them using another platform), once the time has come and gone that the post should have gone live, I want the formula column for that record to have a check box. Thanks in advance for any help with this. Kelsey

Hi! I am trying to set up a field to automatic track the day of a meeting. This meeting is the last Thursday of each month. I found a formula to automatically populate the last day of each month but I can’t find/figure out a solution to change that date to the last Thursday. For Example Last day of the month for August is 8/31/2021 (Tuesday) but I need it to be 8/26/2021 (The last Thursday) Here is the formula I found for the last day of the month: DATETIME_FORMAT( DATEADD( DATETIME_PARSE( DATETIME_FORMAT( TODAY(), ‘YYYY’ ) & ‘-’ & (VALUE( DATETIME_FORMAT( TODAY(), ‘MM’ ) ) + 1) & ‘-01’, ‘YYYY/MM/DD’ ) , -1, ‘day’ ) , “MM/DD/YY” ) I was thinking another formula field using WEEKDAY() might work but it just gives an error when trying to change the day. Thank you in advance!

I’m working on creating a table for company accounts w/specific contacts within it. Table 1 - list of all contacts Table 2 - list of all companies The company table contains a link to the contact table to pull over the contact names & company name. What I want to do is create a field that counts how many contacts I have within each company. In my screenshots, the black lines are unique companies & contacts. The two yellow lines are unique contacts within the same company. The two green lines are also unique contacts within the same company. I want a new field that counts per company how many unique contacts per company. So the yellow company would read 2, black companies would read 1. This field would be in the company table (first screenshot).

I’m planning to use Airtable in storing price data from stocks over time. I’d like to make a formula that calculate the trend over the last time period consisting of X data points. In Google Sheets there are two functions for this “Slope” and “Trend”. Are there any equivalent for Airtable or a smart way to calculate the direction and steepness of the growth over time?

Hello, I am trying to write a formula to sum individual cells. It would look like =SUM(B4, H4, …) in excel. I cam across cell reference in the API (Airtable Scripting) but I am not sure how to reference individual cells in a formula within airtable. Thanks