Skip to main content

Formulas

Leverage this space to unlock the power of Airtable formulas.

  • 5,938 Topics
  • 18,225 Replies
5938 Topics
R
Ron_DaniellNew Participant
posted in Formulas
How to replace commas with line breaks in a field

Currently I have a field that contains multiple products separated by commas. Is there a way to “find” all commas and “replace” them with the “/n” line break? I would like the product field which currently looks like this “product1, product2, product3, product4” to appear in the field as product1 product2 product3 product4 Is there a way to do this?

13315
N
J
Jocille_CoronicNew Participant
posted in Formulas
Create a Schedule Based on Time

I wanted to assign a delivery timeslot to an order based on the order created time. Example, if a customer places and order after 8pm and before 12midnight, the schedule delivery will be between 8am to 11am the next day. I tried using IS_AFTER and IS_BEFORE in an IF-AND formula but it is showing a blank result. NEED HELP BADLY. Thanks

52
J
L
Lauren_MadfisNew Participant
asked in Formulas
Date Formula Help!

Hello! I am trying to create a formula to help track tenure and extension dates for personnel. Each person has a “start date” – I want to have formula fields that calculate quarterly extensions +12 months; +15 months; +18 months Here is the formula I entered: DATEADD({Start Date},+12,‘months’) but I am getting an error

418
kuovonne
F
Franck_pasquesNew Participant
asked in Formulas
Dateadd Formula date and hours

Hello, I have two fields with two different formulas: Field 1:DATEADD({Date morning Start},{Number of days},‘d’) Field 2: DATEADD({Date am Start},‘4’,‘hours’) I would like to have only one field grouping these two formulas Also, I have a third formula field but the formula doesn’t work on either the date or the time: If({field date}, 09:00, ‘true’, ‘false’) Thank you for your answer

268
F
J
Jin_yi_Liu_FuNew Participant
posted in Formulas
Duplicate values to only show once for a specific column

From my screen shot below, you can see I have the column Company. In this column it has duplicated values, so I was wondering if there was a way for it to only display once using a formula in a new column? e.g. in the count column to only show The good company once. Also would be great if this process is automated, as I am using integromat to keep insert new records.

193
ScottWorld
C
Coral_FallonNew Participant
posted in Formulas
Need Formula to Make my Base work!

I’m currently working on a base to track our inbound containers. We’ve had a simple one for a while where we will update the movement of our shipping containers, and then manually update the ETA, based mostly off guesswork. I’m looking to automate the ETA updates, based off when a container was last seen and where it was last seen, using the average number of days it takes to get from place to place. For some context: we have 5 different shipping routes spread across 3 different warehouses. These all have a different number of steps to get through before they reach our warehouse. The way I’ve designed it thus far (with the help of someone in a forum, thank you!), is to have the following tabs: Containers ETA Calculation Paths (Routes) The container tab has generic information about the containers we need to track. I have it linked to the Path tab to show which Port it will need to go through. I also have a “Last Activity” as a date field and “Last Known Movement” as a single field drop

31
V
A
Andrew_StrotheiNew Participant
posted in Formulas
Looking To Combine Data From Fields In Different Columns

This is not actually the context of what I’ll be using this for but rather than explaining what we do, I figure it’d be better to compare it to the old math question which is… If I have someone that has 5 shirts, 5 pairs of pants, and 5 pairs of shoes we need to cleanly display all the options and print them into different fields. I’m wondering if this is doable, if so, what would my setup need to look like assuming the columns I’d start with in this example would be shirts, pants, and shoes? Thanks in advance!

52
Ben_Young1
L
Lost-MerovingiaInspiring
asked in Formulas
Display Month Number & Month Name

I have a table which has a Payout Month Column, which is a formula: DATETIME_FORMAT({Check-in date}, 'MMMM') How can I add the month number in front of the Month word (ie: 1 Jan)?

2527
M
N
nilay_gunayNew Participant
asked in Formulas
Linking two same data from two different base to one base

Hello all, I’m trying to figure it out for weeks to solve this problem. Base A : I want to get address data (zip code) + buying rate from this base Base B : I want to get address data (zip code) + selling rate from this base Base C: When I write that specific address/zip code and it matches with the address in Base A and Base B, I want to see buying rate and selling rate in separate columns automatically. So it is like; zip code base A + zip code base B = buying rate base A + selling rate base B in base C That would be great if you could help me to solve this issue because I’m about to lose my mind :slightly_smiling_face: What is the right way to do it? Is it by a formula? Is it by something else? Please help me. Thank you.

183
N
T
Thomas_GiegericNew Participant
asked in Formulas
IF Date is before 4380 days ago

Hey there I still don’t get AirTable to the fullest and would appreciate some help. I got a database with Birth Dates and managed to find out peoples ages with the TONOW formula. I would want a second row telling me wether the Date filed is more then 4380 days ago or not. I tried something like IF(Date > 4380 days ago , “True”, “Not True”) As this does not work - what can I do instead? Thx for any hint.

31
V
S
Sean_ChristenseKnown Participant
posted in Formulas
Formula to display duration in days and months

Hi! I am working on a contract template, where one of the dynamic fields pulled from AT is the duration of the contract, expressed in the following manner: The initial term of this Agreement shall be for a period of 2 months and 5 days, commencing on… I have a basic formula that allows me to extract an amount of time as a single term (1 month), but it’s not smart enough to say something like 1 month and 5 days. Basically, I don’t know how to add the remaining days. Here is my current formula: IF( {End Date & Time}="", "", IF( DATETIME_DIFF( {End Date & Time}, {Start Date & Time}, 'months' )=1, DATETIME_DIFF( {End Date & Time}, {Start Date & Time}, 'months' ) & " month", IF( DATETIME_DIFF( {End Date & Time}, {Start Date & Time}, 'months' )=0, IF( DATETIME_DIFF( {End Date &amp

594
S
L
Lou_QuinnNew Participant
posted in Formulas
If unique ID matches number in colum change staus to complete

Hello, I’m trying to work out a formula that When number of ID’s in ‘ID’ colum matches number in ‘how many in household’ change status of ‘Household’ to complete. I then plan on triggering a webhook which I think I understand how to do. Picture for reference included Many thanks, Lou

54
L
K
kxr1mNew Participant
posted in Formulas
Summary of records, need help

dear airtable community, i would like to create a formula that sums up the size of the identical item. in the screenshot you will see, that every record has only one size, even though its sometimes the identical sneaker. i want it to be: “dunk low disrupt 2 pale ivory black” : 39 EU - 8W US [case 1: only one record with the title - nothing should be changed, just display the available size] “dunk low light violet (venice)” : 38 EU - 7W US [case 2: multiple records with the same title, but with the identical size - only one record should include the size they all share and the others should be empty] “dunk low two tone grey” : 38 EU - 5.5Y US / 44 EU - 10 US [case 3: multiple records with the same title, but with different sizes - only one record should include all the sizes which are seperated with a " / " and the others should be empty. if anyone of you could help me with this challenge i would highly appreciate it, i hope i described the requirements understandable, if not feel

01
TheTimeSavingCo
R
Russ_KeitelParticipating Frequently
posted in Formulas
Need formula to duplicate field

Hi. Im brand new to AT. Im pretty good with Excel formulas but need to learn AT. I need a formula in “Prep Start” that will duplicate “Job Start” without the time. Thank you!

72
R
A
Aparajitha_SrirNew Participant
posted in Formulas
An aggregate formula on table B based on a column on table A

Hi team! I have a table A with a column called “Offer Amount”. I want to create a table B which will have a cell containing the max(tableA’s OfferAmount), that gets updated each time a new record is created in table A. Is this possible on airtable? The background is that I want to send the current highest Offer Amount (out of all the records on table A) to a webpage on builder.io via zapier (hopefully) each time a new record is submitted to table A. The records are inserted into Airtable table A from a form on builder.io via zapier.

264
A
A
Alysa_GiustinoNew Participant
asked in Formulas
Return Months from Start/End Dates

I have a list of tasks in my table and a date range for each task. The date ranges are entered into two different columns, Start Date and End Date. Each of those are formatted as dates. I’d like another column to list all the months that are included in that ranges of dates. So for example, if Start Date is Jan 1, 2020 and End Date is April 30, 2020, I’d like the Months column to list Jan 20, Feb 20, March 20, April 20. Is there a formula for this?

5212
kuovonne
M
Mathieu_PreauNew Participant
asked in Formulas
DATETIME_DIFF within an IF function

Hi there, I need to calculate the difference between the {Date} and today in days, and display “todo” or nothing if the number of days is >1. I tried this but I keep having errors : IF(DATETIME_DIFF({Date}, TODAY(), ‘days’)>1),“To do”, " ") Any ideas?

460
B
L
Liz_Baehr2New Participant
asked in Formulas
Formula to move word

I’m trying to workout a formula to identify a text field that starts with “The”, or “A”, and move the “The” or “A” to the end of the text string with a comma. So if the field said The Blue Book, I want it to say Blue Book, The Or at the very least I want to delete “The” or “A” from the start of the text string, but not from Elsewhere in the string. Can anyone point me in the right direction?

102
L
Scott_Gardner1
Scott_Gardner1Known Participant
posted in Formulas
Attachment url formula with button

Hello- I am looking to extract a specific file saved art image as “FINAL” when we have 10 plus revisions in one field/cell. Meaning the designers have revisions of art file attachments and I want to create a button based on the file type URL that only contains the word FINAL in the saved attachment. RIGHT(LEFT({ART FILES}, LEN({ART FILES}) - 1), LEN(LEFT({ART FILES}, LEN({ART FILES}) - 1)) - SEARCH(“https://”, {ART FILES}) + 1) How do I have it only find the word FINAL and create only the button URL link to that file only. Thanks! Scott

113
kuovonne
T
Tony_ParrNew Participant
posted in Formulas
Add a field that has numbers with commas

Hello! I have an automation that populates numbers separated by commas in the same entry. Example: 12, 12, 12, 8 I want to create a field that will tell me the sum of these numbers, essentially replacing the commas with +. When I do this using the SUM function, it returns 0 likely because of the commas. Is there an easy way of doing this?

22
kuovonne
C
Chris_DukesKnown Participant
posted in Formulas
Is there a function to remove trailing spaces + double spaces?

Basically, if a field has an extra space at the beginning or end from human error, I want to automatically remove it. Also, is there a way to flag a field that has extra spaces? Some kind of data validation when adding to the field?

703
kuovonne
J
Jade_PayneNew Participant
asked in Formulas
Need to see which group didn't have a representative attend a meeting

Hi there, I am creating a participation tracker that I can use for an upcoming project. The concept: There are 10 different groups, and each group can have 3-10 members. One “leadership” group contains at least one member from each of the other groups, so theoretically each member can belong to up to 2 groups. I have used a many-to-many relationship to link members to groups. Here is what I have: A table that lists all members with their group(s)(& other pertinent info) A table that lists all groups with their members (& other pertinent info) A table that lists all meetings with the members that attended and their group(s) as a lookup field A table that lists all emails and phone calls with the member they’re with and their group(s) as a lookup field What I need: identify how many times a group has participated in some way (meeting, email, or phone call) identify which groups did not attend each meeting identify how many meetings a group has attended A group “attends” a mee

82
J
T
Thomas_GiegericNew Participant
posted in Formulas
Formula to separate adults and minors in a list of people

Hey there i am pretty new to AirTable and its formulas - and I am not a programming genius. Is have a long list of people, with all kind of data. This includes the date of birth. I am trying to generate a daily number of people who are above or below 12 years of age. I played around with sorting lists a bit and managed to do 2 views - one with adults (born 4380 days ago or before that) and minors (born within the last 4380 days). Is there a more clever way? I would actually prefer to do a new table telling me how many of which I got in my list. Any help would be great. Thx.

124
V
A
Ava_SchmidtNew Participant
asked in Formulas
Change value at weekly intervals

Hello! I am trying to set up a timesheet in Airtable and I want a formula or automation to assign a new pay period every two weeks. I don’t care if it is just a number (pay period= 1,2,3,4…etc.) or a single select field or something else! I just need to indicate a change in the default entry every two weeks. My first thought was to set up an automation and set the trigger as a scheduled time (every two weeks). But, I keep hitting a roadblock when trying to set up the action. I do not want to FIND, UPDATE, or even CREATE records… I just want the default field entry for new records to change every two weeks. In my mind, the sentence would be “Every two weeks, add 1 to the pay period number.” Any thoughts on how to get this done?? Thanks to anyone in advance!

84
Alexey_Gusev
C
Cady_Smith1Participating Frequently
asked in Formulas
What formula do I need to extract this text?

I have a field called: Branch Name I need to extract text from it using a formula field. I’m just not sure how. Examples of data in the field: #2828 - Clarksville Branch - Clarksville, TN #4109 - Aiken Branch - Aiken, SC What I need to extract: Clarksville Branch Aiken Branch I tried this formula, but it doesn’t work (returns #ERROR!) and I don’t know enough about REGEX to troubleshoot: REGEX_MATCH({Branch Name}, '/(?<=-).*(?=-)/g')

140
Justin_Barrett

Badge winners

Show all badges
Terms & Conditions