Skip to main content

Formulas

Leverage this space to unlock the power of Airtable formulas.

  • 5,940 Topics
  • 18,233 Replies
5940 Topics
K
Kyle_HenryNew Participant
posted in Formulas
Replace %20 with Hyphen using ENCODE_URL_COMPONENT

Hi, I’m trying to replace %20 with hyphens in my Zillow URL formula. How do I do this? Thanks for your help. Example: https://www.zillow.com/homes/123-Main-St-Macon,-GA-31217_rb

22
Justin_Barrett
S
Sandra_MayernikNew Participant
asked in Formulas
Calculate a number based on selected single select option

I need to calculate a number based on selected single select option in another field. For instance if the users chooses “Excellent” then the number should be 10, if the users chooses “Good”, then the number shoud be 7, if the users chooses “Fair”, then the number should 5, if the users chooses “Poor”, then the number should be 3, other wise it should be blank. It so that I can then tally the score of nine different fields for a total score. Suggestions?? Formula? I’m fairly new to Airtable and I’ve trid numerous formulas toget this to happen an so far have failed.

265
Justin_Barrett
I
Ian_PlotnerNew Participant
posted in Formulas
=ROW() equivalent or workaround?

Hi Basically, when a row is reordered in a view, I need to update the “Last Modified” field. Since it does not update on its own, I want to use something similar to =ROW() so that the record is automatically modified with the new row number and thus the Last Modified field will also update. That’s the best way I can think to do it but I’m happy to hear other ideas!

11
kuovonne
Y
Yasutaka_ItoNew Participant
posted in Formulas
BLANK() should return true ONLY for the actual "blank" situation

Currently, BLANK() function returns true even for the cell where the value is 0 (zero). It will be more useful if the BLANK() function returned true strictly if the cell is empty.

11
Justin_Barrett
C
CJBNew Participant
asked in Formulas
Formatting Phone Number in formula

Hi - I created a column with an IF statement that pulls phone numbers entered from a survey into the field IF we haven’t recorded the phone number ourselves manually (the survey column is called Phone Number and the manual column is Phone # to differentiate). The formula is IF({Phone Number}, {Phone Number}, {Phone #}) When the numbers are pulled, some of the formats are in the form that the client filled out XXX.XXX.XXXX but we want it in (XXX) XXX-XXXX. Is there a way to add formatting to the formula to force the phone number to be in the format we want?

872
Justin_Barrett
J
Jayme_RichardsoKnown Participant
asked in Formulas
Calculating the most recent date out of three date fields

Hi! In my base I have 3 fields where various dates are entered, based on when a client was contacted. Basically a first, second, and third touch point in communication. I have a fourth field that currently pulls the most recent date based on what’s entered in the other 3 columns. If only the first contact field has a date, then the most recent outreach is that date. If the second contact point has a date, then it assumes that date is most recent. Same with the third contact date… if anything is entered into that field, it assumes it’s the most recent. My issue is that sometimes, 6 months to a year down the road, the contact points may start over, and that first date may become the most recent date. (But because there are still dates entered into the other two fields, the 4th field assumes they are still the most recent, when they aren’t) Is there a way for it to calculate within the system which of the three dates is actually the most recent date? I hope that makes sense!

765
Y
D
DekoInspiring
asked in Formulas
Having trouble getting my formula to work (prefill form)

So I watched an excellent Youtube video and figured out how a user can edit table data with a form. However, I got the Record ID to prefill, but I can seem to get the others working. Here is the code. It’s a mess. Hard to write it in since a tiny box. CONCATENATE({Form URL},'?prefill_Parent%20Record%20ID=',{Record ID},'&prefill_Doll%20Complany=',{Doll Company},'&prefill_Company%20Logo=',{Company Logo},'&prefill_Country=',{Country},'&prefill_Status=',{Status},'&prefill_Doll%20Lines=',{Doll Lines},'&prefill_Sub%20Lines=',{Sub Lines},'&prefill_Resin%20Colors=',{Resin Colors},'&prefill_Will%20they%20custom%20color%20resin?=',{Will they custom color resin?},'&prefill_Official%20Webpage=',{Official Website},'&prefill_Official%20Taobao%20Store=',{Official Taobao Store},'&prefill_Official%20Measurements=',{Official Measurements},'&prefill_Skin%20Color%20Comparison=',{Skin Color Comparison},'&prefill_Notes=',{Notes}) Company Logo is an attach

474
D
P
Parke_BrownNew Participant
asked in Formulas
Help Correcting Nested IFAND Statement

After searching here and looking at the nested ifs reference, I believe I’m close, but not quite there. I want the formula to look at values in two fields (likelihood of a problem; severity of a problem) and return a third value (combined level) depending on the combination. A sample of part of it is below. Example formula so far: IF( AND({Likelihood} = “Improbable”, AND({Severity} = “Acceptable”)), “Low”, IF( AND({Likelihood} = “Improbable”, AND({Severity} = “Tolerable”)), “Medium”, IF( AND({Likelihood} = “Improbable”, AND({Severity} = “Undesirable”)), “Medium”, IF( AND({Likelihood} = “Improbable”, AND({Severity} = “Intolerable”)), “High” ) ) ) )

32
P
M
Michelle_SokolNew Participant
asked in Formulas
Formatting field with inconsistent phone number format

I’m pretty new to Airtable & formulas. Was hoping someone could help me find a solution to cleaning up data for a phone field. I’m pulling data from multiple sources and while most numbers are formatted consistently, there are a few outliers I want to be sure to account for. Most common format: Dirct: XXX-XXX-XXXX A decent number also have an extension on the end in the format of " ext. X" , which are always one character. Some other formats that throw things off: There are just a few that are entered as (XXX) XXX-XXXX A handful of others that are Cell: XXX-XXX-XXXX And a small percentage that include multiple phone numbers as Dirct: XXX-XXX-XXXX, Dirct: XXX-XXX-XXXX My goal: If there are two phone numbers listed, I’d like to move the second one into a new column for an additional phone field. My guess is to parse this by comma somehow, but again – new to Airtable and having trouble figuring this out! I’d also like to remove the cell: or dirct: in front of all numbers. They can rem

401
M
A
Anna_AllinghamNew Participant
asked in Formulas
Convert Google Analytics Hour of Day

I have imported our Google Analytics data through AirFlow and would like to convert the Hour of Day to the correct format. The way GA has it formatted is “2021070100”, which is 7/1/2021 at 12:00 AM. Is there a formula I can use to get the desired outcome? Thanks!

62
A
T
Travis_Groom1Participating Frequently
posted in Formulas
Nesting Multiple IF statements in a formula

Hi everyone, Trying to write a multiple If statement formula to return in what quarter something took place. Example: IF( AND( IS_AFTER( {Training Start Date}, ‘2020-03-29’ ), IS_BEFORE( {Training Start Date}, ‘2020-07-04’ ) ), “FY21Q1” , This formula works fine, but when I try to add more modifiers for each quarter, it doesn’t seem to work. IF( AND( IS_AFTER( {Training Start Date}, ‘2020-03-29’ ), IS_BEFORE( {Training Start Date}, ‘2020-07-04’ ) ), “FY21Q1” , IF( AND( IS_AFTER( {Training Start Date}, ‘2020-07-05’ ), IS_BEFORE( {Training Start Date}, ‘2020-10-03’ ) ), “FY21Q2” ) IF( AND( IS_AFTER( {Training Start Date}, ‘2020-02020-10-04’ ), IS_BEFORE( {Training Start Date}, ‘2021-01-02’ ) ), “FY21Q3” ) IF( AND( IS_AFTER( {Training Start Date}, ‘2021-01-03’ ), IS_BEFORE( {Training Start Date}, ‘2021-04-21’ ) ), “FY21Q4” ) IF( AND( IS_AFTER( {Training Start Date}, ‘2021-04-04’ ), IS_BEFORE( {Training Start Date}, ‘2021-07-03’ ) ), “FY22Q1” ) Hopefully, someone can see what I am missing

52
T
D
Dennis_PetrouKnown Participant
asked in Formulas
Character Count with symbol

Hello! I am using LEN({Long Text}) to count the number of characters in a text field. I also see how to use this to display a symbol depending on if its under or over: IF(LEN({Long Text})>280, “ :x: ”, “ :white_check_mark: ”) How do I combine these in one field? So it would show the character count, and also the symbol for over or under? Thanks for any help!

52
D
A
Adnan_HalilovicNew Participant
posted in Formulas
Number of working days between two dates (without weekends)

Hello, I’m trying to solve the following problem: I have created a table which has 2 columns {DATE FROM} and {DATE TO}}. The third one I have is called {WORKING DAYS}, so that one should show a number of working days between selected dates (from and to). The problem I’m having is that I cannot calculate number of working days between them (without weekends), I can just get all days, which is not useful for me in this case… Is there any chance to solve it? WORKDAY() function doesn’t help, since it just creates a due date when I enter parameters. I could solve my issue, if there are for loop, for each, or any other loop functions in the functions menu. Thank you

18612
J
N
Nicola_PilcherNew Participant
asked in Formulas
Static date function in IF()

Hi All, I am trying to get a function to enter the current date when I change the status to done but I want the date to stay static and I cant figure out what date function to use for this. Here is what I have: IF(Status=“Done”, DATETIME_FORMAT(TODAY(),“DD/MM/YYYY”), BLANK()) Now when I changed one item to “Done” yesterday it put in yesterdays date but when I logged in today it had todays date instead. I figured this would happen but don’t know what to put where I have “TODAY()” to get this to work. Can anyone help me with this?

204
N
W
Wayne_MerlinoInspiring
posted in Formulas
Date Conversion for Concatenate

Good Morning AT Community, Would love some help on how to convert the date in the primary column (which is named Interactions in the screenshot). I am attempting to combine the name of the contact, type of interaction, and the date of the interaction into the name of the primary field. I get how to use Concatenate how to do this, but have tried every conceivable DATE_FORMAT method I can think of, and nothing gets me what I want - which is for the date to match the “When” field. Thanks in advance.

44
W
F
Federico_PoliscNew Participant
posted in Formulas
Name a field in relation to another field in the same table with a formula

Dear All, I am quite new user of Airtable and I am trying to create a database for my geology laboratory. In short, we make microscope slides named Thin Sections (hereafter I will call them “TS”) from geology samples (hereafter “samples”). From one sample, one can obtain more than 1 TS at the end of the manufacturing process. However, the user knows since the beginning if he/she wants one, two, or three (and so on) TS from his/her own samples. I have thus created a table with the following fields (see attached image): “Sample name”, “Site name” (the name of the site where the sample has been collected), “total TS” (that refers to the number of TS on aims to obtain), “TS names” (since each TS has its specific name at the end of the making process). In the case more than one TS will be produced, I would fill automatically the “TS name” field as it follows: e.g. → (Sample name)_1, (Sample name)2, (Sample name) 3…and so on, in relation to the number of TS he/she wants that he/she wrote i

00
F
K
Kristine_GuzmanNew Participant
posted in Formulas
Calculate days within date range, but returning a 1 if start and end is the same date

Hi Everyone, I’m trying to figure out a formula and am wondering if anyone here might know the answer: I’m trying to calculate the days between a Start Date and End Date, but if the Start and End Date is the same, I want that to = 1. I’m using the DATETIME_DIFF formula, but when the Start/End is the same, it returns a 0 when I want a 1. Thank you!

62
Justin_Barrett
M
Marc_LoskillNew Participant
asked in Formulas
Formula for "only next date"

I made a short video to explain my challenge: https://vimeo.com/575868703/4099a1186a Basically, I’d like to create a formula, so I get only the coming date within a list of dates. Hope someone can help me with this. Thank you guys!

354
kuovonne
M
Matthew_DuhamelNew Participant
posted in Formulas
Math on an array (rollup field)

I have two fields - a rollup field with 3 integer values and a currency field with a single value. I want to divide the currency field by each value in the array to, hopefully, get a new array that includes the output of each calculation. This seems like an obvious problem to do on an array but I cannot find the formula to perform the task. Can someone point me in the right direction? I’m brute forcing this and it sucks.

934
M
A
Alba_Machado2Known Participant
posted in Formulas
Duplicate Record WithOUT Duplicate Inventory Number

Hi old Airtable friends, It’s been a while. I’ve been using a formula that’s worked up until now: IF([Book Number - Old}, {Book Number - Old}, “BN” & {Autonumber}) But now I see that when a book record is duplicated, the same old book number is duplicated right along with it and we need each and every book in our inventory to have a unique inventory number. Any ideas? Please and TIA. :slightly_smiling_face:

43
Justin_Barrett
S
Simone_NabholzNew Participant
asked in Formulas
Formula to calculate Cost of Goods

I want to create a table to calculate my cost of goods, for products that I make. I have a table with the list of products and their RRP. Another table with a list of expenses, ie labour cost, packaging cost, materials cost etc I want to have a third table that takes (product RRP) - (total costs) = profit For example (candle $59) - (wax $2, vessel $5, packaging $3, labour $10) = $39 I’m very new to tables and formulas, so it may be easy and obvious. Any help would be appreciated!

342
S
M
Mariah_GregoryNew Participant
posted in Formulas
Rollup or Formula to take FIRST record added to a Linked Record Field

Hello! So I’ve found a formula before for pulling the most recent record added to a Linked Record Field, but I now find myself needing to pull out the FIRST record added. Basically, I have a Market Research base with a Table of Titles. There is a field there called “Seen at” that links to second Table of Events, showing every time we’ve seen that title exhibited. I want to add a rollup or formula that takes the linked record that was the first added to that field and puts it in a new field called “Discoverd at.” Anyone have any ideas on how to do this?

583
Kamille_Parks11
H
Haydn_Sonnad1New Participant
asked in Formulas
Restrict LOOKUP to record with max value in field

Hello! I’m looking for a way to restrict a lookup to only the record with the max date in a date field. Context: Managing a vehicle fleet Bases: Registration VIN Desired outcome Only display the license plate that is associated with the registration record that has the max “license plate exp date” value. I’m not sure how to go about this. Seems like there may be a way to do so by creating a view in the registration base that only shows the records with the most recent restricting the lookup to that view but can’t figure out the first step there. Any help appreciated.

111
ScottWorld
J
Jeffrey_BrandtParticipating Frequently
asked in Formulas
Perform a search off of a rollup?

Hi, I have a rollup field (called “Lessons Included”) that won’t work in the following formula: IF(AND(FIND(“Math”, {Lessons Included}), {Submit Date}=BLANK()), “Math Pending”, IF({Submit Date} = BLANK(), “Math Not Included”, DATETIME_DIFF( {Materials Due}, {Submit Date}, ‘days’))) The FIND doesn’t work, and all fields where Submit Date is blank return Math Not Included, even if Math is part of the rollup field. Any ideas on a workaround for this? UPDATE: If I create a helper lookup column that includes ONLY “Math”, I can get the formula to work. However, with all the lesson types included, that would make my sheet much larger than it currently is.

112
J
C
Corentin_MetgyNew Participant
posted in Formulas
Combining informations from 2 fields, need help please ! :)

Hello, I’ve integrated Airtable with Pandadoc. Each time a client signs a quote, the information lands in airtable so that we can prepare the order to be shipped to the client. However, the order information we receive is split in 2 columns : On the left are the product references and on the right the order quantities for each reference. Is there a way to use ROLLUP or FORMULAS in order to have something that is more readable. For example : V-1C * 1 CS-1C-24M * 1 CS-6C-24M *1 Even better would be to be able to display only the references we want. For example, if I want to display only the V-1C orders, I’d have only V-1C * 1 in the resulting field. All we need is to easily understand the order in order to prepare it and ship it to the client. Thanks a lot for your help !

72
C

Badge winners

Show all badges
Terms & Conditions