Skip to main content

Formulas

Leverage this space to unlock the power of Airtable formulas.

  • 5,941 Topics
  • 18,240 Replies
5941 Topics
C
courtneydeanNew Participant
asked in Formulas
TRIM Formula

I have a formula field that creates an email address for me, but when there's an extra space after someone's last name it returns an extra "." and breaks my automation connection to create the email. Ex. sandy.castellano.  --- it should be sandy.castellano I thought adding TRIM to the last name would fix the issue but I keep getting an error code. Can anyone help with removing extra spaces in the last name? Current Code: SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(  (REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(LOWER(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(first_name & " " & last_name, ",", " "), "'", ""), "  ", "&nbs

410
C
HaraldPalma
HaraldPalmaParticipating Frequently
asked in Formulas
SWITCH function for a date field ?

Hello,sorry, if I ask a question that might have been asked before. I am creating a simple private bookkeeping table and would like to build a SWITCH function that stamps the DATE when a value changes ("paid" / "open").Furthermore, I have tried the TODAY() function, but it updates every day. The LAST_MODIFIED_TIME() works, but if I change the value of the field, it will automatically update again. I would like to build a function that stamps the date only once, after the value "paid" / "open" changed for the first time.This is the formula I used so far:SWITCH(Status, "bezahlt", LAST_MODIFIED_TIME()) Thank you very much!

222
OpsDir_Cassie
D
Daniella_CastelNew Participant
posted in Formulas
Birthdays on Airtable

Does anyone else keep tracks of birthday on Airtable? How do you filter it so you can see them in the new year? I typically filter them out by months but just realized that everyone that filled it in last year will now show up this year. TIA!

173
TheTimeSavingCo
S
samb1758New Participant
asked in Formulas
Invalid Date?

I am trying to make a formula that takes the date/time from a previous column, and if the time is in the p.m, display the current date, however, if the time is in the a.m, to display the previous day's date. Attached it my current formula, which works for the p.m. times, but shows a mixture of 'Invalid Date' and an oddly specific time (see photos)Any ideas what I'm doing wrong?  

275
TheTimeSavingCo
K
Krystal_TobiasParticipating Frequently
posted in Formulas
Can't read code

Hi allI only know basic HTML so the Functions field in Airtable to me looks like hieroglyphics.In my events company, we require people to complete forms to update their existing data. I followed this tutorial: "Use a form to update an existing record" https://www.youtube.com/watch?v=BkAY7OHEgdc - and got the 2 tables working with automation.However - if I want to apply this method to other bases, I'm worried because I don't understand the purpose of any of the code I've written. Can someone explain what is happening/the purpose of the following (broken down to baby level please eg. what does '' do in the code? what about &?):'https://airtable.com/shrPd1axJx1k3Yg6u'&'?prefill_Guest%20ID='&RECORD_ID()&'&prefill_Guest%20Name='&ENCODE_URL_COMPONENT(Name)&'&hide_Guest%20ID=true' Happy to read material if anyone wants to point me to any resources. Thank you.

206
Jack_Manuel
N
n0xpInspiring
asked in Formulas
TODAY() cannot read date from Created Time field correctly?

Either I've stumbled upon a great bug or I'm a complete idiot. If history is anything to go by let's assume the latter.In a greater context I'm using a formula to check if TODAY() is in the range of some dates.Sometimes the date is from an automatic Created Time field, other times it's from a regular date field. When I check the Created Time (doesn't matter if the field is with out without a timestamp) on a record created moment ago against TODAY() I get a false.I used it in a long formula, but boiled down to it's minimum it just doesn't work. IF(TODAY()={Created Time},"1","0") The above returns 0 on a record created 15 minutes ago.If I ask it to check a date field with today's date inserted, then it returns 1.What's going on and how can I work around this?

133
Greg_F
R
Raphael1New Participant
posted in Formulas
Rollup Fields with Dates & Average

Hi all,I am trying to extract the mean/average of a set of dates through roll-ups. I am able to calculate the min or max date through the use of MIN(values) or MAX(values), but AVERAGE(values) returns NaN.Any suggestions? (thanks in advance!) 

61
kuovonne
V
VutropushNew Participant
asked in Formulas
HTML Inside of Formula?

Hi all, I'm trying to set up a HTML button with a link that includes a variable "Name" (name of the product) like this: I tried adding " " to make everything except &Name& green but it didn't work. Is there a way to put HTML in the formula and use variables? Surely I'm missing something. Thanks!  

312
V
D
Daniel_RNew Participant
asked in Formulas
Loop a Concate Formula

I can't seem to figure out how to loop a concate formula. I have 2 columns: Show Name and Episode Number. Show is single select and Episode is multi. My desired output is a string that loops the show name before the episode number; instead I only get Show Name, then a list of episodes with a comma deliminator.The desired outputs are the names of linked records. Example base below. Any help truly appreciated.https://airtable.com/shr07v7lc15RGEaj5 Show (single)Episode (Multi)FormulaDesired OutputThe Boys301, 302, 303The Boys 301, 302, 303The Boys 301, The Boys 302, The Boys 303The West Wing401, 502, 302The West Wing 401, 502, 302The West Wing 401, The West Wing 502, The West Wing 302Archer201, 303, 105Archer 201, 303, 105Archer 201, Archer 303, Archer 105

504
D
S
Sara_OphoffKnown Participant
posted in Formulas
Separating a long ling of text into distinct list and then inputting it back into multi select field

Hello! I have a Typeform that is connected to an Airtable base.There is one question that in the Typeform that is a multiselct (the user can pick more than one option).When it translates to the Airtable multiselect field (the field is call "Select Dates"), it comes through as a massive long option that is super hard to read:  YIKES - I am wondering if there is a formula that can separate this out into distinct values, such as - Tuesday: February 28th at 12p PT / 3p ET Wednesday, February 15th at 10a PT / 1p ET Thursday February 9th at 11a PT / 2p ET Tuesday, February 14th at 10a PT / 1p ET Thursday February 9th at 11a PT / 2p ET Tuesday, February 14th at 10a PT / 1p ET (there are also duplicates in there.... but thats not the worst thing / I don't mind... )My plan is to then take the output of that formula and use an automation to re-submit the cleaned up values into the multi-select field named "Select Dates" in Airtable.The formula se

21
TheTimeSavingCo
Ashley_Jackson
Ashley_JacksonInspiring
posted in Formulas
What datetime format is this?

I'm attempting to prep a table for import and need to recreate a concatenated formula field that includes a date field formatted like this:Can anyone identify this format for me?  Thanks!

543
Ashley_Jackson
T
Tamas_KoncsikParticipating Frequently
asked in Formulas
Formula result not being recognized as a number.

Hi All,I have text field called Name, which contains data in the following format: XXX - YYY - ###, where XXX and YYY are random texts, ### is a 1/2/3 digit number and they are always separated by " - ".In another field I'm using the following formula: TRIM(MID(Name, FIND("-",Name,FIND("-",Name)+1)+1,3)).My goal is to find the number at the end of the Name field and use it as a number in other calculations, but although the result looks good, it is not recognized as a number.Any suggestions?Thank you in advance!

235
Ron_Daniel
A
amykalraParticipating Frequently
asked in Formulas
Using IF and FIND in a formula

Hi everyone,I'm very new to Airtables and I'm trying to figure out some formulas. For now, I'm trying to figure out training completion from my base. The 'Training Completed' field is a multi-selection field (also a linked field) that shows all training an employee has completed. I want to create a formula where if a person has completed Training A and Training B, then the result is 'completed'. So basically, out of the many options that present themselves in the multi-selection field, I need to select 2 specific pieces of training that indicate that a program has been completed. I've tried using the IF (FIND) formula. I'm getting part of it correct, where I've just included Training A, but when I try to add in Training B, the formula doesn't work. This is what I have so far:IF(FIND("Training A", {Training Completed}), "Completed", "Not Completed")  I need to add in Training B to the mix as well. Can anyone help me with this? 

780
A
Cole_Wedemeier
Cole_WedemeierInspiring
asked in Formulas
Formula to Extract Last Name from Full Name

Hello!I know at one time there were several threads that spoke to extracting the First Name from a full name, and also a formula for extracting the Last Name. While I can find several threads with the code for First Name, I cannot find any that show for Last Name now that I'm in need of it 😂First Name Formula Found: IF(FIND(" ",{Full Name})=0, {Full Name}, LEFT({Full Name}, FIND(" ",{Full Name})-1))I've tried different variations using RIGHT instead of left, but I either get really odd returns where some last names are extracted properly, and others are completely way off, or I get blank returns. I have several Full Names that include more than 1 name as that Last Name. Example: Olivia Gutierrez Lawson. I would need "Gutierrez Lawson" to appear in the last name field with the formula. I feel confident in some of my formula creation skills and then there are times like this when I realize just how little I know haha. I am so grateful there is a community

2228
Ron_Daniel
A
aleajewelleParticipating Frequently
asked in Formulas
Count Number of Weekdays

Is there a way to count the number of weekdays between a date range? I am currently using the following formula to count the number of days between the FROM and TO DATE:DATETIME_DIFF({To Date}, {From Date}, 'days')+1 If the date range includes a weekend, I only want to include the number of weekdays. 

460
M
J
Jesper_HolmstroKnown Participant
posted in Formulas
Dynamic 2 weeks calendar solution

Hey guysIm stuck with a problem with some formulas, I got some real good help from @Andrey_Kovalev in December but now I'm stuck, so please if someone could advise. Long story short, I have 10 records (Mon-Fri odd and even weeks)and I want the date to change dynamically, i.e Today Monday the 16th will turn into Monday 30th after midnight, so just a date change on that record. We got this to work for one week but when the date turns into Sunday 22nd the 1 row will go back to the 23rd, it doesn't remain to the 30th. The Formulas are:Calendar formulaIF(Today <= Date, Date, DATEADD(Date, 14, "day"))Date formulaDATEADD(Today,Autonumber-WEEKDAY(Today),"day")Today formulaToday()And some pics from the base so you understand:Works fine for this week and date in the Calendar formula works:And on Sundays it all jumps back:Best regardsJesper

299
M
G
Georg_KuklickParticipating Frequently
asked in Formulas
Extracting or deleting string in brackets

I have one column with text in the following format: A Meeting By The River (Album) 3 versions I need to split it into 3 columns 1: the string before the bracket 2: the string between the bracket 3: the string after the bracket

618
S
E
eryndNew Participant
posted in Formulas
Hiding 'Completed" Status rows in Grid View

Hi All,I currently work off Airtable's grid view, using it as a collaborative content planner/scheduler. Throughout this year, this will get extremely clustered and lengthy. At the moment, we have a status column of- Briefed in, In progress, Scheduled and CompleteAt the moment, I can filter to show if 'Completed' is not selected, but I don't see this being a viable long term option. Is there a formula where once 'Completed' is marked, the row is immediately hidden & potentially moved to another page/tab where it can be easily accessible without having to turn on/off a filter? I have heard of a few formulas/features being thrown around in my search for an answer but being fairly new to Airtable, it's still quite confusing! Thanks in advance for any help - Eryn.

582
TheTimeSavingCo
D
DRM_Vol_Assist8New Participant
posted in Formulas
Conditional Rollup

Hello all!  I am trying to add up all volunteers signed up for a given shift, and I am having a bit of trouble figuring it out.  I've tried multiple approaches and I am running into a wall.  Please Help.Can I use a Rollup field in 'Table 2' to add up the number of people signed up in a # field named '# of Vols' in 'Table 1' that signed up for one or more of a possible 7 time slots? The time slots are in a multi select field in 'Table 1' named: 4:00 pm - 6:00 pm (4/6), 6:00 pm - 8:00 pm (4/6), 5:00 am - 7:00 am (4/7), 6:30 am - 9:00 am (4/7), 8:30 am - 11:00 am (4/7), 10:30 am - 1:00 pm (4/7), and 12:30 pm - 3:00 pm (4/7). Those time slot names are also the unique Identifier field in 'Table 2'. If multiple time slots are selected, the formula should add the value from "# of Vols' into each time slot selected. 'Table 1' is named 'Event Volunteer Tracker', 'Table 2' is named 'Event Shift Tracker, and the multi select field in 'Table 1' for the time slots is named 'SHIFT TIM

22
TheTimeSavingCo
J
JLaws8New Participant
posted in Formulas
How to add the amount of times each product has sold

I have my inventory list on one table. I have another table where I enter the date a product has sold and how many units it sold on that date. I would like add the amount of times each product has sold within the last 30 60 & 90 days on my main table. Is there a good way to do this?

11
TheTimeSavingCo
N
Noah_ColemanInspiring
asked in Formulas
Calculating Time without using Date field type

Is there a way to calculate times without using a datetime field? For example, if I have a start time of 11:30am and an end time of 3:30pm, can I use a formula to calculate the difference without using a datetime field?On the surface, I can do this by making my start and end times Duration-type fields, but I'd have to switch to a 24 hour format, which is less readable for me than a 12 hour format (Americans, amirite?). Also, the Start and End times will be used in concatenate formulas, so formatting becomes a real problem (Tuesday 8:30-11:30 becomes Tuesday 30600-41400). 

920
N
M
Michael_Morris2Inspiring
posted in Formulas
Can I return a specific record (like Record #6) from a field?

I have a field of numbers somewhere between 1-100, I'll call the field "MAX" and sort high-low. I want to be able to count the total number of records in this field, and pick the lowest numeric record of a certain percent, let's say 80%.For an example, let's say my MAX field has the following records: 95, 84, 83, 81, 70, 68, 67, 60, 54, 46. I want to count the number of records (there are 10) and find the top 80% of numbers. So this would be 10(.80) = 8 records and return a record set of 95, 84, 83, 81, 70, 68, 67, 60. Then my ultimate goal is to display the lowest number of this data set, so the result would be 60. Is this possible with a formula? A script (I don't know how to script, so this might be out of reach for me)? Thanks for any help!

21
kuovonne
M
mairesNew Participant
posted in Formulas
Using a function to create precents

Airtable support!Thank you in advance for helping me with this issue. I would like to use Airtable to track web page metrics. Each week, our company publishes web articles. The first column is the date the article was published, the second column is the name of the article, the third column is the page views and we would like the fourth to show the percent each page article was viewed. An example is below. The percent column should be as follows: 2023-01-0326.67%33.33%40.00%2023-01-1016.67%33.33%50.00%Would someone be kind enough to help me create the formula needed for this outcome? Thank you!! 

41
kuovonne
S
SondraWithAnONew Participant
posted in Formulas
copy record to base based on condition

Hi! I'm trying to figure out a way to copy a record from one base to another when a certain condition is met. I have people submitting proposals for classes in one form. And once accepted, I want that same information to populate the current classes base. Can I do this? And have the info appear in different cells on the new base (like in a different order, etc)?I'm not a coding person at all, so I'm trying to figure out the most basic way to do this. Thank you!! Sondra.

72
S
Javier_Growth_L
Javier_Growth_LInspiring
posted in Formulas
Calculate percentage of total of a field

Hi there Airtable folks! I'm trying to wrap my head around a simple calculation - but I'm not sure how this can be done. I'd like to calculate the % of total of a given column, I know I can input a constant number into the formula but the table will increase it records, so it has to be calculated. Essentially I want to perform the following, take each number and divide it by the total.  Is there a way to do that?Thanks a lot!

3553
Javier_Growth_L

Badge winners

Show all badges
Terms & Conditions