Skip to main content

Formulas

Leverage this space to unlock the power of Airtable formulas.

  • 5,938 Topics
  • 18,225 Replies
5938 Topics
M
mustOfGotLostNew Participant
posted in Formulas
Extract Text from a Data string

I have read the solutions for extracting text but still can’t seem to get it right. Need extract text between the dashes (-). The text between the dashes could be any number of characters, if there is a limited it is unknown. - ###### - ##### - ##### -

554
M
D
Devan_KreitzerInspiring
asked in Formulas
Splitting Multi-select field into different fields

Hello, I have a Jotform survey flowing into my base per each response. In the survey, I have multiple “Check all the apply questions” that subsequently flow into multiple-select fields in Airtable. I am trying to connect a data visualization program to Airtable to visualize these responses, but the connect combines the row into a single string (i.e. “Option 1, Option 3”), which makes it difficult to visualize. Is there a way to have a field that puts a 1 if a selection is made, or a zero if not. I am trying to avoid making separate tables with available options, since my base already has a lot of tables (this is a big project). What I initially have: What I’m trying to achieve:

405
D
U
ucmavnerdKnown Participant
posted in Formulas
TODAY(), SET_TIMEZONE(), and WEEKDAY() are killing me

I am trying to convert TODAY() to the WEEKDAY() and then to the single letter for that day of the week. I need to match that to a data feed that provides the day of the week as a letter. Here is my formula: SWITCH(WEEKDAY(SET_TIMEZONE(TODAY(),'America/Los_Angeles')),0,"U",1,"M",2,"T",3,"W",4,"R",5,"F",6,"S") The problem is that it is returning the wrong WEEKDAY() numeral. If I do this: SET_TIMEZONE(TODAY(),'America/Los_Angeles') I get the correct day. And if I do this: WEEKDAY(TODAY()) I get the correct weekday. But as soon as I combine these ideas it’s returning the value for yesterday. So, assuming we are using today as 2/2/2022 I’d expect to get WEEKDAY() of 3 and my SWITCH() would change it to a W. But what I am getting returned is “2” which is turning into a T. Thoughts?

52
U
D
Dionisis_PriftiNew Participant
posted in Formulas
Calculate mathematical expression in text form

Hello! Is there a way to calculate the value of a mathematical expression which is written on a long text field? For example (long text field: 340+850 and formula field: 520)

82
ScottWorld
D
Dom_MoirParticipating Frequently
posted in Formulas
Annoying lookup issue

Hi all, • I’m asking Harvest to populate a field in table jobs called {client id by Harvest} via Zapier - this is working fine • I then want AT to lookup the corresponding client {Name} in my client table that relates to the client ID {client ID - manual} in the calient table and populate the field {client name - lookup} in the table jobs i cant make it happen for some reason… quite frustrating. any thoughts?

107
D
Michael_Benson
Michael_BensonParticipating Frequently
asked in Formulas
Commas separating items in array lost

Hi there, I have a rollup field - Field A - which displays items in an array using a comma separator. I retrieve the items using ARRAYUNIQUE(values). When I try and call this field in another field - Field B, this formula keeps the comma separator: {Field A} Produces: 1, 2, 3 When I try and add text to the formula, the commas are lost and the values aren’t separated but bunch into one long block of text: {Field A) & " Text" Produces: 123 Text Would anyone be able to help? How can I add the commas/separators back in between these values?

754
ScottWorld
B
Brad_HolleyNew Participant
posted in Formulas
Formula for Commission Pay

Hi all! We have an employee paid in part on commission. I’m attempting a formula to calculate the pay. Here are the factors: If the appointment shows (pays $25) If purchase is made (pays based on source of purchase) Source 1: $50 Sources 2&3 : $100 So I’m looking for a formula that can display the amount if an appoint shows and no purchase is made and also if an appointment shows and a purchase is made via any source. Thanks in advance for the help!

164
Justin_Barrett
M
Melissa_JenkinsNew Participant
posted in Formulas
Single select formula

Hi, I have created (what is essentially) a spreadsheet that tracks my products for shipping and calculates the costs associated with shipping and duties. I want to create a Single select column where I can select a percentage to add to the Net Value of my items and have it give me the new total including the added percentage - for example, $100 Net cost x 10% = $110. I have multiple percentages I need to choose from - 10%, 15%, 20%, 22% and so on. I’m very new to this, so apologies if this has been asked before. I tried to create a separate table and link them but it says there is an error in the configuration. Any help would be much appreciated!

11
kuovonne
Michael_Benson
Michael_BensonParticipating Frequently
asked in Formulas
Formatting percentage numbers in a formula

Hi there, Let’s say I have a formula field - Field A - that produces a decimal value (e.g., 0.5). I use the formatting option within the formula field to display this value as a percentage. However when I call that field in another formula - Field B - it is displaying using the original decimal value. Is there a way to format my the ouput in Field B, so that it displays as a percentage value? Currently Field B is outputting data like: 0.5 0.8333334 0.2 I want Field B to output: 50% 83% 20% If anyone could help that would be much appreciated!

2044
Michael_Benson
J
Judith_SotoNew Participant
asked in Formulas
Consignment Formula Question: Auto discounting a price based on number of days that have passed

I track consignment items in Airtable. This is my consignment pricing structure: Day 0-29: Initial List Price Day 30-45: Initial List Price minus 15% Day 45-90: Initial List Price minus 25% Day 91: Expired What formula will auto-discount the initial price by the above amounts based on the date when the item sells? Can the entire cycle of auto-discounts be calculated in one column? What formula can be used to mark an item “expired” on day 91? - could this formula also be included in the same column as the auto-discount column? :slightly_smiling_face: Thanks in advance for any help or insight.

270
J
M
Mark_ComishKnown Participant
posted in Formulas
Changing the Attachment filename

I use Zapier Parser to bring over a PDF attachment and update a record in AirTable. The issue is it takes the email attachment and gives it a super long filename in AirTable with letters and numbers. Is there any way to have it be a set filename or custom filename instead? I tried using Format from Zapier but not having any luck.

141
ScottWorld
G
Grace822New Participant
asked in Formulas
Combining IF AND statements (one is a function)

Hi, having issues combining two different IF statements for contracts: IF(Phase=“ :inbox_tray: Lead”,“ :bell: ”) IF(DATETIME_DIFF(TODAY(),{Last Contact},‘days’) >= 7, “ :bell: ”) They both work separately but want to be able to combine them so that IF a contract is in Lead phase AND the last contact date was 7or more days ago, then follow up ( :bell: ). Every time I use the (AND) function, following the example here, it doesn’t work. Is it because the Phase field is a function?

124
G
O
Olivia_EhrenreiParticipating Frequently
asked in Formulas
Is there a way to count the number of rows in a table?

I want to know (and then trigger an automation) when there are 10,000 records added to my table. How can I do this?

131
kuovonne
R
Robinson_BetancNew Participant
posted in Formulas
Multiselect in One table with data of other table

We want to know how to fetch a field from another table and make it multiselect depending on the record it relates to. Example in record one, I bring a field from another table (This one has several data in the same column) and I want to be able to select one, several or all; for record two, I bring the same field from the other table and I only want to select some that are not the same as the one in record two. In the first record, choose a data from the generic task package, in the second record, choose other data

10
R
Melissa_Frank1
Melissa_Frank1Known Participant
posted in Formulas
Calculating Depreciation

I am trying to create a table that calculates equipment depreciation by straight line, sum of years, and double-declining, because we have a number of different equipment types that depreciate in very different ways, but want to monitor all of them in one place. I am struggling to find a way to calculate the accumulated depreciation in each given period. Straight line was no problem, but the sum of years is reminding me a lot too much of high school calculus and I’m ready to take it all back into an Excel sheet no one will ever look at again. I haven’t tried the double-declining yet, but I’m hoping it’ll be easy after sum of years! Is this even possible in Airtable? I suspect I will need helper fields and don’t really want to go there in case some future equipment has a much longer lifespan than I’ve built in. Excel has an SYD function to get the period Depreciation Expenses, but nothing (that I’ve found) to give you the Accumulated Depreciation without referencing another row.

178
N
C
CloeNew Participant
asked in Formulas
Overtime bank per employee / time sheet

Hello beautiful community! I hope you are doing well. I’m looking to optimize timesheets on airtable. I have almost everything set up, but, I am missing a feature: “cumulative time” / overtime. In fact I would like to have in the time bank who is the sum of the overtimes per employee over all periods to know how many hours the employee or the company owes (time balance) I was thinking of adding the SUM/Overtime by period and have it appear in the employee’s profile (Banque d’heure) Do you have any idea of the formula / process to do this? After that, From this bank of hours, the employee could with a form say: I take x hours for recovery. it makes sense? Thank you for your help! Template source : Payroll Time Sheet - Airtable Universe

281
Dimitris_Goudis
E
EAugumParticipating Frequently
posted in Formulas
Pull out most looked up / rolled up item

Hi there, I’m facing a challenge with a lookup / rollup field. I will share a link to an example base I quickly threw together, feel free to check it out to better understand my challenge. Keep in mind that this is a very simplified version of my original base. I have a lookup field which lives in a table of drivers which gives me cars used for different trips. If a driver uses a BMW X3 on 3 trips, the lookup field shows “BMW X3” 3 times. If that same driver uses a Tesla Model 3 for 5 trips, the lookup field would contain BMW X3 3 times and Tesla Model 3 5 times. So far so good. Now, what I want to pull out is simply “Tesla Model 3”, because that was used the most times. If one driver has used two/three cars the same amount of times, I would like to pull out both/all. I’m not sure if it matters but in my base there will never be more than those 3 cars to select from. Also, it is not possible to use more than one car for a trip. Does anyone have any tips and tricks to overcome this?

20
E
S
Sylvie_MNew Participant
posted in Formulas
Slug from Title with hyphens - avoid double hyphens

Hi… the forumulas are quite challenging for me. I am using Airtable with the Airpower Shopify App. A total game changer but I have had to learn Airtable. I want to create a new column where I take my Product Title and make it into a slug (for a webpage title). However I often use hyphens in my titles and all my attempted formulas still create a double – hyphen. Example {Product Title}: Vintage Futa Lures - 9" Slant - MOP Slabs - Like New Result: vintage-futa-lures-9"-slant-mop-slabs–like-new (between slabs and like there is a double hypen but looks like a long dash here) What I want: vintage-futa-lures-9"-slant-mop-slabs-like-new Formula: LOWER(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( {Product Title}, " - ", " "), “–”, " “), " -”, " "), "- ", " "), " ", " "), " ", “-”)) Thanking you in advance for any suggestions!

210
S
E
Emma_SousaKnown Participant
posted in Formulas
How to modify this formula to ignore blank fields

Hi All I have a formula I am using to take a date and add a month to it. The problem is that I specifically have some fields blank. Right now the formula works, but where there is a blank date field, it shows ‘error’. Is there a way to modify the formula, so it would ignore blank date fields and not display ‘error’? DATEADD({Due Date},1, ‘months’) Thanks Emma

372
E
R
Ruth_RaymerNew Participant
asked in Formulas
Formula to find next Monday date from another date field

Hi - New to Airtable and hoping for some help. I need to find a formula that displays the next occurring Monday date based on a date in another field. Thank you for any suggestions.

864
R
E
Emma_SousaKnown Participant
posted in Formulas
Recurring Date each month with change in a year

Hi I have a formula I found in airtable for creating recurring events. This appears to mostly address when the year rolls to a new one - so that it changes automatically. What I’d like to do is in my table have a due date of an item - called RECURRING DATE. In a new column called Due Date - I want a formula that will automatically advance the date to the next due date once current due date goes by. I then want to use that Due Date in a calendar view. The formula I found is: DATETIME_PARSE(DATETIME_FORMAT({Recurring Date},'MM/DD')&"/"&YEAR(NOW())) I tested this with a December 21 date and it does in fact advance the year to the new year, but not the day. So if the recurring date is 1/5/22 and we are now on 1/27/22, I want my due date column to show 2/5/22. Is there a way I can modify this formula to achieve that?

30
E
E
Eduardo_MatosNew Participant
posted in Formulas
Zapier: Exact same formula not working

Hey guys! I have the exact same formula on Airtable and Zapier, it’s working on Airtable but not working on Zapier, what am I doing wrong? (The purpose of the formula is to check if my “Shopify ID (Produto)” is inside a bunch of ID’s that I get from Shopify) The formula: Thanks a lot

81
ScottWorld
B
Beth_KocolNew Participant
posted in Formulas
Calculating Total Time Spent

I have a “All Completed Tasks” table that holds all of my completed ClickUp tasks - two of the columns are: Project Title and Total Time Tracked. I have a second “2022 Projects” table that lists all of my projects for the year. I want a field/column in this table to calculate the total time spent on the project, based on the ClickUp tasks from the first table. I know enough to have a Project Title field in the 2022 Projects table that would match the Project Title field in the All Complete Tasks table (which I think is necessary). But I can’t figure out how to do the calculation. EXAMPLE All Completed Tasks table may show: ABC Project - 12 min XYZ Project - 9 min XYZ Project - 7 min XYZ Project - 4 min XYZ Project - 9 min ABC Project - 11 min ABC Project - 8 min I would like for 2022 Projects to calculate: ABC Project - 31 min XYZ Project - 29 min

421
A
H
Hannah_MorganParticipating Frequently
posted in Formulas
Needing help with a formula

Hello! Noobie here trying to get a bit of help creating a formula. The goal is to get some visibility on when I last commented on a record vs when someone else last commented. This will help me see if they have replied or not so I know to follow up. I already have two formula fields: {last commented by me} (datetime) - this field is set with the timestamp of when I last commented {last commented by someone else} (datetime) - this field is set with the timestamp of when anyone but me last commented So far so good. Now, let’s say I commented on a record 2 days ago, so the {last commented by me} field was set to 23/01/22 6:30 pm. Let’s say it’s now 25/01/22 6:30 pm and I have not yet received a response, so the {last commented by someone else} field is empty, or maybe before 23/01. I’d like to be notified at this point so I can follow up. My thinking is to have another formula that calculates the time from now to when I last commented - sort of like a timer. DATETIME_DIFF(NOW(),{Las

164
H
R
Renaud_SavignarInspiring
asked in Formulas
How to avoid the display of null numbers?

Hi, can I get a blank space/nothing instead of “0” as the result of a formula ?

335
R

Badge winners

Show all badges
Terms & Conditions