Skip to main content

Formulas

Leverage this space to unlock the power of Airtable formulas.

  • 5,940 Topics
  • 18,230 Replies
5940 Topics
C
Christopher_CanKnown Participant
asked in Formulas
Rollup Fields with Dates & Min/Max?

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?

991
Kamille_Parks11
T
Tom_RhalterParticipating Frequently
posted in Formulas
Struggling to create a Substitute formula

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 separate the value being substituted with the new value but that didn’t make a difference. I’m pretty sure I copied the format of the example correctly otherwise so I’m not sure what I’m doing well. I have no experience programming, I’m just teaching myself as I go along so any help/advice would be extremely appreciated. Thank you!

44
T
S
Struan_FarquharParticipating Frequently
posted in Formulas
Formula to get Collaborator's email

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.

31
Databaser
A
AM858Participating Frequently
asked in Formulas
Regex for this field?

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?

61
Justin_Barrett
M
Motlaq_AlMutairParticipating Frequently
posted in Formulas
How to retrieve the name mentioned the most in a list (look up field)

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

63
Justin_Barrett
T
Trudy_LeiNew Participant
posted in Formulas
Unique Lookup values across a grouping

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!

20
T
D
davidcarldesignInspiring
asked in Formulas
Extract numerical value from string

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:

7310
D
K
Kevin_BatdorfNew Participant
asked in Formulas
Return rows that contain multiple select item

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

210
K
R
Rae_GroshanParticipating Frequently
posted in Formulas
IF Statements where two conditions have to be true

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” )

106
R
D
Dennis_PetrouKnown Participant
asked in Formulas
Calculate days, but if it falls on weekend choose the friday before

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!

276
Justin_Barrett
R
Ross_Drummond1New Participant
asked in Formulas
Calculate details for a coffin nameplate (# Years, # Months, # Days)

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.

62
R
B
Betsy_DeWittNew Participant
asked in Formulas
Formula novice IF formulas that choose between two columns

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

82
B
S
Sebastian_ApelNew Participant
asked in Formulas
IF Formula using dates

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!

135
Databaser
J
Julian_PerezNew Participant
posted in Formulas
Hex Decimal Conversion

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!

374
J
M
Moshe_PeretzNew Participant
asked in Formulas
Quotation marks while copying a formula field

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?

645
M
W
Wayne_MerlinoInspiring
asked in Formulas
Value of <= with dates!

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:

152
W
B
B_Michael_69New Participant
asked in Formulas
Use number(s) from Single Select in calculation formula to average the score

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

51
kuovonne
S
ShneorParticipating Frequently
asked in Formulas
If column A contain X - display value of column A, if contain Y, display value of column B

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

128
S
C
Coaching_KlikNew Participant
asked in Formulas
Is this possible with Airtable formulas?

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:

112
C
W
Wayne_MerlinoInspiring
asked in Formulas
Finding the duration between two date and time fields

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

123
Databaser
K
Kelsey_JuszczakNew Participant
asked in Formulas
Formula to add checkmark if post date is after today

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

164
K
S
Sarah_RoachNew Participant
asked in Formulas
Making date last Thursday of each month

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!

422
S
R
Rebecca_Bartel1New Participant
posted in Formulas
Counting contact names within accounts

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).

51
Databaser
T
Treffsikker_KjeNew Participant
posted in Formulas
Formula for calculating trends

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?

41
Databaser
T
tars_sidekickNew Participant
posted in Formulas
Referencing individual cells in formula

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

463
T

Badge winners

Show all badges
Terms & Conditions