Skip to main content

Formulas

Leverage this space to unlock the power of Airtable formulas.

  • 5,938 Topics
  • 18,225 Replies
5938 Topics
C
Cole_GoebelNew Participant
posted in Formulas
Need help with a Date formula

I’m trying to forecast revenue for my company. I have a table, and basically what I want it to do is tell me: If Order Date Created is “X”, and payment method is “Y”, add “Z days” to Order Date and output new date. Can’t quite figure it out. For example, if they paid with a credit card, I know their payment will settle today. But if they paid by check, I can forecast the revenue out 2 weeks or so. Anyone know how to do this?

44
C
N
Natasha_VashkoNew Participant
posted in Formulas
Formula for multiple columns into one column or a separate table

I have a table like in Screen Shot 1. I spent a few hours here looking for a solution on how to get a column like the green one in the screenshot or another table like in the second screenshot. Any hint would be greatly appreciated. Thanks in advance , N

742
J
M
MarlijnNew Participant
posted in Formulas
Using LEN() but excluding URLS in the tweet

Hello! Currently I am trying to create a formula field to check the length of a text if it is the right length for a tweet. But I want to exclude links in the text, as Twitter always takes 23 characters for a link. So the hyperlinks in my text don’t need to be counted. Can I make a formula to count only the text which is not part of the url?

103
kuovonne
H
Holly_WilliamsoNew Participant
posted in Formulas
Conditional Formatting not working for me

I have a field that lists jobs as “open,” “closed complete” and “closed incomplete”. I would like to calculate the number of days for open jobs since they’ve been submitted. Without conditional formatting, datetime_diff (today(), {date requested}, ‘days’ works. When I try to add in a condition it doesn’t. I’ve tried multiple variations of this, but nothing seems to work. =if({date requested} = “open”, ) (datetime_diff (today(), {date requested}, ‘days’ I’m sure it’s an additional/missing character issue, but if you can help, I’ll forever be in your debt.

72
H
J
Joselin_ManeNew Participant
posted in Formulas
Monthly Community / Membership Profit Calculator - Can it be done?

I want to find out if I can convert this Community Profit Calculator Google Sheet into an AirTable. is that possible? In the basic version simply input Membership rate Current Membership Size Membership Growth (monthly goal) Membership Churn Expenses You will then get a breakout of how much profit you will make per month. The Plus version I want to be able to allow multiple Membership based revenue options (I have counted 21!) I have other ideas on how to expand including being able to sell that individual Airtable via https://paytable.io/ A future idea includes being able to indicate how many members I want in 12 or X months in the future, then the output will divide the total by the number of months to reach goal and determine the monthly profits from there. Thoughts? Can it be done? If so, how? Thanks let me know if you need more info to answer question.

31
D
T
Tobias_LGKRKnown Participant
posted in Formulas
New Formula Formatting

Thank you Airtable team! I love the new Formular formatting you have implemented. It really helps, when writing longer and complex formulas!

85
ScottWorld
A
automadienInspiring
posted in Formulas
Formula to fetch information from a URL

Hello! We use this formula: =REGEXEXTRACT(IMPORTXML(A1,"/html/head/meta[@property=‘article:modified_time’]/@content"),"^(.+?)T") on Google Sheets to fetch publish date information from a URL (A1, in the formula). We would like to do something similar on AT but I can’t for the life of me figure out how AT would write this out as a formula. Any tips?

231
kuovonne
S
Son_NguyenParticipating Frequently
posted in Formulas
[BUG] Field Name with "@" will render all formulas fields useless

I can’t open my formula field. Every time I double-click on the field name, it just pops up a banner saying “An error has occurred” and forces me to refresh the page. This happened to my base after the formula formatting update today. Strangely this only happens to one table in my base. Other tables are working fine. I attached a video recording of my screen below. Any help will be much appreciated! Thanks guys. https://cln.sh/PpmNkX

168
C
J
J_W1Known Participant
posted in Formulas
Spintax possible?

Any way to use spintax in formulas to create concatenation texts that are a bit more dynamic?

20
J
M
maichel_peppelsNew Participant
posted in Formulas
Calculate a Minimum Sample Size for an experiment

Hi all, i am trying to reproduce an exel formula that calculates the Minimum Sample Size based on some values in Airtable. the formula in excel is like this: =IF(AB2;ROUNDUP((1,644853625*(2I2(1-I2))^0,5+0,8416212327*(I2*(1-I2)+(I2+(I2AC2))(1-I2-(I2AC2)))^0,5)^2/((I2AC2)^2);0)*K2;0,1) Reference: AB2 = value from call (TRUE/FALSE) I2 = 10% (value from cell) AC2 = 20% (value from cell) K2 = 2 (value from sell) I am stuck with the use of “^” (power) any suggestions? Thanks in advance. Regards Maichel

13
D
C
Connor_ForsythNew Participant
posted in Formulas
Date list is before date

Hi there! I’m looking to calculate the growth rate of attendees for events that I run. Specifically, I’d like to understand how many attendees are first timers. To spell out how this formula could work… I see x variables Attendee Attendee attended event date list Event Event dates I’d like a sum of how many attendees attended this event if it was their first time (had not attended events prior. if ( {Attendee} has NOT(BEFORE({attended event date list}, {Event dates})) This is not really working… One of the problems is that the rollup of events attended is just a list of dates, it’s not running through the formula one by one with the attendees. Any advice on how this could work? I have two linked databases with Attendees and events.

73
J
G
Gamikey_StoreNew Participant
posted in Formulas
How can I count the number of occurrences of a character in a paragraph?

How can I count the number of occurrences of a character in a paragraph? Example: I want to check the number of occurrences of commas in a paragraph: “182-DISPLU6-Profile 3-disneytest04@gmail.com, 161-DISPLU6-Profile 1-disneytest01@gmail.com, 162-DISPLU6-Profile 2-disneytest02@gmail.com” I want the return result is 2

321
kuovonne
A
Alyssa_BuchthalInspiring
asked in Formulas
DATETIME_PARSE broken?

I had a formula that was working until this week and has now broken for some reason. I looked into the basics (did the field names for the formula change, synced fields change, anything like that) but literally nothing about the components of the formula broke, so I’m unsure why it’s now resulting in #ERROR rather than the result. Formula below, it’s honestly incredibly basic. Any ideas for simple things I could be missing? DATETIME_PARSE(ARRAYJOIN({Release Date_static}), ‘MM-DD-YYYY’) Where {Release Date_static} is a lookup of a single line text field with a date in ‘YYYY-MM-DD’ format.

51
A
S
Sarah_InmanNew Participant
posted in Formulas
How to create all possible pair combinations without duplicates

Hi! Anyone have any solutions on how to create a formula that would allow me to input a list of say 10 people and then create all possible pair combinations without creating duplicates? Example: Name 1 Name 2 Name 3 Name 4 Name 5 Then I would want to create 10 groupings where each name is paired with each other at least once without duplicating. I’ve tried using autonumber, but can’t seem to figure out the formula to create this.

90
S
S
Seb_BlandinNew Participant
posted in Formulas
How to extract numbers operation from a string?

Hello everyone, I receive in a single text field this kind of data: +1 * 1+0 * 2+0 * 3+0 * 4+0 * 5+0 * 6+0 * 7+0 * 8+0 * 9+0 * 10 or +1 * 4+1 * 5+0 * 6+0 * 7 This is the number of kilos that needs to be sent in the appropriate package. I wonder how I could retrieve the 1*1 from the first line, or, for the second line, 1 * 4 and 1 * 5 I was thinking about some regex expression but my knowledge is poor in that field. Anyone would have an advice? Thanks in advance

10
S
M
Michael1Known Participant
asked in Formulas
Decimal number with two decimal places (2.45) to hours (2:45h)

How can I replace decimal number with two decimal places to hourly like number. 2.42 > 2:42h

103
M
S
Simon_ByNew Participant
posted in Formulas
Prefill Form Formula

Hi Trying to get a prepopulated airtable form URL to prefill a certain field, per this link. The problem is that the form is generating a new record, not updating the old one. Do I need to use a separate sheet for the submissions, is this causing the issue? The formula used is CONCATENATE('https://airtable.com/shrxxxxxxxx?’, ‘prefill_StatusC=’,{Record ID}) Record ID = formula field = RECORD_ID() StatusC = Single select field which is also included in the form

61
M
M
Michael1Known Participant
posted in Formulas
Collect ratings for records from multiple colabolators

Hey guys, So I have a freight-orientated base, where I record all loads driven by particular collaborators (drivers). So that means I have a list of facilities and warehouses where those drivers are delivering particular loads to. So, I would like to give drivers the opportunity to rate those facilities, to write comments for that particular facility/warehouse and choose some predefined options from “Multiple select” fields. Also, I would like drivers to have the option to fill their loading/unloading time for that particular facility/warehouse. And all that I mentioned above I want to show as average numbers in my base, so later on drivers can see average rating for that facility, and their previous ratings. Is that even doable? Thank you

51
M
M
Matt_Kennedy1Known Participant
posted in Formulas
Access column name in a formula

Let’s say I have a multi-select column called “Colors.” And I have a record with the Colors value: Red, Yellow, Blue I’d like to create a new column called “Tags” with the string: “Color: Red, Color: Yellow, Color: Blue” Is it possible to access the column name in a formula?

10
M
J
J_W1Known Participant
asked in Formulas
Changing multiple select options via formula

Getting into formula’s a bit more as a novice lately, but struggling to get this one right. Anyone able to help me out on this one? What I want is to change the Multiple Select Field as per below. small instead of s average instead of a tall instead of t Was thinking in the direction of wrapping the exiting formula in a Switch( ) to change the select options small, average and tall to s, a and t for the the output.

142
J
J
J_W1Known Participant
asked in Formulas
Formatting numbers like 1.7k to 1700, 1M to 1000000 etc

Anyone can help me make a formula to write out the below numbers in a Single Line text field (A) that are styled like below, to full digits in a Formula Field (B)? Field A 1 10 100 1k 10k 100k 1.1k 10.1k 100.1k 1M 1.1M Etc. So far I’ve come up to this formula. But it’s very lacking. SUBSTITUTE( Views, REGEX_EXTRACT({Views}, "\.\d+k"), "00") It outputs 100 when its 1.7k and doesn’t work at all for entries like 1 to 999 and 1M upwards.

72
J
L
LaTrelle_GomezNew Participant
posted in Formulas
I need to add a number of weekdays only to generate an end or due date

I am looking to take an integer and add the number to a start date. I need to have weekends excluded from the date add. Please see the formula I am currently using that isn’t generating the result I need: IF({Workflow}=“Manufacturing”, DATEADD( DATEADD({Start Date}, {Estimated Production Days}, ‘days’), SWITCH( WEEKDAY( DATEADD({Start Date}, {Estimated Production Days}, ‘days’) ), 0, -1, 1, -2, 0 ), ‘days’ ))

61
M
T
Tiffany_OneyNew Participant
posted in Formulas
Sorting by chronological order

I have a formula that groups together transactions within each month. Formula is: DATETIME_FORMAT({Pickup Date}, ‘MMMM’) I need to sort the months in chronological order instead of alphabetical. Is there a concatenation or formula that will return this result?

101
M
M
Michelle-TNew Participant
posted in Formulas
Calculating the last day of the month for each quarter

I have a “Next Year End” date field for each of our clients and then I have fields for each of the 4 quarters to populate dates in various other tables. I am trying to create a formula that will take automatically choose the last day of the month when I use this formula: DATEADD({Next Year End}, 6, “month”) - it basically brings me to October 30th, not October 31st. Does anyone know what I’m missing? I’ve been looking everywhere in here…It doesn’t seem like this should be so difficult unless I’m totally not understanding :slightly_smiling_face: Thanks in advance!

161
M
W
Washington_CentNew Participant
posted in Formulas
IF Formula for Budgeting

I am reconfiguring my budget spreadsheet and am having trouble with an IF formula. I want to set up the base so that I can select if something is revenue or an expense, enter the dollar amount, and then in the next column over (Debits and Credits), it will automatically add a (-) negative to any item that is marked as an expense. Here is the formula I was using: IF({Trans Type}=“Expense”,{Transaction Amount}*-1)&IF({Trans Type}=“Revenue”,{Transaction Amount}*1) The formula is messing things up somehow because it won’t recognize the output as a number, and therefore will not let me format it as currency. Instead, it says: Your result type is not a number or a date. Formatting options are currently only available if your result type is a number or a date. I want this column to be formatted as currency and show a running total. Can someone help?

01
Ben_Young1

Badge winners

Show all badges
Terms & Conditions