Skip to main content

Formulas

Leverage this space to unlock the power of Airtable formulas.

  • 5,939 Topics
  • 18,228 Replies
5939 Topics
J
julieqiuNew Participant
asked in Formulas
Using DATEADD() for just one value in a multi-value column

Hi all, I want to use the DATEADD() formula on only the oldest created value (date) in a Lookup column that stores multiple values (a 1-to-many relationship is needed). I can’t seem to figure out a way to design the base or write a formula that does this. Any ideas? Thanks, Julie

152
kuovonne
Z
Zulmee_POUPARDNew Participant
asked in Formulas
Replace letter by its position in alphabet

Hi, I need to convert letters to their position in the alphabet. So A becomes 1, B → 2, C → 3 etc… Does Airtable have a function to do that? I’d like to avoid the multiple IF statements :upside_down_face: Thank you

291
kuovonne
M
Matt_StevensonParticipating Frequently
posted in Formulas
Lookup related records within same table

Ok, I’ve scraped the forums and can’t figure this out. I have a catalog of courses, and I’m trying to show the related courses (only within a particular discipline) but also, only at a particular campus. I can create a lookup that shows all of the courses tagged within a specific discipline. I just can’t get it to filter that ALSO by campus. I can lookup all courses by campus, but then not also by discipline. I can only do one or the other. I’ve thought about setting up an automation in another table, or creating campus-specific Views. But I can’t see how I can do this without manual entry. Is there a Formula or Rollup formula to filter the Lookup results by another Linked Record field?

102
M
D
Dennis_PetrouKnown Participant
asked in Formulas
Remove unwanted characters

Hello, this is an example of a value in my NAME field: 1290 Name 3 (Some Words) 1255 Another Name 12 (more words) I’m looking for a formula that will get rid of the numbers and space before Name, and anything in parentheses) so would result in: Name Another Name Thanks for any help!

342
D
S
sudomthaNew Participant
posted in Formulas
Lookup with multiple criteria

Hello, Is it possible to perform a lookup with multiple criteria on Airtable? Similar to this: Sheetaki – 24 Apr 21 How to Use INDEX and MATCH Function with Multiple Criteria in Google Sheets:... The ultimate guide in understanding and using the INDEX and MATCH function with Multiple Criteria in Google Sheets. Learn exactly how to use the INDEX and MATCH function with Multiple Criteria in Google Sheet. Est. reading time: 12 minutes Example data table: Item / Size / Color / Availability / Price Shirt / L / Red / Yes / 3.00 Shirt / M / Blue / Yes 5.00 Jeans / S / Black / No / 10.00 Lookup Columns: Item / Size / Color Return: Availability / Price

50
S
H
Henry_Meiklejo1New Participant
posted in Formulas
Troubleshooting a Count Field

I’m building a base to track my workouts in the gym. I have a table where each record represents a set of an exercise performed. It has fields for the weight used, the number of repetitions, the name of the exercise (dropdown), and the muscle group worked by the exercise (IF formula based off the value in the exercise field). I know from a base design perspective that the exercises and associated muscle groups should probably be in a separate table and then linked to the table for sets. However since I will be adding records during my workouts on my phone, I found that the usability was better when keeping everything on a single table. With a linked record when selecting the value, there would be a lag time, the values would not be alphabetized, and I would have to scroll through. Whereas with a field on the table, there would be no lag time, and the values would display in a single alphabetized block. What I am looking to do is create a table with one record for each muscle group, wit

51
kuovonne
T
TailoredTouchesParticipating Frequently
posted in Formulas
Formula Help for Dates

This is our current working formula. IF(AND({Number of Items}=5, {VA Recipient?}),57.39, IF({Number of Items}=5, 55, IF(AND({Number of Items}=7, {VA Recipient?}),73.18, IF({Number of Items}=7, 70, IF(AND({Number of Items}=10, {VA Recipient?}),88.98, IF({Number of Items}=10, 85)))))) We want to specify dates for this. So, if before October 1, 2020, then the above formula is correct. BUT between October 1, 2020 and October 1, 2021, the formula should be: IF(AND({Number of Items}=5, {VA Recipient?}),57.70, IF({Number of Items}=5, 55, IF(AND({Number of Items}=7, {VA Recipient?}),73.60, IF({Number of Items}=7, 70, IF(AND({Number of Items}=10, {VA Recipient?}),89.50, IF({Number of Items}=10, 85)))))) And then finally, starting October 1, 2021, the formula should be: IF(AND({Number of Items}=5, {VA Recipient?}),63, IF({Number of Items}=5, 60, IF(AND({Number of Items}=7, {VA Recipient?}),78.90, IF({Number of Items}=7, 75, IF(AND({Number of Items}=10, {VA Recipient?}),94.80, IF({Number of Items

42
T
O
oceanseleven11Inspiring
asked in Formulas
Weirdest problem on "superior to" formula

Hi all, I’ve been spending around 2 hours trying to understand what I was missing in a simple “if X superior to Y, then show X, otherwise show Y”… Here’s a screencast of what’s happening, can’t believe my eyes :Screencastify I have Y = 11.8 If I change X to 9, the formula says X is higher than 9… Very weird behaviour here as shown above. Any hint on this amazing bug??

94
Alexey_Gusev
L
Lowell_RingelNew Participant
posted in Formulas
Making the Share View Calendar Link a Lookup field

I have about 45 (and growing) different Airtable calendars, all with their own unique “Share View” Link. Is it possible to have a lookup or linked record field in a different table that displays all of these links? I feel like there must be a formula for this. I would also ideally want this to update automatically if the links are changed. Thanks!

31
Kamille_Parks11
R
Rachel_EricksonParticipating Frequently
asked in Formulas
Splitting dimensions into 3 columns

Hi everyone, I currently have a base where there is a column for DIMENSIONS in the form of manual entry formated as such: 12" x 10" x 8" I would like to SPLIT the numerical values into 3 different columns (column 1 = 12, column 2 = 10, column 3 = 8) From my research I have not been able to find an adequate split formula (I know that airtable, unfortunately, doesn’t have an easy way to SPLIT) How best would I go about splitting this information by a split-like formula?

276
R
T
Tech_Support2New Participant
posted in Formulas
Easy way to see all formulas / check for dependencies?

I want to clean up a base for old, deprecated fields. The problem is, I want to make sure they’re not being referenced in a formula field somewhere. (Or that their Look-Up or Roll-Up copy on another table isn’t in a formula.) Is there an easy way to check dependencies? Or an easy way to search all formulas at once?

231
kuovonne
A
Alexey_AlexeyNew Participant
posted in Formulas
Joining the values of two "Multiselect field" into third "Multiselect field" and keeping the list updated

Hello ! I am is looking for a simple solution for joining the values of two “Multiselect field” into third “Multiselect field” and keeping the list updated if values in initial fields change. And can you please advise how to manage the list of values of “Multiselect field”, - for example create conditional lists etc. Thanks in advance ! Alex.

11
P
R
Remi_BlatesicNew Participant
posted in Formulas
Set a field value if condition else do nothing

I have a table with a status column. I created a Kanban view for these status and I would like to set the current date when a certain status is set. For example when status is STARTED set the started_date column to NOW() So I use the following formula : IF(status = 'STARTED', NOW()) It works to set the date but the issue is when I switch the item to the ENDED status the started_date is cleared. I would like to know if there is a way to set the column value when the condition is true and do nothing when it’s not. Thnaks

181
Alexey_Gusev
D
Daniel_BurkeInspiring
asked in Formulas
Extracting Time from Date Field - But how to avoid #ERROR if date field is blank?

Hi there, I am using a formula field to pull the time stamp (and t-zone) from a Date and Time field. I am using this formula currently: DATETIME_FORMAT(SET_TIMEZONE({Day 1 - Start}, ‘America/Los_Angeles’), ‘h:mm a’). This works wonderfully, however, if the time and date field is blank (which, in my case, does happen) my time stamp field shows #ERROR. How can I add to this formula so it knows to leave the field blank if the date and time field is blank? Would appreciate all and any help on this. Daniel

112
D
R
Rachael_CastelaKnown Participant
posted in Formulas
Count All + Percentage of Total

Hey there, I’m looking to capture: Total Number of Vendors (1st column) that are in each Lifecycle Stage (Ex: 2 in PO Launched, 1 in PO Placed) and The % of vendors relative to the total (Ex: 40% of all vendors have a Lifecycle Stage of PO Launched) Questions: Does Lifecycle stage need to be a linked record? For my case it doesn’t (although that’s how it’s shown in the screenshot) Can that live in this table, or will it need to live in a separate, linked table? Thank you!

287
J
M
Michael_LeidensParticipating Frequently
posted in Formulas
How to calculate difference between 2 records?

Hi, I need to calculate the evolution between two records. Row 1 = Week 1 ; 50 followers Row 2 = Week 2 ; 75 followers etc… In a column: (75-50)/50= 50% (progression of follower) How to do it in Airtable? Thanks

302
kuovonne
Nedra_Hines
Nedra_HinesInspiring
posted in Formulas
Concatenate with datetime formula is adding an additional day

Hey everyone! I’m creating a column in a table where I’m concatenating a date field (formula driven), client name and default text. The result of the concatenate is adding an extra day to the formula driven date. Below is the formula and I uploaded an image of the dates reflecting the additional day. Any insight on what’s causing this? It’s so weird! Formula in the concatenate field: CONCATENATE(DATETIME_FORMAT({Monthly Rental Fee Renewal Date},‘MM-DD-YY’)," | “,{Client Name},” | ",“Rental Fee Renewal Date”)

32
Nedra_Hines
C
Corey_MayneNew Participant
asked in Formulas
SECOND-Most Recent Testing Date Formula?

Hey all! I’m stumped. I have a “Link to” column compiling every testing date, then a “Most Recent Test Date” rollup column using MAX (VALUES) to list back what the most recent date is. However, sometimes we schedule testing dates in advance. HOW do I get a column to determine what the most recent testing date is before TODAY? Thanks so much!!!

111
Kamille_Parks11
J
Jason_NashKnown Participant
asked in Formulas
Formula not working / just creates an error

{Business User Value} + {Time Criticality} +{Risk Reduction - Opportunity Enablement} / {Story Point Estimate} The first part works great then I add the / and error its nothing to do with the format of the fields as {Story Point Estimate} + {Business User Value} works fine… any ideas? its seem really odd to me

3228
Kamille_Parks11
R
Rose_WilsonNew Participant
asked in Formulas
If statement true if plus or minus value

Hi there, I’m using airtable to compare an actual quantity with a goal quantity, then using an If, Then statement to tell me whether I’m at my goal, need to increase, or need to decrease my actual. Here’s my current formula: IF({% of total}={Goal % of total}, "OK", IF({% of total}>{Goal % of total}, "Trim", "Expand")) This is working fine, but what I would really like is if I were able to specify a range for values that are “close enough” to the goal. For example if {% of total} is plus or minus 0.5 of the goal, I want it to return OK. Anyone have any thoughts on how to do this? Thanks in advance!

136
R
L
Lowell_RingelNew Participant
posted in Formulas
Getting Formula to Run only if cell not blank

I’m trying to get a formula to run only when the cell has a value. I work with hotels, so we send out weekly summaries of the entertainment scheduled for that week. I am creating a column that will automatically pull out the specific location (Venue) within that hotel where the performance is taking place. This is the formula I am currently using (which works): CONCATENATE("| ", {Venue (from Venue)}) Not all hotels have a specific venue name for the performance location. If the “Venue (from Venue)” field is blank, I would like to make it so this formula doesn’t even run. I did some reading in the forums and tried the below formula (amongst others), but it isn’t working. IF({Venue (from Venue)} = BLANK(), BLANK(), CONCATENATE("| ", {Venue (from Venue)}) Can someone help me? Thanks!

182
L
F
Frank_VittoriniNew Participant
posted in Formulas
Checkboxes Disappearing/Archiving After Being Checked

Is there any way to have checkboxes disappear after they are clicked? I know there is a way to sort so that all the completed records (with a checkbox) filter to the bottom but is there a way to move them so they don’t appear amongst other uncompleted projects? Thank you!

01
A
V
veryallowedNew Participant
posted in Formulas
30 second countdown timer

I’m trying to create a 30/45/60 second countdown timer, how would I create a formula for TIMESTR(NOW()) and +30 seconds into the future. I would then point this new field to the countdown app. sorry im very new to this

311
Justin_Barrett
B
ben_pappasNew Participant
posted in Formulas
Help! stuck for 3 days

I need some help. I have a column of address like below: 12345 Anywhere St, San Fransico, CA, 90000 I need to add the street 12345 Anywhere to is own column called street. Same with city ect. whoever can solve has a VENm0 coming with 25 dolls.

23
J
B
Bab_BitNew Participant
posted in Formulas
IF from single select option

Hi, I can’t get the formula to work. Can we do condition formula on Single select option? Example; Single select option Category A B I’ve tried, IF(Category=A / IF(Category=‘A’ & IF(Category=“A” but none work. Please help. What I need is; IF Category is A, “Yes”, Else “No” I have no problem for digits, but stuck for text. Thank you.

123
P

Badge winners

Show all badges
Terms & Conditions