Skip to main content

Formulas

Leverage this space to unlock the power of Airtable formulas.

  • 5,939 Topics
  • 18,225 Replies
5939 Topics
L
Lauren_NoackNew Participant
posted in Formulas
Holiday Rollup Field Not Working in WORKDAY Formula

I have a list of Holiday dates in a rollup field linked to another table. I tried to add that rollup list of dates to my WORKDAY Formula, but it’s producing an error. If I copy/paste the string of dates between ‘single brackets’ the formula works perfectly but I can’t surround the Holiday field in single brackets. Here is what I currently have: IF({OE Date},DATETIME_FORMAT(IF({Deposit Hard Date}, {Deposit Hard Date},WORKDAY(DATEADD({OE Date}, {Deposit Due (Days)}-1,'days'),1,Holidays)),'MM/DD/YY'))

30
L
B
Blake_EskinNew Participant
asked in Formulas
Displaying most recent date in another table

I’ve used Airtable before, but never set up my own base before, so pretty new to this. I have two tables Students Daily Submissions Students is a fixed list of enrolled students that is linked to Daily Submissions Daily Submissions is fed by a form that collects a student (selected from a menu from Students), a link, and a description. When someone submits a form, it also collects Created Time in a field called timestamp. In Students, I want to create a field called Most Recent Share that will display the date of the most recent Daily Submission for each student. After reading Rollup field overview and topics including Formula to calculate the latest Date in an array and Roll up max date but show another field I tried creating a Rollup that collects ARRAYUNIQUE(values), which showed nothing, and MAX(ARRAYUNIQUE(values)), which showed a column of zeroes But I don’t know which of those is correct, or what the Formula (or is it Lookup?) in the Most Recent Share field should be, and h

804
B
A
AM858Participating Frequently
posted in Formulas
Using Regex to find first instance of record IDs in array Lookup

I am using a software to autofill information in via a form where uses copy data from an existing tabl. There are linked record fields I want to pass into the appropriate fields onto the other table. However, I can only pass in one unique ID and not an array of multiple records. For instance, I have a record with this value of a lookup field with record IDs: rec7Nk1lhJ0VJ0PNa, recbWvD9TI1PkMpdM I need to have this in a formula where the output is: rec7Nk1lhJ0VJ0PNa This should also work for where there isn’t a comma-separated string, as if one record is already it should just pass the same value Frankly, it doesn’t matter if it’s the first, 2nd, 3rd, etc record, what matters most is I need to pass only one record ID into the URL of the form Is there a way to use Regex_Extract() to obtain just one instance? edit: I learned that regex doesn’t work on an array in airtable and you have to turn it into a string, but the formula doesn’t work that I’ve arrived at so far: REGEX_EXTRACT({ACompa

80
A
R
random_nameNew Participant
posted in Formulas
How to remove specific word (long rich text field type with multiple lines)

Need to remove word ‘Keyword’ from field ‘formula test’. I would like to have a separate column (Mostly a formula column) that would give me output with no word ‘Keyword’ in the list.

244
R
J
Jenny_PitmanNew Participant
posted in Formulas
Nested IF statements that include ORs

I need help building a nested IF formula with ORs that implements the following logic: If {Field A} = ValueA OR ValueB then return contents of {Field X}, If {Field A} = ValueC, then return contents of {Field Y}, if {Field A} = ValueD OR ValueE, then return contents of {Field Z}. Thanks!

132
J
K
Katelyn_MogerNew Participant
asked in Formulas
Array Unique Function for Linked Tabs

I have a base I am using for rehearsing a play. I have several tables including a character table, which I linked to the scene table, inputting which characters appear in each scene, finally linking a rehearsal table which has which scenes are being rehearsed on any given day. In the rehearsals table, I am trying to find any characters that will appear in any of the scenes rehearsed that day, without duplicates. I have tried a rollup command and a function command. I have played with ARRAYFLATTEN and ARRAYUNIQUE to no avail. Can anyone help?

384
kuovonne
O
Owen_MarshallNew Participant
asked in Formulas
Total Number of Packages Sold in a Month

Hi Everyone, I had previously accomplished this in Google Sheets, but since moving to Airtable, I’ve had a bit of trouble replicating it. We’re a dog training company, and want to count the number of packages that each trainer sells in a given month. My table has a column with a date field that contains when the package is sold, a column with the trainer’s name (a linked record to a trainer’s table), and a column if the package has been paid for. Previously in Google Sheets, I used a table that contained the date range for each month (ex. 1/1/22 to 1/31/22). The formula would see if the date the package was sold was between these dates, checked the trainer’s name matches, and if the payment contained the word “Paid”. Would love some help trying to get this working with Airtable :slightly_smiling_face: Thank you

51
Justin_Barrett
Valentino_Escal
Valentino_EscalInspiring
posted in Formulas
Wind Direction Formula (wind bearing number to text)

Hi Community! I am trying to create a formula for wind bearing and convert it to a text. This is the formula I successfully created in Microsoft Excel and I was trying to follow a similar logic for Airtable… =CHOOSE(1+ROUND(A2/22.5,0),“N”,“NNE”,“NE”,“ENE”,“E”,“ESE”,“SE”,“SSE”,“S”,“SSW”,“SW”,“WSW”,“W”,“WNW”,“NW”,“NNW”,“N”) This is the formula I unsuccessfully created for Airtable. ROUND({field1}/22.5),(“N”,“NNE”,“NE”,“ENE”,“E”,“ESE”,“SE”,“SSE”,“S”,“SSW”,“SW”,“WSW”,“W”,“WNW”,“NW”,“NNW”,“N”)) Note: {field1} is a integer field containing the wind bearing number. Any ideas?? Thanks!

295
Valentino_Escal
J
Joel_Andrews1Inspiring
asked in Formulas
AutoStatus Column based on Team Assignments

I’m guessing this scenario has already been covered, but I couldn’t find it anywhere. Feel free to point me to the relevant article if so. I’ve got 5 status fields, one for each of 5 teams. When a new project is added, the person adding needs to identify whether or not each team has any tasks to do in association with the project. If so, they pick “Yes”; if not, “No”. Then each team is responsible to update the status in the field from there. I want to create an AutoStatus column that put out the following: “In Progress”: if any of the team statuses is “In Progress” “On Hold”: if any of the team statuses is “On Hold” “Complete”: if all 5 statuses are either “Complete” or “No” (ignore "NA "- I’m taking that one out of the list) “Not Started”: if all 5 statuses are either “Yes” or “Reviewing” This level of complexity in formulas is beyond me at this point, but I’m confident that someone here will know how to do what I’m looking for! This will be an awesome step forward from what we’re

79
J
A
April_KastnerKnown Participant
asked in Formulas
Multiple IF statements after searching for blank field

I currently have an IF statement formula nested into a longer SWITCH formula, and am looking for a way to modify it some. Currently it is IF({Total Order Sold Price} < 15, 2.95, IF({Total Order Sold Price}>= 15, {Sold Price} * .2)) I want to first check for data in the {Total Order Sold Price} field. If there is data, I want it to run this set of IF statements. If there is no data, I want it to run the same IF statements, with a slight modification: IF({Sold Price} < 15, 2.95, IF({Sold Price}>= 15, {Sold Price} * .2)) I’ve tried writing it a few different ways, the best I came up with is this: IF({Total Order Sold Price}, IF({Total Order Sold Price} < 15, 2.95, IF({Total Order Sold Price}>=15, {Sold Price} * .2))), IF({Total Order Sold Price}=BLANK(), IF({Sold Price} < 15, 2.95, IF({Sold Price}>=15,{Sold Price} * .2))) Nothing is working so far though. Any help is appreciated

173
A
D
Daniel_HamerNew Participant
posted in Formulas
Help with Formula using REPT

Hello. I have two tables. Hardware and Ratings. Hardware is linked to Ratings and rolls up different ratings for hardware records. I have a roll up field in the Hardware table called ‘Average Rating Back’ which rolls up all the ratings (from the Ratings table) per hardware record to create an average. The formula I’m using here is: IF(ISERROR(AVERAGE(values))=0, AVERAGE(values), BLANK() ) I have a formula field as below in the Hardware table which converts the roll up fields integer and turns it into stars (essentially an average rating). IF({Average Rating Back}="",“This hardware hasn’t been reviewed yet”,REPT(“ :star: ”, {Average Rating Back})) If I don’t have an integer in the roll up field then the text will appear as per the IF statement. If I do have an integer then I use REPT to show a star per integer i.e. 2 = :star: :star: . This was all working fine until I just received an #ERROR! after the roll up field rolled up a ‘5’ and a 4’ rating for a hardware record. Do you know wh

70
D
S
Stephen_JoslynParticipating Frequently
posted in Formulas
Select last string form an Array Rollup - Everything after last comma, up to and including 2 digits after the decimal point

Hi there, I have have a roll-up field that gives a load of values as CSV. Great. Turns out they are naturally in chronological order - Great again. They are all variable values with sometimes variable decimal points too. So I want to extract the last value. e.g. From this 19795.59,3909.09,6753.98,12435.720000000001 I am only looking for “12435.72” I’ve been thinking that I can use RIGHT and FIND the last “,” and select what comes after. But I always need a number of characters to look for. Is there a way to include the range to be from the last “,” to the last “.” +2? Thanks in advance!

20
S
H
Hector_J_CarrParticipating Frequently
asked in Formulas
Number Formatting problem

So im having an awful hard time trying to figure this out. I only need the price in “Monto Texto”, im trying very hard to get only the number but for some reason “Monto2 Cut” is not formatting into number. My other guess was that its the comma that makes it unable to treat it as a currency or number, so I created a third column where I deleted the comma and still no luck. Any help is appreciated. I just need to be a number to make some calculations.

143
D
Tracy_Standish
Tracy_StandishNew Participant
posted in Formulas
Inventory Tracking - total left in stock

I want to track when a person takes stamps, and display the total stamps we have left. For example, We have 200 1st class stamps. Courtney takes 5 1st class stamps. The new stock is 195. Bridget takes 5 stamps. the new stock is 190 1st class stamps. Also tracking global stamps, and ‘over 1oz’ stamps. I need to track who takes what, when, and how much we have left. Easy to do in excel, not so much here.

217
K
E
Edward_ZevNew Participant
posted in Formulas
How to make a formula field that adds to itself

I have print shop. And I have a need to calculate the useful usage of printing roll. For example, I know that the roll is 100meters, so if I add all printed jobs between replacements (ex.89meters), I know that my useful usage is 89%. So I have this table with fields (order#, meters, date roll changed, #number5) where #number5 right now filled manually. How can I replace it with formula field doing same comulative function between roll changes.

31
kuovonne
M
Maria_Ada_SantoNew Participant
asked in Formulas
If an email address column contains [string], put "ABC"

Hello everyone! I have an email address column. I want to create a formula column that will search each email domain for a specific text string; if that specific string exists, the formula column will print a pre-determined ID (also as a text/string). To illustrate: If an email address comes from abcd@paypal.com, input “PAYPAL” into the formula column. If an email address comes from zyxw@vrbo.com, input “VRBO.” I’ve tried combinations of Nested IF statements, SEARCH and FIND formulas, etc. but I’m stuck and would appreciate help. Here’s a screenshot showing the end result I’m looking for.

172
Justin_Barrett
W
Wayne_MerlinoInspiring
asked in Formulas
Formula Help for giving "Pass" or "Fail" grade with multiple data sources

Hello Airtable Community! We have a situation where I am trying to create a “pass” of “fail” grade on wether or not an estimate was submitted by a certain date and time. Our table is structured as follows: There is a field called “24 Hour Deadline for Estimate” that is the deadline we are holding our estimator to as far as his deadline to submit to our customers. There are a few other fields that show the two different estimates and their correspond totals, as well as the roll up of the two types for a grand total. We also have two lookup fields that are filled out when we actually submit each type - “(MIT) Date Submitted (LU” and ““CON” Date Submitted (LU)” are these two fields. When our estimator submits one or the other on another table, he puts in the date. That is what these two fields are looking up. The ultimate goal of the formula that I am stuck on is to grade as “Pass” or “Fail” if we submit both of the estimates before the deadline. This field is named “Grade on 24 Hour Sub

196
W
J
Jeremy_RossNew Participant
posted in Formulas
When I group by {Parent} it only says "CloudSec-378" and I want it to find/return the associated issue key and summary, so the grouping title is more clear ex (CloudSec-378: Ent Sec Internal Documentation)

The primary field here is the issue key. The issue key represents an “Epic” when the {parent} field is blank or when the issue type is “Epic” not “task”. The parent field links issues, with their epics - this confuses my existing formula for concatenating parent and summary or issue key and summary fields.

10
J
Z
zws-airtable-adNew Participant
posted in Formulas
Filterbyformula using fillable fields in a encoded URL

I am trying to have fillable fields in a encoded url that uses filterbyformula. here is my example: unencoded formula that gets a subset of records from a table. Here the search criteria is explicit. OR(AND({authenticatedUserId},{authenticatedUserId}=‘recB7VzcJLuL6UgIK’),AND({mobile},{mobile}=’+1 2345678987’),AND({email},{email}=‘johndoe@example.com’) here is the encoded form that will be used in the url as query. filterbyformula=fields%5B%5D=zwsMemberID&filterByFormula=OR(AND(%7BauthenticatedUserId%7D%2C%7BauthenticatedUserId%7D%3D’recB7VzcJLuL6UgIK’)%2CAND(%7Bmobile%7D%2C%7Bmobile%7D%3D’%2B1+2345678987’)%2CAND(%7Bemail%7D%2C%7Bemail%7D%3D’johndoe%40example.com’)) I would like to be able to have the explicit data be fillable fields. filterbyformula=fields%5B%5D=zwsMemberID&filterByFormula=OR(AND(%7BauthenticatedUserId%7D%2C%7BauthenticatedUserId%7D%3D**{{input_auth_user_id}})%2CAND(%7Bmobile%7D%2C%7Bmobile%7D%3D{{input_phonenumber}}’)%2CAND(%7Bemail%7D%2C%7Bemail%7D%3D{{input_

10
Z
J
Josh_CheekParticipating Frequently
posted in Formulas
How do you query for a field with a carriage return in it?

Here you can see I have 2 records, one has the value "abc\ndef" and the other has "abc\rdef". I can query the first one with {Value}="abc\ndef", but I cannot figure out how to query the second one. The obvious thing of "{Value}="abc\rdef" does not work, nor does any other thing I’ve tried.

51
B
K
Kelsey_Freeman1New Participant
posted in Formulas
Suspected Bug in Formulas (Simple Addition and Multiplication Formulas)

Edited: I solved the first issue I mentioned. However I cannot figure out the issue regarding the formula {750mL}*13

73
B
N
Nina_TsarykovicNew Participant
posted in Formulas
Formula to return the name of time period based on dates

Hello everyone! Hope you can help. I’m looking for a formula that will return the number of a quarter (e.g. Q1, Q2, etc.) based on the current date and the date when the customer started his subscription. For example, if the customer subscribed on October, 1st, 2021, and today is 13 January, 2022, it should return Q2 so that we knew his subscription phase, or quarter. Thank you!

41
Kamille_Parks11
D
Dean_McDowellNew Participant
posted in Formulas
Copy cell to another table if its record meets criteria

Happy New Year, Airtabletons. I hope 2022 is good to you all. Here is my scenario. I have a table which is populated with all our employees, freelancers and contacts. Using a combination of manual entry and linked records I then populate a 2nd table with only our employees and freelancers which is then used to construct an org chart. My question is, is there a way to automatically copy the contents of a cell from the master table to the 2nd table IF the record contains a certain company name within it’s record? So for example: If a record within TableA contains “CompanyA” or “CompanyB” I want to have the first cell of that record entered into a new line on TableB. I hope that makes sense guys. Thanks in advance for your time.

92
D
A
AlbaNew Participant
posted in Formulas
DATETIME_FORMAT returning wrong time

Hi there, I’m using Airtable to log some vitals for my doctor, and, for some reason, DATETIME_FORMAT is returning the wrong times. So, 1:17 becomes 7:17, for example. Here’s my formula: DATETIME_FORMAT({Auto Date},"M/D/YY - h:m ") & {AM/PM} Any suggestions would be appreciated! :slightly_smiling_face:

567
A
M
Mariusz_JankowsNew Participant
posted in Formulas
Function counting down the value -1 every one day

Hi, I am agonizing over a function that will allow me to subtract a value of -1 every one day from the number in the “Days” column. The function is supposed to start counting when TODAY()>=“Date Start”. I made such a function in the “Days count” column but it shows the range always smaller by 1(when there are no weekends) and when there are weekends by (-1, -2, -3 etc) depending on how many weekends it has along the way. Here is my function from the Days count column: IF(TODAY()>={Date Start}, IF(Weekends="with weekends", DATETIME_DIFF({Date end},TODAY(),'d'), IF(Weekends="without weekends", DATETIME_DIFF({Date end},{Date Start},'d')- (INT(DATETIME_DIFF({Date end},{Date Start},'d')/7)+ IF(DATETIME_FORMAT({Date Start},'e')>DATETIME_FORMAT({Date end},'e'),1,0))*2, "") ), “”) Anyone have any idea how to write such a function that would do this correctly? The function has to subtract from the value in the Days column the value -1 every day and ideally it

71
M

Badge winners

Show all badges
Terms & Conditions