Formulas

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 Betsy - OOO) I’ve poked around several topics and the formula database trying to figure it out but I don’t know coding syntax or complex formulas. Here’s what I have so far: Attempt #1 (kind of works?) {Brand} & "

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 the formula I am using for this:

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}-{Time Stamp - Start Time} Any help would b

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

I created a formula field to merge three date fields into one. No record has more than one date, so I wanted them all to be in one place. However, when I used concatenate, the dates are very long, so I’m trying to use DateTime_Format for all three, but I keep getting Errors. I’ve tried looking this up and have tried different combinations, but no luck. Here’s what it looked like originally before I tried formatting

Hi all, I am trying to combine text from two fields using Concatenate (and that’s simple enough) but when I try to add a Substitute formula to shorten one field’s text, it doesn’t work. I am new to formulas, and am sure I’m doing something wrong here. Here are the two formulas that work on their own, but not together. Thank you for any insights! SUBSTITUTE(RIGHT({2021-22 School Program & Membership},LEN({2021-22 School Program & Membership})-22) CONCATENATE({Program Booked},’ / ',{School Name})

Hi all, I am terrible with formulas, and have spent way too long looking through previous posts and reading up on Replace and Substitute and Find formulas. I have a column with various names for programs. I would like to cut the first 22 characters of the title and replace any instance of New School with “New/Comprehensive” for Salesforce upload purposes. Any idea where I’m going wrong here? REPLACE({2021-22 School Program & Membership},1,22,""), FIND(“New School”,{2021-22 School Program & Membership}),17,“New/Comprehensive”) I’ve also tried variations of combining Replace with Substitute. Both work on their own but I can’t find a way to combine them in one field. This may be impossible, but the more likely scenario is user error on my part.

I’m guessing there’s a syntax error here… I can get the formula to save if I copy a sample formula that includes 3 or 4 arguments, but as soon as I add more, I get an error when trying to save. PLEASE HELP! IF( {Ticket Total} > 0, SWITCH( {ShowDate4Formula}, “2021-09-11”, “PIT”, “2021-09-18”, “MAR”, “2021-10-02”, “PHL”, “2021-10-30”, “MD”, "2021-11-06”, “PAS”, "2021-11-20”, “SD”, "2021-12-04”, “DEN”, "2021-12-18”, “SJ”, "2022-01-01”, “AUS” ) )

From this: animals > cat > face > eyes into Animals/Cat/Face/Eyes how can i do that i know the first step: SUBSTITUTE({Category 1}, ’ > ', ‘/’) // this replaces the > into / and then how can i use the upper case method only for the first letter of every word? thanks for the help please write an example code, thanks!