Skip to main content

Formulas

Leverage this space to unlock the power of Airtable formulas.

  • 5,939 Topics
  • 18,228 Replies
5939 Topics
A
Adam_KramerKnown Participant
posted in Formulas
Count the amount of times a number appears in a column

Hi all, I have three columns: Member Number, Referrer, and Referrals. I am trying to count how many records contain the Member Number in Referrer, for the Referrals field. In other words, if I have 100 member records, and 5 records have “24” in the Referrer field, I would want the Referrals field in Member Number 24’s record to say 5 (the amount of times they reffered members). Does this make sense? Any ideas on how to do this? Thanks so much! Adam

5415
R
W
Weiyi_LiuNew Participant
posted in Formulas
Is there any functions like the function Large in Excel?

Hey here, I’m tring to find the second to the max value of a array, but i can’t find any functions like Large in Excel. Is there any way to implement this function? Thanks for your help

52
W
K
Keith_GreerNew Participant
posted in Formulas
Formula for Calculating six month from a specific date

Hope you can help. I have a table to record customers and products purchased. Each product comes with 6 months warranty. In the table I have a date of purchase column. I need another column that displays how many days of their 6 month warranty are left since the date of purchase. My question is: is there a formula that can do this? Many thanks in advance.

222
K
J
Jason_Friedlan2Participating Frequently
posted in Formulas
Datetime_Format Not Working for Time

I have a formula that is taking a Start Date Field with value 9/10/2022 2:30pm GMT I am trying to get the time of the event in 24 hour format using DATETIME_FORMAT({Start Date},“HH:MM”) The result is coming up 21 minutes short and giving me 14:09 vs. 14:30. Makes no sense.

335
J
A
Amir_BanihashemNew Participant
posted in Formulas
Fix Nominal Growth

I’m trying to create a forecast model where we start, for example at $1000 in period 1, which grows at $250 every period. The growth is cumulative, so I’m unsure how to create this.

31
Justin_Barrett
C
Carlos_Seguin-LInspiring
asked in Formulas
Read a file extension from Attachment filed to set its type in a different field

Hi team again, I need a quick hand, block with this atm In a table I have the following to fields File - Attachment : It could be an image, gif, video or audio Type - Multiple Select: Details de type of the image based on manually set options (image, gif, video or audio) Any ideas on how to set the Type based on File extension? If you’re wondering, the Type field will be used in a user facing filter to search by file types.

464
TheTimeSavingCo
WilliamPorter
WilliamPorterBrainy
asked in Formulas
How to get a blank result for Age when no DOB present?

I know how to test for the presence or absence of a value in another field, e.g. IF( MiddleName != BLANK().... But it does not seem to work with a field that should return a number result, when the parameter being fed to the formula is empty. Here’s what I think is the standard Airtable formula for an Age calculation: DATETIME_DIFF(TODAY(),DOB,'years') Works great so long as there is a date in the DOB field. If DOB is empty, on the other hand, this formula returns “NaN” (“Not a Number”). That’s ugly. So I tried IF(DOB!=BLANK(),DATETIME_DIFF(TODAY(),DOB,'years'),BLANK()) But get the same result. Apparently BLANK() isn’t a number value. This wouldn’t be a problem in FileMaker, but if it were, I could coerce the (empty) result to a (non) value of type Number. In FileMaker these are both valid expressions: GetAsDate("") GetAsNumber("") But Airtable doesn’t have a way to do this does it? Something like COERCE(BLANK(),Number) Is there another way to get rid of the “NaN” result in my Age fiel

268
WilliamPorter
J
J_CParticipating Frequently
posted in Formulas
Setup Weekly calculations based upon a particular plan

I am trying to set up Airtable to have an interface where my office staff can get the amounts they need for a meal plan change. We have multiple plans and each week the cost of the plan reduces by a set amount until the end of the change period. The amount reduced is different for each plan. When someone wants to change their plan we take the prorated amount of the original plan and deduct the prorated amount of the new plan. If the amount is positive then the customer is given a refund, but if the amount is negative the customer is charged the additional fee. In either case, there is a set fee for the change. If they want to cancel the prorated amount of the plan, plus the remaining balance of their point plan, minus the fee is calculated so it can be refunded. There are some cases where the fee would not be applied. My goal is to have an interface my staff can use to choose the current plan then choose the new plan and the interface would tell them how much needs to be charged or

34
TheTimeSavingCo
E
Elodie_DelneufInspiring
asked in Formulas
Insert black between concatenated values

Hello :wave:t3: I get concatenated values from a roll-up. The result is: CotisationsONIRCP I want this: Cotisations ONI RCP (with black in between each value) I tried using this formula but it does not change anything: CONCATENATE(values)&SUBSTITUTE(“”," ") Any advice? Thanks!

91
TheTimeSavingCo
Shawntel_Cote
Shawntel_CoteNew Participant
posted in Formulas
Exclude starting zero's from the middle of a value

hi! I have a field that is manually entered and I’m trying to create a formula field that extracts the middle number (found between “-” and “.”) but excludes the starting zero’s. This middle number can range from 1 to 999. I’ve tried a variety of Right/Left & MID, but I can’t seem to exclude the starting zero’s. Examples: 123456-002.5 → “2” 154773-025.26 -->“26” 89347-031.124 → “31”

273
Ben_Young1
J
John_MParticipating Frequently
posted in Formulas
Duration in Hrs/Mins between time stamps, minus off hours/weekends

Here’s what I need, if someone would be so kind as to assist… I have a Submitted at column which tags the date/time the record was created from a form entry. I have a Completed column which tags the date/timestamp when the Completed box is checked, which concludes the job duration. And I have a Duration column which has the following formula: DATETIME_DIFF({Completed},{Submitted at},'minutes')/60 We work 7am-4pm, M-F… What I need is that duration field to exclude off hours, before 7am, and after 4pm, to get a true measure of the work hours that job took. Any help would be greatly appreciated!

10
J
G
Guido_CappaInspiring
asked in Formulas
Search formula if records match

Hello, I am trying to build a simple automation using Make.com (Integromat) when a form is submitted to Webflow I need to create, search and update a record. This is the workflow: I have 2 forms on my webflow website: Form 1 gets email address and IP address → Create a new record Form 2 gets a few checkboxes and IP address → Search for records that matches the IP address and update the record with the checkboxes I tried different formulas but the issue I am having is that when Form 2 is submitted all the records on my table are updated. I hope somebody can help me. Thank you. Here some screenshots from my automation.

1005
ScottWorld
J
Javi_ChaconNew Participant
asked in Formulas
Gmail signature isn't shown when using a mailto URL with button fields

Hi mates, I’m using a field button to open a gmail mailto URL. Everything is working well (I’m adding fields, subject, email and body) except the fact that gmail doesn’t add my signature by default as always. Is anyone facing the same problem? Thanks in advance,

433
TheTimeSavingCo
S
Scott_HedgesNew Participant
posted in Formulas
Formula to Copy into Multiple Other Fields

I have two Tables, each have a Primary Field of Date. Table 1 has a row for each day of the month. Table 2 has multiple rows for each day of the month. My Challenge - How do I take a single field’s data in Table 1 and apply/copy it to a field in Table 2 for each row that has the same date? For example, in Table 1 I have a column called Invoices Processed. I want to take that number of Invoices Processed from July 15 in Table 1 and copy it to each field in Table 2 with the date of July 15. Please Help!

454
S
S
Sarah_MacGregorNew Participant
asked in Formulas
If current formula field returns anything, then show specific text, otherwise, display nothing

I have a pretty complex string of nested IF statements that appear as bulleted items. If the string returns anything at all, I need it to display with a title at the top “Pending Items:”. If it returns nothing at all, the title should also not appear. There is no way to add the current field title into it’s own formula, so I’m not quite sure how to approach this.

122
S
D
David_MacDouga1New Participant
posted in Formulas
Holiday String for Workday function

Hi everyone, I have a Start Date(calendar) and End Date(calendar) I want to be able to generate the required ISO date string for all the dates inclusive of the Start and End Dates, such that it can be inserted into the [HOLIDAYS] option of the WORKDAY function. Initially I thought this would be as simple as finding a formula function, something like DateBetween() or DateRange() but alas, all I can find is some seriously huge formulas (1000’s of lines long) which don’t quite work and are impossible to edit without errors. Let’s say my Start Date is 25 July 2022 and End Date is 27 July 2022. What is the simplest way to get a string like this ‘2022-07-25, 2022-07-26, 2022-07-27’ Apologies if this has been asked before, but I couldn’t find the answer so happy to be directed to an already answered question if it exists. Thanks

547
D
K
Kristin_Turner1New Participant
posted in Formulas
If QTY Column of Part A = 1,000, then correct

Is there a way for me to Reference the Summary bar that is built into Airtable in another column with a formula? I know AirTable doesn’t have normal sum function (which makes my job so much harder) but I didn’t know if I’m missing an option or an extension that can reference those Airtable built in summary bars? I want the formula to be something like: If column Part name = xxx-xxx-1315-xxx, then reference summary bar of column QTY used, then if that sum is equal to 1,000, then “Correct” if not “incorrect”. I need to be able to do this with quantities that match 4,6,500, and 1,000. I currently use a switch function to achieve something like this with a very basic formula, but if we receive part numbers with different lots and they don’t total to one of my set numbers, it looks like it’s incorrect (but it’s not-it just has different quantities that all collectively should total to 1,000) Furthermore, rolling up data in to a separate column will require me to have 8 different tables that

41
kuovonne
D
D_Boudewijn_AusNew Participant
posted in Formulas
Making a counter each time a cell is altered

As a pianist, I have a base with all the works of the composers I work on. I mark the date each time when I work on a score, so I can see later how much time (in days) has passed since I worked on it (using DATEDIFF). When I work on it again, I change the date. Is there a way to count the date-changes automatically, so that I can see how many times I work on a score? Each time I change the date, the number should go one higher. I tried with (IF(Uitvoeringsdatum<TODAY(),“”,Totaal+1) but that only works for the present day. (the next day the condition doen’t apply anymore) Any ideas?

85
R
K
Keehun_NamNew Participant
posted in Formulas
Reference name of the field in formula?

Hi AirTable community, I’m wondering if it’s possible to get the name of the field within a formula as a variable? Something like TODAY() but get the name of the column/field itself within the formula. Thank you!

101
kuovonne
J
Jeff_HaskinInspiring
posted in Formulas
Make an in-line histogram! Copy/paste this formula

I figured out how to make a histogram inline with the table and I thought I’d share: It was horribly tedious for me but it won’t be for you! This formula expects whole-number values between 0 and 100. Here’s the formula: CONCATENATE( SWITCH( ROUND({1}/100,2), 0.00,"_.", 0.01,"▁",0.02,"▁",0.03,"▁",0.04,"▁",0.05,"▁",0.06,"▁", 0.07,"▁",0.08,"▁",0.09,"▁",0.10,"▁",0.11,"▁",0.12,"▁", 0.13,"▂",0.14,"▂",0.15,"▂",0.16,"▂",0.17,"▂",0.18,"▂", 0.19,"▂",0.20,"▂",0.21,"▂",0.22,"▂",0.23,"▂",0.24,"▂", 0.25,"▃",0.26,"▃",0.27,"▃",0.28,"▃",0.29,"▃",0.30,"▃", 0.31,"▃",0.32,"▃",0.33,"▃",0.34,"▃",0.35,"▃",0.36,"▃", 0.37,"▄",0.38,"▄",0.39,"▄",0.40,"▄",0.41,"▄",0.42,"▄", 0.43,"▄",0.44,"▄",0.45,"▄",0.46,"▄",0.47,"▄",0.48,"▄",0.49,"▄", 0.50,"▅",0.51,"▅",0.52,"▅",0.53,"▅",0.54,"▅",0.55,"▅", 0.56,"▅",0.57,"▅",0.58,"▅",0.59,"▅",0.60,"▅",0.61,"▅",0.62,"▅", 0.63,"▆",0.64,"▆",0.65,"▆",0.66,"▆",0.67,"▆",0.68,"▆", 0.69,"▆",0.70,"▆",0.71,"▆",0.72,"▆",0.73,"▆",0.

446
O
E
EV_TRAININGNew Participant
asked in Formulas
IF, Then formula

I’m trying to write a formula that says. If {Inspection Date} is not blank and the number is greater than 730, then “No” or If {Inspection Date} is not blank and less than 730 then “Yes”. I keep getting hung up with the blank fields. This is my current formula: IF({Inspection Date Formula}!= BLANK(),AND({Inspection Date Formula}>730,“No”,“Yes”)). The output is now a 1 for NO and a 0 for Yes. Blank fields are still blank which is what I want. How do I get the 1 and 0 to say No and Yes?

233
kuovonne
P
PatakessNew Participant
asked in Formulas
Regex replace (or formula) find and delete multi strings

Hello ! I’m looking for a solution to find the different strings in one fret and erase them in another. For the moment it looks like this: REGEX_REPLACE(Concat, Del,"")) But it doesn’t work if in “Del” I put: 8hKitchen 9hRoom And in Concat 9hRoom 10hDiner 8hKitchen 11hBathroom I would like to delete: “8hKitchen” and “9hRoom” (but it can also be of other occurrence, more or less.) Someone would have any idea ? Thanks in advance

432
P
J
Jared_Wright1New Participant
posted in Formulas
Lookup / Rollup of Multi Selects field glitch

When I create a lookup field of a multi select field I get the new updated version seen here However after reading recommendations in this forum when I try and create and arrayunique() from the look up it is not successful. (See Pictures) I have found that If I convert the lookup to a rollup and back again the I get the result that I am looking for. (See Pictures) I have gone back and forth from Lookup to rollup and I get the same result every time. I get the feeling that this is some kind of glitch or bug. Please give me any feed back that you can. See video for better clarification VIDEO

2510
J
A
Ariana_GarbersNew Participant
asked in Formulas
Datetimediff producing weird values

I’ve made this datetimediff formula with two lookup values that are both formatted in the way of date with the same time zones. Lots of the values are accurate, but some values are very off… I’ve attached screenshots here for reference. The most_recent_sold_date is a lookup from a rollup value, but I would think it would still work in the formula.

41
kuovonne
Z
Zach_ConradKnown Participant
posted in Formulas
Progress bar in July 2022 AT Video

I’d LOVE to see the formula that Airtable used in their What’s New in Two July 2022 video. Specifically how they handled percentages over 100%. Very cool. https://www.youtube.com/watch?v=ktkizKuN5bU

114
Hannah_Wiginton

Badge winners

  • First Reply
    GegHhas earned the badge First Reply
  • 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
Show all badges
Terms & Conditions