Skip to main content

Formulas

Leverage this space to unlock the power of Airtable formulas.

  • 5,945 Topics
  • 18,249 Replies
5945 Topics
D
Dani_OliverKnown Participant
asked in Formulas
If this, then this. If blank, then that

I’m not sure why this one isn’t working, because I’ve tried copying and pasting from another base where this formula DOES work. I’m trying to write an IF statement for the Gift ID. If “Donor Account” is not blank, GIFT ID should pull Donor Account after the date. If it IS blank, it should pull Company. This is my starting point, but it’s telling me it’s not a real formula. (DATETIME_FORMAT({Date of donation}, ‘M/D/YYYY’) & " — ") & IF(Donor account, Donor account,Company) Thoughts?

81
kuovonne
D
Daniela_HechtInspiring
posted in Formulas
Substitute in string with quote marks

Hello Community, I’m having an issue with a substitute function I hope you ban help with. I need to substitute certain numbers in a string that includes quote marks, and I get errors or invalid formulas in every try This is my original string, I have it in a separate field called _bto _data a:2:{i:1595610800;a:29:{s:10:“query_type”;s:12:“category_ids”;s:21:“assigned_category_ids”;a:1:{i:0;i:227;}s:14:“selection_mode”;s:9:“dropdowns”;s:10:“default_id”;s:4:“2615”;s:5:“title”;s:26:“Elige la base de tu Anillo”;s:11:“description”;s:97:“Selecciona una de las opciones de la lista con el metal, talla y piedras secundarias que deseas. “;s:12:“thumbnail_id”;s:0:””;s:12:“quantity_min”;i:1;s:12:“quantity_max”;i:1;s:8:“discount”;s:0:"";s:19:“priced_individually”;s:3:“yes”;s:20:“shipped_individually”;s:2:“no”;s:8:“optional”;s:2:“no”;s:14:“display_prices”;s:8:“absolute”;s:13:“select_action”;s:4:“view”;s:18:“hide_product_title”;s:2:“no”;s:24:“hide_product_description”;s:3:“yes”;s:22:“hide_product_thu

60
D
I
Info_UpliftNew Participant
posted in Formulas
Helps needs with search function for Airtable in Zapier

Hi guys, I really need help with setting up the search formula for my specific case. We have an attendees email column in Airtable, which each cell will includes multi emails, e.g. email1, email2, email3. In the zap, I need to search certain email in this column, and find the right row, then read the other related info from the same row. I used the FIND("{{94825201__hidden_email}}",{AttendeeEmails}) in the search formula in Zap, which works, but it doesn’t apply to find the exact same email, e.g larose@test.com (let’s say email A), and rose@test.com (let’s say email B), when searching B, the system will find A. Then I changed the formula to FIND(", {{94825201__hidden_email}}",{AttendeeEmails}), added ", " into, which can help to find e.g “, A”, but this doesn’t apply to the first email address, since there’s no ", " before it. Does anyone have any idea about this will be very appreciated! Another small question, does anyone have the idea to find the multi records/rows at the same time?

30
I
D
David_GroveNew Participant
posted in Formulas
Extract Initials for Given Names

I have two fields in a table for a sheet music library: Last Name and Given Names. Is there a formula expression that will produce the initials for the given names? For example, If the Given Names field is just “Johann”, I would like to produce the string “J.”. If the Given Names field is “Johann Sebastion”, I would like to produce the string, "J. S. ". If the Given Names field is “Carl Philipp Emanuel”, I would like to produce the string, "C. P. E. ". Etc. Thank you for any comments. DG

8413
kuovonne
T
TronNew Participant
posted in Formulas
Automatic file numbering - Year-File#

Hello, I’m trying to create an automatic file numbering scheme for my project list and file list. I’ve read some of the other posts on this but can’t get my specific need. I’d like the filing scheme to be: year (last 2 digits) + 3 digit sequential number: so 20-001, 20-002, etc. I figured out how to pull out the string value for the year from current date (i.e. “20”) and I’ve figured out how to sequentially number entries. But I can’t get the two together. For example, when 2021 starts, I want the numbering to reset to 001. Is there anyway to do that? Many thanks,

61
Z
K
Kendall_HeardNew Participant
posted in Formulas
Time Stamps for a Status Change

I am looking for a way to trigger a timestamp when a status changes. This is for an article writing process and we have 3 status options, (In Creation, In Review, Complete). I want a timestamp to take place when the status is changed between each status so at the end, I can calculate how many days the article was in each status. I am hoping to do this so the timestamp doesn’t override itself when the status changes but can help me see how long it stayed in each status. I know how to trigger a timestamp using a checkbox but want the status change to trigger that on its own. THANKS SO MUCH FOR THE HELP!

51
Z
A
Ahmed_ElagamiKnown Participant
asked in Formulas
This Week formula for a due date

Hi Folks, I need help writing a formula that returns only data for this week related to a release date I know this formula works IF(WEEKNUM({Release Date})=WEEKNUM(TODAY()),“this week”,“NOT”) However, it still shows me content released for the next year same as this week. I only want to work on the current year not all years in my view. Thanks, A lot for your time

102
A
M
M_WNew Participant
asked in Formulas
Extract four digit number from cell (i.e. year from citekey)

Hi, for my academic research database I’m trying to write a formula that will automatically extract a four-digit number from a cell (i.e. extracting year from a citekey). For example (Citekey --> Year), Downs1965NonmarketDecisionMaking --> 1965 Welborn1968BureaucracyAnthonyDowns --> 1968 Any ideas on how I might be able to do this? Thanks.

13
ScottWorld
J
Jason_FriedlandNew Participant
asked in Formulas
Nested Formula Help

This is probably so easy, but I just can’t get it to work. I hav 13 columns that are using a formula to extrapolate a comma separated list from a single column. That works great. I am then trying to use a new formula field to look across all 13 and put the result of the last column that is not blank. (i.e. if column 1 has text then check column 2. If column 2 is blank then column 1 in field if column 2 has text then check column 3 and if 3 is blank them column 2 and so on. the columns are called item1, item2, item3 and so on. Thanks in advance

32
kuovonne
R
Ruby_SnidermanNew Participant
posted in Formulas
Automatic number fill down a field

Hi, I am creating a cash float in Airtable. I have a field called ‘Start of Day Cash’ that gets entered via Form. However, we now have remote days where people are not on site to count cash. Is it possible to have the record be the same as previous entries automatically UNLESS somebody fills out the Form to update it with a new number. Then all the subsequent entries would change to that updated number via Form. Thank you!

726
ScottWorld
D
Dani_OliverKnown Participant
asked in Formulas
Producing most recent field by date attached

Not sure how to describe this one… I have a primary field in one tab of Publications. Each publication has a Pub Date column. In another tab, I have Last Name as a primary field (of authors) and want to create a column that tells me the most recent publication they were attached to. If I use Roll-Up with max value, I can get the pub date itself (1/12/20) to appear rather than the name of the most recent publication the author is attached to. So I think I need a formula that would produce, instead, the most recent Publication the author is attached to. Any thoughts?

122
kuovonne
J
Jeni_MeadowsNew Participant
asked in Formulas
Change in Transaction Cost from Specific Date

Hi, I work for a charity that distributes surplus food to members of the local community for a small weekly fee. I use Airtable to keep track of, among other things, how much each person pays each week, versus how much they should’ve paid. I use two linked tables to track this - one on which I record each individual transaction, and another that uses rollup fields to add each member’s transactions together so I can see each member’s history at a glance. I’ve set it up so that the Rollup table shows me how much (if anything) a member owes us in total, but the actual maths for that is done in the Transactions table. At the moment, one food delivery costs £3, but we’re about to increase that to £4. My trouble is with how to get Airtable to reflect this change. If I just change the formula for the amount owed from {Packages delivered}*3 to {Packages delivered}*4 then it’ll re-calculate every member’s payment history and tell me that everyone is £1 per week in debt, so I’m hoping there’s an

50
J
C
carloschristianParticipating Frequently
posted in Formulas
Generate list of file titles with one variation depending on user selection

I’ve been trying to figure out how to make this crazy formula happen, but I’ve hit a wall. Can it even be done? Check out below and let me know how you’d accomplish such a thing… In the base, users select from a list of deliverables via a linked record field that contains everything a client can order for a particular asset (InstagramAd, SnapchatAd, Master, etc.) Each deliverable has a generic file title that follows a specific convention: Everything between the exclamation points is replaced with information specific to the asset when the deliverable is chosen on the Order table. So the above generic file title auto-generates this asset-specific file title: Figured it out :slightly_smiling_face:

10
C
E
Eli_bNew Participant
asked in Formulas
What am I missing? Remove everything after *

How would I remove everything after *? Using this works but also gets rid of the ones that only have 1 name. LEFT( {Name}, FIND( " * ", {Name} )-1 ) Maslow *Loyal, Lovable Companion Max Bella Hope’s Boy 2 Hope’s Boy 1 Chase *Snuggle Bug (pending)

22
E
R
Ryan_Anderson1New Participant
asked in Formulas
Looking for help writing formula for pay earned based on variable

I’m looking for help writing a formula to show what pay a staff member has earned based on a variable. I have hours worked (at 30 per hour for one role, and 25 for a different role), and trainings complete at 50 per session. How do I write a formula that looks at the “role assigned select field” and calculates based on the correct rate of pay? Thanks,

73
JonathanBowen
B
Becky_TaylorNew Participant
posted in Formulas
Combining two formulas to stop blanks returning errors

I’ve created a formula to subtract no.of days from a date, this has worked, however a number of the fields are blank so returning an error, I’ve created a separate formula for that too which also works, but I don’t know how to join them together in the same formulae? DATEADD({Date auto-renew goes into effect},-{Auto renew notice period},‘days’) IF({Date auto-renew goes into effect}, " ")

41
JonathanBowen
M
Mandy_HutchisonKnown Participant
asked in Formulas
Formula Help - IF Formula to generate Date

Having trouble with an IF formula. IF({Job Size}<=10,DATEADD{Order Start Date,7,‘days’},DATEADD{Order Start Date,14’days’}) I’m trying to generate a date based on our ‘Order Start Date’, where if the integer entered in the ‘Job Size’ field is less than, or equal to, ‘10’, it will add 7 days to the ‘Order Start Date’, and if not (specifically a larger number), it will add ‘14’ to the ‘Order Start Date’. I’m not seeing what I’m doing wrong :woman_facepalming:

119
M
J
Jason_Friedlan1Known Participant
posted in Formulas
Conditional Formula to Update Status from Form

Hey Everyone: I have a form that takes multiple inputs such as Date/ Status/ Update String. They all link to another table where I can see the most recent update per item. So as my team updates items from week to week I have a view of the most recent. The issue is that not each input updates from week to week. So I want to use a conditional formula in my form table that will see if the input Update Status is blank and if it is then take the previous update that was not blank. Having a hard time figuring this one out. I hate that forms if left blank blow out a record that was there. I should be able to conditionally control those by saying if no response keep previous.

152
J
D
DippityKnown Participant
posted in Formulas
Linking 2 tables so that one table is a summary of the other

Table A contains individual evaluations of project performance. I want to create Table B that summarizes the evaluations by Project Team (average of individuals’ responses for a specific project). Table A fields include: Project Name I Year l Responses to Evaluation Q’s What is the best way to link both tables so that individual responses for a specific project automatically appear altogether in 1 cell in Table B? Below are the tables that I hope to create. All the separate responses in Table A to autopopulate in Table B according to the projects they are linked to so that I can average all responses by projects in Table B.

34
D
E
EYES_InternatioParticipating Frequently
asked in Formulas
How to count number of times a word comes up across a different columns?

We have a weird way of keeping track how many interviews our interviewers have done. I would like to know how to count the number of times “1” comes up in each cell across all columns from Jan 2020 - August 2020. So in my simple way of formulating it would be something that translates the following (number of times “1” comes up in Jan2020) + (number of times “1” comes up in Feb2020) + (number of times “1” comes up in March2020) + (…etc etc). You can access a view example here: https://airtable.com/shrVjcpeWwW1jC51n Let me know if something is not clear! Hope somebody has some ideas or an answer :slightly_smiling_face: Thanks in advance.

165
E
U
Unai_EzkurraParticipating Frequently
posted in Formulas
Extract number digits from cells with text & numbers to sum them

Hello everyone! I need help with this situation. From cells with text followed with (number) I would like to extract just the number value to be able to sum in a new column. See the example of what Im looking to do it. Thank you very much!

10
U
E
EYES_InternatioParticipating Frequently
asked in Formulas
How to get automatic todays year into a formula

I am running a membership organization, we have “Member Since” that is a year, e.g 2014, 2018, etc. I want to calculate on a column to the right, how many years they have been member. Something in simple words like =TODAYS YEAR - “Member Since” (in the picture it is “PGM Since”) So that this adapts to every year. Otherwise I can just keep it to =2020-“Member Since” Anybody can help with that? How to get automatic todays year into a formula

197
E
B
Ben_HInspiring
posted in Formulas
Search and Fill

Hello Everyone, I think this should be fairly simple. I have two tables. Customers (auto populated when an order is made) and the Quiz Results (auto populated when a quiz is taken on the website.) I would be simple to link records and look up the appropriate data. We want it all to be automated though. The email address is most likely to match can I search(email) to link records then look up the fields. Thanks in advance @Mohamed_Swellam @Justin_Barrett BH

1211
Justin_Barrett
J
Jason_Friedlan1Known Participant
posted in Formulas
From Responses Updating Another Table

Hey Everyone: I am having an issue that I am sure is solve-able but I am stuck. I have one table that houses all my company roadmap features. I then have an additional table that has a form for my team to go in and fillout weekly updates. The issue is that not every column updates each week. Right now I have the Roadmap Items Table rolling up the max date and then using a formula on the other table to use that date to only pull in the most recent update for each column. That all works fine. Where it falls apart is when a user submits the form but leaves one of the section unfilled because it doesn’t need to be updated from the last time. Is there a way to set it so I get a super set of the most recent update for each of my columns across multiple entries? Description / Planned Activity/ Update / GTM Date?

10
J
N
Noel_BolgerKnown Participant
posted in Formulas
If a date and time field is not blank then copy to date column

If a date and time field is not blank then copy to date column

118
N

Badge winners

Show all badges
Terms & Conditions