Skip to main content

Formulas

Leverage this space to unlock the power of Airtable formulas.

  • 5,942 Topics
  • 18,244 Replies
5942 Topics
F
Franck_GENTILI1Inspiring
posted in Formulas
single data extraction

Hello, I want to extract from the rollup field "noms des participants" only one of these data for which appear in the fields "Nom Prenon 1" "....2" "....3" "....4" ..... VUE https://airtable.com/shroD4pRoA52pXcg6Thank you for your helpFranck from FRANCE 

87
M
J
jennyafrankNew Participant
posted in Formulas
Associating multiple select field with subsequent specific multiple select fields

If I make multiple selections from a dropdown list is there a way to associate each of those selections with a specific selection from a different multiple selection field? Using the existing Product Catalog template as an example it is easy to understand what my issue is..... If I choose two+ items from the multiple select "Materials" field (ex. "Light Wood" & "Metal"), then choose one or more colors from the multiple select "Color" field (ex. "white" "brown" "black") I have no way of knowing which color is associated with which material. Is there a way to differentiate between having a "Light Wood" in "white" "brown" and "Metal" in "brown" "black" "white"?

31
Alexey_Gusev
P
Pamela_GilesInspiring
asked in Formulas
Combine two formulas to format postal codes

I'm trying to format Canadian postal codes. Often the person enters them as k0e1b0. I need them to look like K0E 1B0. UPPER({PostCode}) will make them upper caseREPLACE({PostCode},4,0," ") will put the space in the middleHow can I do both of these steps in one formula field?

81
P
I
ianonlineParticipating Frequently
posted in Formulas
Linked record or Lookup, or both?!

Hello, I'm frustrated! For nearly two hours I've been looking at Airtable documents trying to achieve what I could do in five minutes with a spreadsheet using the filter or query functions. There are a few recent posts on the same subject but each case is slightly different and I'm too frustrated to focus for long. Soon I need to go off to my paid work having spent the entire afternoon on this.I've got one table with a list of unique place names in the primary field along with related data, I want a second table in the same "base" (I think that's the word) to display all places with specific matching data. For example, list all places in a specific region where the region is specified in another column of the first table.Maybe linked records or lookup or both are what I need, or neither. Nothing is clear. I follow the advice in videos and articles then in practice half the time the option to lookup for link a record isn't even shown.If it's possible with Airtable, and if I can get the

571
C
P
pdenmanNew Participant
asked in Formulas
Formula displaying zero result as a blank field (and hyphen in Interface)

Hi Everyone,I'm new to Airtable and formulas and have a pretty simple formula that is displaying a hyphen as a result of a calculation of zero, instead of the number 0. I admit I borrowed the formula originally from one I found on here, but not sure why the number I've designated isn't being displayed.It's not a true numeric calculation, but a replacement I'm using to give a numeric value to a field that also has text for ratings, i.e. 3 - Exemplary, etc., so that I can total up all the ratings from different categories and assign a value.Here's the formula:    IF( {Field1} = '0 - Deficient' , 0, IF( {Field2} = '3 - Exemplary', 3, IF( {Field3} = '2 - Accomplished', 2, IF( {Field4} = '1 - Developing', '1', 'Out of range' ) ) ) )    I personally have no idea why the formula shared was nested and if that has something to do with it, but everything else about the formula is working. It's displaying like this in the table and the interface. 

873
P
R
Rita_SpanuKnown Participant
posted in Formulas
Help/Any Solutions?

Hello All,I need to migrate all of my records from an old base in to a new base.  The records in the old base have been assigned a number (through Auto ID plus a formula).  We are leaving the old base behind, importing all of the records in to the new one base, and resuming work in the new base.  I am trying to figure out how I can have Airtable resume number sequencing from where we left off in the old base and continue in the new base.  The new number sequence does not have to start exactly where the old one left off, and i cannot have the Auto Number feature assign a new number to existing records that I have imported.For example, we use Airtable to create and track projects.  Whenever a project is created, we have a summary field formula that takes the Auto Number (in this example 50011) and joins with with the prefix PR- (making it PR-49998) and also joins it with the project name (again in this example it is Organic Social Retainer - TEST).  The end

20
R
L
luizgualmeidaNew Participant
posted in Formulas
Subtract multiple values in two different coluns in the same row

I have multiple values in two separate cells located within the same row. My objective is to subtract the first value from the "Stock" column from the first value in the "Quantity for Production without Units" column, and continue this process accordingly. Finally, I would like to place the result in a third column.So it would be something likeEstoque          Quantidade para produção sem unidades          Estoque-Consumo46200                              6000                                                        

112
L
C
Cristiano_VolpiParticipating Frequently
asked in Formulas
Compare text in a field with a table, using lookup and logic

Hello Community, please help to solve in an elegant way!I have record, and in a field called "FOOD" I have text separated by comma, let's say in the text I have"FOOD" = Apple, Carrot, sausageI then have another table caleld "FOOD CATEGORIES" with all the food separated by categories like (Can be a table for each category, same situation):Apple - FRUIT Banana- FRUIT Mango- FRUIT Carrot - VEGETABLEZucchini - VEGETABLEPotato - VEGETABLESausage- MEATBeef- MEATChicken- MEATIceCream - DESSERTCheescake - DESSERTCandy - DESSERTSo now I want that in the field next to the field "FOOD" will be a field called "FOOD CATEGORIES" where it will appear the food categories according to the table classification, the previous example, I want to see CATEGORIES FRUIT, VEGETABLE, MEATAt first I tough I could do with a super concatenation of IF, but seems not very elegant approach.The real challenge is that in the field food, could be a lot of different items, so I want that the match will be d

172
C
Ritchie_Wong
Ritchie_WongNew Participant
posted in Formulas
Create new records for [Monday of the week of created time] - [Sunday of the week of created time]

Hi Airtable community, first time posting here. Thanks for your help in advance.So, I'm trying to create something like YC Startup School weekly update forms: every new record will be named as "[Monday of the week of {created time}] - [Sunday of the week of {created time}]". Just like this:I'm very new to using formulas, but would really love to learn how that works.Tried "Weekday", "Dateadd"Got stuck right here. Thanks a lot! 

40
Ritchie_Wong
N
Nocode34130Inspiring
posted in Formulas
Find and substitute the single € symbols

Hi !I'd like to substitute the "€" symbol only when it's alone in a field. (alone but with a space in front)For example, I've sometine a field with :1.00 €2.00 €€€€and it's very ugly in our contract.  ><I thought at the "LEN" and "FIND" formulas but I struggle to not replace the € when it is accompanied with a number... It would require a formula that only searches and replaces € symbols when it is alone with a leading space.Any ideas will be greatly appreciated :-)))) Thanks !

00
N
X
xiongfeiwaiyin_Inspiring
posted in Formulas
How to show day of next week. like 'next Monday'

Hi .. Hi. How can I show 'next week day' . I know.  DATETIME_FORMAT(Date, 'dddd'). but anyway I can how 'next Monday' ?

83
X
Miles11
Miles11New Participant
posted in Formulas
Find the date that is the last date before another date (closest but not after)

I could not find a solution for this in the forums: From a list of dates, I need to find that date that is the last date on that list before the launch date of a given project. The idea is to find the date best suited for an event to happen before a project is launched.Full description:Records in table "Status" are each linked to one specific "Project" and one specific "Outlet". "Status" looks-up the launch date of that project. In "Outlets", multiple dates are rolled-up from a table called "Release dates".I am looking for a formula that returns the one "Release date" that is closest, but not after the Launch date of the Project. The returned date must be in the "Status" table.I cannot use conditional look-up, as the date I need to compare with is not relative to today, but referenced elsewhere. So no way to use a relative condition like "before today" or so.I have transformed the dates in to Unix timestamps already. Mathematically, the date to be returned is the minimum positive value

40
Miles11
A
andre-nap92New Participant
posted in Formulas
Bring the sales price with two conditions Customer and Route

I need to get the price from another table and bring it to my booking table. The conditions must be based on the company and the route to bring the correct sale price registered in the Routes Price table,My airtable system is already working and I use the Company field as a lookup for the Customers table and the Routes field is just a single select.However, today, to form the sales price, I use the supplier's cost price, but this needs to be changed and the sales price needs to be added manually in another table to later appear. If customer is A and route is A show this price If the customer is B and the route is A show this price.

20
A
O
oreocereusKnown Participant
posted in Formulas
Auto-adjusting timeframes according to time of year...

I have no idea how to make the title succinct and easy to understand, but here's what I want to do. I run a market garden. I am building a crop planning tool inside airtable. Throughout the year, the typical timings of events for different crops will change.For this example, we'll use my Weeks to Harvest calculation. In peak summer, lettuce will be ready to harvest 4 weeks after transplant. In the darkest part of winter, it'll take 8 weeks.Now, there is not a cut off date where suddenly it goes from taking 4 weeks to 8 weeks. So I want to be able to store the following data against a crop1) Fastest weeks to harvest (peak summer) - e.g. 42) Slowest weeks to harvest (dark winter) - e.g 83) Fastest date of the year (based on transplant date) e.g. Jan 14) Slowest date of the year  (based on transplant date) e.g. June 1And then I want to make a formula field that will adjust the weeks to harvest based on the time of year of transplant.&

41
O
S
Steve-ONew Participant
asked in Formulas
Using a formula to populate EDITABLE date fields?

I run a table that keeps track of content my team creates for a digital billboard. When we hear about new content, our project manager builds out a production schedule based on the date the content is supposed to go live on screen.To help my PM with scheduling, I used formulas to populate some milestone columns (initial concepts due, kickoff to production, multiple creative reviews, etc...) with some estimated days for each step in the process. For example: Project Kickoff should occur about a month from the start date and uses "WORKDAY({Start Date}, -33)" while the File Handoff date is only a week before launch and uses "WORKDAY({Start Date}, -5)" So far, this has worked great for providing some rough dates during the initial project planning stages, but we run into problems with our calendar when real world events force dates to change. For example: If the PM knows that a certain executive will be out of office on the formula-generated milestone date for a creative review,

757
S
I
IrisANew Participant
posted in Formulas
RSVP - limiting participants

Hello,I would like to create an RSVP form that can limit the number of participants. Is it possible?

1408
R
K
KeisyNew Participant
posted in Formulas
Date diff rounded by years months and days

Hi,I'm trying to determine how to display the difference between two dates as years, months then days.I have a start date and an end date, (the moment a worker started in the company and the moment he ended his contract with us), and I need to know how to have the result like "1y,3months,15days" or something like that, I found a formula that gives me the result in weeks and days, but a week always have 7 days, but a month can vary the days (28 days, 30 days, 31 days), so I am kind of confused

1073
A
J
Jack_ColeyNew Participant
asked in Formulas
Determine if WORKDAY() returns a Friday or Monday

I'm using the WORKDAY function and it's skipping the weekend as it should, but it always returns the previous Friday. Is there a way to have it return Friday if the date falls on a Saturday, and return Monday if it falls on Sunday?

151
C
A
angel0936491749New Participant
posted in Formulas
NEED HELP FOR FOLLOW-UP DATE Formula

"I am conducting research to monitor the depression status of patients. It's crucial that I track their status on a weekly basis. Consequently, I am looking to set up a reminder calendar to facilitate this. I've utilized the provided formula to determine follow-up dates, but Airtable suggests that the outcome isn't recognized as a date format. This has hindered my efforts to create a calendar using the results. I would greatly appreciate any advice on this matter. Thank you!"   IF(    {First visit},    DATETIME_FORMAT(        DATEADD({First visit},1, 'weeks'),        'MM/DD/YYYY'    ),    "")

61
R
T
Tender_TeamNew Participant
posted in Formulas
How to consolidate inventory base

Hi, I'm working on a way to consolidate a base used for a diaper program. We're a nonprofit and need to track for many different reasons, but our current process is long and requires toggling from table to form to table. Here's what we're doing (all are in separate tables):Entering donation inventory via form (an individual count of diapers, wipes and formula)Receiving requests via formReviewing the requests and inventorying out the items via form (linked to step 1)Here's what I'd like to do:Inventory donations via form (needed because amount spent is required for tax purposes)Link the request and inventory out form to subtract from inventoried donationsIs it possible to deduct (2) items at once or do we have to inventory out each item to properly subtract?Essentially, all three tables would be linked together with the inventory out and request forms "combined" to avoid exiting the table when fulfilling the requests for delivery or pick-up.Is this possible? I feel like it is, but I'm n

112
M
S
symoneNew Participant
asked in Formulas
Date formula override

I'm using the formula below to calculate the date difference between the day a position was opened to today (tracking number of days the position is open until filled). Is there a way to override that formula if the Position Status is listed as Filled or to make the formula only run on Positions with the status as Open. I don't want the running count of days once the job is filled, but don't want to lose the data point of the day it was opened.DATETIME_DIFF(TODAY(),({Open Date}), 'days')

41
R
C
Christina_SturrNew Participant
asked in Formulas
Formula or Rollup

Hi there,I am trying to set up an OKR table in Airtable.  I have two tables in my workspace.  One for Key Results and one for Initiatives.  Under initiatives I have a column with tasks just check off as they occur.  Please see below.  How do I create a column with a formula that will calculate the % completed based on the check marks listed in the task column.  I have tried to create the formula with creating additional columns (total task count and total task count completed to calculate off of but that didn't work).  I used the "countall tasks" in the total task column and tried to create a formula in the total task count but it isn't working.  Any guidance would be greatly appreciated.     

203
C
D
DanBlissNew Participant
asked in Formulas
Function to test if date field is empty or null

I created a table called Items (household items).  In Items, there is a partially filled-out field, PurchaseDate, that is some items have a PurchaseDate; other items don't have a Purchase Date (field is left blank).  I want to run a filter of those items with a blank PurchaseDate:  Is the best way to do this is to create a formula field, named noPurchDate: LEN(DATESTR({PurchaseDate))=0 such that if the PurchaseDate is not empty, noPurchDate returns FALSE, and if PurchaseDate is empty, then NoPurchDate returns TRUE? Is there an easier way to get this result? Another more "normalized" way of handling this might be to create a separate table, PurchasesWithDates linking Items to PurchasesWithDates.  How does one create a filter of Items with no link to PurchasesWithDates? 

2393
D
Y
yhouissaNew Participant
posted in Formulas
Quote Generation Based on Fixed Prices, With Multiple Inputs/Outputs

Hey there!I'm migrating some data and processes from Google Sheets to AirTable as it is far more responsive to the frontend of a portal I am developing for my company. Right now, I'm using AirTable as the backend for the quote generator we use for our events. I'm looking to do the following:Have a set of Items that may be changed (Ex: Min/Max Price Per Person, Event Discount - Percentage Value, Weekend Markup (A True or False value set if the event happens on a Saturday or Sunday), Amount of Raw Materials that will be priced using fixed amounts based on what I mention in Section 2)The Price Per Person will go up a fixed $ amount of $40 if the Weekend Markup is set to TrueHave a set of prices that will virtually never change (Cost of Instructor, Cost of Safety Officer, Cost of Raw Materials)Generate a range from Minimum to Maximum price per person to be quoted withinGenerate an "Addon" value of additional costs (Being the total of applied prices that will never change)Create a Final "Re

611
ScottWorld
B
barbburenNew Participant
posted in Formulas
Making a Formula Field a normal field so it is editable

For my salesperson's airtable,  He needs to change the name of a few companies. He is unable to do that since it is a formula field. How can he undo the field from being a formula field and just a normal field so he can make edits to the name of a few companies?

153
Jean_Thoensen

Badge winners

Show all badges
Terms & Conditions