Skip to main content

Formulas

Leverage this space to unlock the power of Airtable formulas.

  • 5,939 Topics
  • 18,227 Replies
5939 Topics
L
L_C_BrownNew Participant
asked in Formulas
Workday_Diff and holidays in 3 different countries

This formula worked fine, until we brought on an additional team member in another country. I am now tracking this data for 3 team members in the US, UK, and Germany. I think I ultimately have 3 issues I’m trying to resolve: I’ve seen the great U.S. and U.K. Holidays tables created by user W_Vann_Hall, but my company has a number of additional days off outside of federal holidays I’d like to include. I’m going to go ahead and assume there is no easy way to do this. I don’t see one for Germany, and I’m not up for building that myself. But would it be possible to alternatively upload .ics files with the holidays for each company into a single table (in ISO-Format), and link those records for each team member? Assuming #1 above is more advanced than I’m capable of at this time, and #2 isn’t possible, is there a way to double nest the IF statements in my current formula to include 3 sets of holidays? Here is the formula: IF({Date Contract / Redlines / Feedback Returned to Stakehold

131
TheTimeSavingCo
A
Amanda_Hickman1New Participant
posted in Formulas
Can I make a Kanban from a calculated field

This is obviously very much related to my last question, but have a formula that is calculating the average score (0-5) from multiple judges. I’d like to create a Kanban view based on that average and I’m trying to figure out the best way to do that. I can round off to the nearest whole number, but Kanban requires a single select to create a view. Is there a clever workaround that would let me accomplish this?

171
Kamille_Parks11
A
AnxiousInspiring
asked in Formulas
Extracting the record ID from linked record and formatting

I have a situation where I need to display the linked records in a field like this (including the double “” on either end): [““recFMVIujI5jqSiSD””,““recf8rGUjcM0li5f4"”,”“recztuGU7Tl4GSSYU”"] Is this possible? I have this code to format it, I just need the ID part. '["' & SUBSTITUTE( {Relations}, ', ', '","' ) & '"]' Which displays: [“record1”,“record2”,“record3”] Instead of the record primary field name, I need it to display the record ID in a text field as formated above. I have a linked record column, then I added a new text field column that grabs the name from that linked column but I need it to be the record ID. Does that make sense? (replace the name with the record ID in the brackets)

41412
A
A
Adam_ReaburnNew Participant
asked in Formulas
Formula to track social media posts

Hello - We are creating a way to track social media posts for our clients. Clients will ask us to make a certain number of social media posts on their behalf, we then want to keep track of how many social media posts have been made and how many are still required for each client contract. Here are the two tables we have created Social Media Posts Created - Airtable - Content overview Social Media Contracts (requests) - Airtable - All content and requests I’m new to Airtable, so this may be a simple formula, but any help would be appreciated.

382
Ben_Young1
A
Amanda_Hickman1New Participant
posted in Formulas
Finding the average of a multi select

I’m having trouble even getting started. I know how I would do this in a real programming language, but I’m not sure how to use an airtable formula. I have a lookup field that is getting rankings (1-5) from another table. I’d like to average those rankings. The dumbed down version is just to average entries in a multi select. I would expect to be able to use COUNT() and SUM() for that but I’m not getting anywhere.

143
A
A
Alex_LawsonNew Participant
posted in Formulas
Extractic the very last email from a field

Hello there, I am trying to extract the very last email that is input into a field. Example of data would be: godzilla@gmail.com, mouse@gmail.com, waterbottle@gmail.com I want to grab the very last email no matter how many emails are input in the field. I have tried using a combination of RIGHT() and MID() but so far haven’t been successful. Thank you all in advance!!!

52
A
J
John_DlouhyKnown Participant
asked in Formulas
Formula for Combining Multi-Select Fields

Greetings Airtable Community, I am seeking help with formatting the formula below that combines data from two multi-select fields: {College}&" and "&{Department} Example of what the formula returns: Humanities and Department of Religion {College} is always be populated, but {Department} is sometimes empty. When this is the case, the formula returns: Humanities and Is it possible to edit the formula so that if {Department} is empty, the " and " will be removed, returning just the name of the College? Thank you very much!

173
J
J
Jane_BarbaNew Participant
posted in Formulas
Exclude weekends and holidays when tracking First Response Time and Completion Time

Hi everyone! Kindly asking for your help for formula in airtable. I’m trying to keep track of our team’s first response time and completion time duration for our Airtable tickets but we would like to exclude the count for non-working hours, weekends and holidays; our working hours will only be from Monday to Friday, from 8:00AM to 5:00PM GMT +8. Currently, I’ve already set it up like this: First Response Time duration in minutes DATETIME_DIFF({First Response Time},{Opened date & time (PHT)}, 'minutes') and Overall completion Time duration in minutes DATETIME_DIFF({Resolution Time},{First Response Time}, 'minutes') This is it looks like at the moment. Hoping for help from anyone! thank you in advance!

251
TheTimeSavingCo
M
MaciejNew Participant
posted in Formulas
How to add amount to current field?

Hi, on “IiloscZlotowekTydzien” i have formula =iloscZetonow*2. Field iloscZlotowekCalosc I need to update each week by adding iloscZlotowekTydzien to iloscZlotowekCalosc. Any idea how to do it?

173
TheTimeSavingCo
M
Michael_SusekNew Participant
posted in Formulas
Unable to edit any formula fields

I cannot edit any of my formula fields. I even created a new database in a new workspace just to see if there was some sort of reference error in my existing database. No luck. In all instances the formula field is simply greyed out / inactive. No error messages. First time poster. The same question was posted in April but it was never resolved and the thread is closed. Anyone know of a solution?

215
Rupert_Hoffsch1
Shannon_Bradley
Shannon_BradleyBrainy
posted in Formulas
Pull received value into total only once

Hey awesome community! Working on a new setup and cannot wrap my brain around something. I have an inventory management setup, with a table for all Inventory items and a table for Purchase Orders. As items are received I would like the amount to add into the “Quantity On Hand” total…but only once. Using the 12ga Sheet Aluminum as an example, I would like the On Hand to update to 10 units with todays (06/30/2022) received order, but I don’t want the formula to look at the received amounts every time…just pull it in the one time. We do a yearly inventory to update what we have and I don’t want to worry about the formula confusing things. I hope this makes sense to someone who can help make it make sense to ME!

174
Shannon_Bradley
R
Ryan_MooreNew Participant
asked in Formulas
Correct Nesting Formula?

Hi. I’m looking to track how much time (in days) a lead spends in each stage in a pipeline. I’m basing it on start and stop dates of time in stage. The formula I’m trying to accomplish is this (hope it makes sense): Is the start date blank? YES: Do nothing, stay blank. (end) NO: (ask) Is the end date blank? —1. YES: Subtract today’s date from start date —2. NO: Subtract end date from start date I came up with the following but I just get an error that the formula isn’t correct. IF({Start Date} = BLANK(), “”, IF({End Date} = BLANK(), DATETIME_DIFF(TODAY(),{Start Date}, ‘days’), DATETIME_DIFF({End Date}, {Start Date}, ‘days’) What’s not working?! Definitely new to AirTable and formulas in general so if there is another way to approach this, I’m all ears! :). Thanks!

84
R
R
RashaKnown Participant
asked in Formulas
More robust formula to eliminate quotes in rollup fields?

I have a concatenate function in a table’s primary field. One of the fields it combines is a rollup of companies in a Companies table. Typically, it’s one company per record, occasionally two. To strip the pesky quotes I get when there’s comma in a company’s name (e.g. Habor Lights, Inc.), this formula only partially works: SUBSTITUTE({COMPANIES}&"", '"', '') When two companies are rollup up comma-separated, the formula strips that comma which is in fact needed Company 1: Toyota Research Institute Company 2: Abbot Ventures Rollup: Toyota Research Institute, Abbot Ventures With formula applied: Toyota Research InstituteAbbot Ventures Can I improve on this formula or is there a better approach altogether? Edit: tried the in-rollup approach suggested in this thread – Close, but no space between company names. SUBSTITUTE(ARRAYJOIN(ARRAYUNIQUE(Companies)), '"', '') Result: Toyota Research Institute,Abbot Ventures

396
R
J
Joel1New Participant
posted in Formulas
FIND multiple text in a cell

Hi, I can’t find a solution to this: I have a Lookup field that consists of multiple text values separated by a comma. I’m adding a seperate column to indicate the status based of that Lookup field which should contain of 3 possible values: IF FIND “Paid” = Completed IF FIND “Pending” = Pending IF FIND AND “Pending” & “Completed” = Partially Completed Now it seems that AND doesn’t work in combination with FIND. Has anyone found a solution for this?

113
ScottWorld
D
Dennis_PetrouKnown Participant
asked in Formulas
Extract a text string from a URL

Hello! Thanks in advance for any help. I am trying to make a formula that will extract a text string from a URL. In this case, the URL is a playlist on Spotify, and the text string is the playlist ID number. Spotify Hang on Little Tomato Denny P · Playlist · 1 songs The ID comes after the playlist/ and before the ?, so in this case it would be 5napFAiwbcMwne6jUfmspg I know this should be fairly easy with REGEX or something, but I’m not up to speed on that stuff yet, so any help would be appreciated! Thanks!

481
Ben_Young1
D
Dane_ChristiansNew Participant
posted in Formulas
Get values from next record in view?

I have a formula that needs data from the record beneath it in its view. The next record in the view could be arbitrary, so this formula should look for the record below the current record for the values it needs. In a spreadsheet, this would be easy enough, just select which cell I’m targeting. However, this is tricky because this is dependent on the view configuration. Switching to a different view or changing the sort order would change the value of the field. Is there a way to do this?

81
Kamille_Parks11
C
CloeNew Participant
posted in Formulas
Changing SUM of Groupe - period hours employee

I am showing a database for timesheets. We collect data by day and by hour. But our administrative department processes the information by period. Sometimes some employees have outside contracts and so their hours change. The number of hours for a period goes from X to Y and back to X. For the moment, each case is automated according to conditions. (Information coming from the employers) But good boss, sometimes wants to change the total hours of a period directly and that all applies to the period. In the example, he wants to change directly the 70 and not the 7… any idea ?

00
C
J
James_RhoadsParticipating Frequently
asked in Formulas
Some Concatenations having "" and others not

Hello All! I’m using a formula to concatenate different dates an area was worked on as the record ID. In one tab I have all the offices and all of the associated data with them, and in the other I have a submissions page that’s linked to the office page. My problem is some of them concatenate as “AreaName” - Date and others are AreaName - Date. I have attached some screenshots for a visual. Thank you for your help!

105
B
L
LuxielNew Participant
asked in Formulas
Counting values

Hi! I am trying to build a formula but I haven’t quite found the solution in the community. I have a table with different customers information, with fields such as “name”, “surname”, “email”, “phone”, “company”… I would like to create a separate table in which I can count how many customers I have from the same company. So let’s say I have: Table 1: customer 1 — Company A customer 2 — Company A customer 3 — Company B customer 4 — Company C customer 5 — Company C customer 6 — Company C In table 2, I would need to show: Company A — 2 Company B — 1 Company C — 3 I have the company field built as “single select”, since I work with a limited and specific list of companies. Not sure this makes any difference in terms of formulas. Thanks a lot in advance!

83
ScottWorld
S
Sara_DavisNew Participant
asked in Formulas
Nested If Formulas

Hello Community! I am trying to figure out the following with a nested IF formula- returning a value based on the name of the project. I have 8 different projects to categorize into one of 3 categories. I keep getting an error message saying to check the formula - what am I doing wrong? IF({Project}=”Hours”,“Personnel”, IF({Project}=”Sales”,“Finance", IF({Project}=”Marketing A”,“Finance”, IF({Project}=”Marketing B”,“Finance”, IF({Project}=”Expenses”,“Finance”, IF({Project}=”Diversity”,“HR”, IF({Project}=”Life Event”,“HR”, IF({Project}=”Executive”,“HR”,"Unknown”)))))))) Thank you!

02
S
Ricardo11
Ricardo11Inspiring
asked in Formulas
Help extracting a string from a rollup (FIND,MID,LEFT,RIGHT)

Hello community, my record has a date and looks up a roll-up like the below example. I’m trying to figure out the right formula to extract the string below: Rollup: …5. 04.05 Vila Falo,6. 05.05 Alvero,8. 07.05 Gjirokastra… Record date: 05.05 Current formula: MID({Rollup}&“”,FIND(DATETIME_FORMAT(Date,‘DD.MM’),{Rollup}&“”)+5,XXX) Output: Alvero,8. 07.05 Gjirokastra… Desired output: Alvero XXX is the part where my monkey brain fails to come up with a solution to determine the length of the string to be extracted. Your advice would be appreciated. :slightly_smiling_face:

574
Justin_Barrett
D
Dustin_GoodKnown Participant
asked in Formulas
Setting up reminder based of rating field

Just started working with a dental lab and I’m trying to create a base for targeted outreach to dental offices. After being out in the field today I have entered my notes and assigned a rating to each office. I would like to use 4 stars or 5 stars ratings to trigger an email automation to respective teammates about these particular offices.

60
D
Tristan-ARG
Tristan-ARGKnown Participant
asked in Formulas
Extract text from a string - REGEX

Hi!, I am trying to use the REGEX formula, but I cannot use it propertly. I need to extract names from a string. To make it easier the names are written after a hashtag #. This are examples #TOM write the essay of a client Complete the packing list for #Peter From an old post I managed to use this formula REGEX_EXTRACT({Transaction Name}, “#[^\s]+”)) written by Justin Barret This formula kind of works, but is giving me the answer with the hasthag in front #TOM #Peter I needed it without the hashtag. Any clue about how to do it? I am getting lots of trouble to understand the sintaxis of the REGEX formulas. Thanks!!

12222
Tristan-ARG
R
Robert_FarrellNew Participant
asked in Formulas
Is there any way to round up these qauntities?

Is it possible to round up those really long decimals? Thank you!

152
R
L
Lauren_Briskin1Inspiring
posted in Formulas
Languages with special characters?

I have been searching and searching but can’t quite find the answer. I have text strings in languages that are not Latin script. I see that languages aren’t supported, but that seems to be referring to the interface. I also see the set_locale function, but that seems only to work with datetime_format. Is there a way to set_locale for all of these languages for plain text? Currently, my long text fields are outputting a lot of garbage when I copy/paste into them. I tried converting to rich text, but this had the effect of chopping off half of the text. I also tried using set_locale with the appropriate language, but that just threw errors. Thanks!

110
L

Badge winners

  • Welcome
    youssefhas earned the badge Welcome
  • First Topic
    _Chris_has earned the badge First Topic
  • First Topic
    GegHhas earned the badge First Topic
  • Welcome
    blessing333has earned the badge Welcome
  • Welcome
    margaryhas earned the badge Welcome
Show all badges
Terms & Conditions