Skip to main content

Formulas

Leverage this space to unlock the power of Airtable formulas.

  • 5,944 Topics
  • 18,247 Replies
5944 Topics
H
HerbshirtParticipating Frequently
posted in Formulas
Display Total from 1 table in another

I’ve searched this topic but I’m obviously missing something because it seems too simple … I have a table of parts, each of the parts have a cost so the table has a Total column. I want to have that Total value appear in another table (then I’ll be deducting that value from corresponding columns to generate another figure. How do I simply reflect the value from a column in 1 table, in another table? Like I said, it seems crazy simple…

53
E
J
Jeannie_Ruesch_Known Participant
posted in Formulas
Last Updated By formula field?

Hi there – There was a formula field option called “Last Updated By” - that seems to have disappeared? It was there at the beginning of January… is there another way to get this information now? Anyone know? Thanks!

11
J
M
Milton_VillarreNew Participant
posted in Formulas
Diferencia de fechas, con una fecha vinculada

Hola, si alguien me puede ayudar con una diferencia de fechas, donde: Corte: es la fecha final y Fecha: la fecha inicial, cuando Corte esta como tipo de campo Date y Fecha también, la formula abajo me funciona perfectamente, pero cuando Corte, esta vinculado a otra tabla cuyo campo es Date, la formula no funciona, probé anteponiendo a Corte, la fórmula DATETIME_PARSE({Corte}), pero no logro convertir dicho campo a data para que DATETIME_DIFF, lo entienda como fecha y resuelva la operación. Alguien me puede indicar cómo convierto ese campo vinculado a tipo data para que funcione la fórmula DATATIME_DIFF? (El campo que se diligencia es tipo Date y diligencia automáticamente el campo tipo vínculo, con una supuesta fecha, en la columna Corte) ROUND(IF({Fac Dep}>0, 0,IF(DATETIME_DIFF({Corte}, Fecha ,‘days’)+1>={Vida Útil}*365,{Vida Útil}360, (DATETIME_DIFF({Corte}, Fecha ,‘days’)+1)/365360)),0)

212
M
U
Utah_MortgageNew Participant
asked in Formulas
Spreadsheet RATE() Function

Is it possible to recreate the RATE() function from Excel and Google Sheets inside Airtable?

21
O
O
ONS_PublishingNew Participant
posted in Formulas
Pre-determined times within formulas

Hi, I would like to calculate how long a project will take based on the number of components * various pre-determined times. Here, for example, I’m saying that it takes 5 minutes to work on one Table. I would like to replace the 5 with 00:05:00, if possible. As this is an average time, I would like it to be part of the formula and not taken from another column. Thanks! (Datasets+{Time series}+{Supporting articles or methodologies}+2)*2 + ((Words*4)/500) + ((Charts+Interactives)*3) + Tables*5 + ((Datasets+{Time series})*1)

30
O
S
Sebastian_JuholNew Participant
asked in Formulas
Countdown to upcoming birthdays

Hey. I’ve tried searching for solutions here, but haven’t found anything I was able to implement. What I’m trying to do is listing all my close friends and relatives, and creating a field that shows the number of days until their next birthday. Then I’d sort it by 1-9 so that the next birthday is on top. When the birthday is over, the “days until” should then jump back to 364, and therefor be sorted to the bottom. Could anyone assist me here? Thanks!

305
R
J
John_DlouhyKnown Participant
asked in Formulas
If Concatenate Formula

I have a field named Concatenated Attendee Name(s) with this formula: CONCATENATE(First&" “&Last&” “&{With First}&” "&{With Last}) I have another Multiple Select field named Guest Code. One of the many choices is ‘Scholar’. How would I edit the formula above to only concatenate names that have the Guest Code of ‘Scholar’? Thanks very much!

175
Justin_Barrett
J
JGMioNew Participant
posted in Formulas
Recursive rollup -> summing values

HI fellow Airtablers, I am building a priority tool for myself that, at it’s core, has a list of tasks. Each task has a total priority score. Now tasks can have dependencies and I want to add their total score to the “total priority score” of the top(or root) task" This works for one level deep but not recursive. Airtable gives me an error regarding a circular reference. Which is logical if I would link a task to itself but I don’t… Anyone have an idea how I can fix this? Thank you and I will post the solution if I find it. I can’t add images or GDrive links so please DM or something for screenshots of my table

90
J
A
Andrew_StrotheiNew Participant
asked in Formulas
If Then Statement populating

I am trying to write what I believe would be best as an IF THEN statement but I’m open to suggestions. I have a column named client payment date and there’s a number, 1-31 there. I want it to print out in a different column (Which I’ve named “Payment Transfer Date”) one of three things. If the number under “Client Payment Date” column is between 1-10 then I’d like the “Payment Transfer Date” column to display 15th. If the number under “Client Payment Date” column is between 11-20 then I’d like the “Payment Transfer Date” column to display 25th. If the number under “Client Payment Date” column is between 21-31 then I’d like the “Payment Transfer Date” column to display 5th. Any help would be greatly appreciated as I feel I’ve gone above the skill level on what I know.

113
Justin_Barrett
A
Andre_ZijlstraInspiring
posted in Formulas
2nd and 3rd top value

Hi everybody, in Excel you can use a MAX formula and set it in such a way that it renders the top value, the 2nd best, 3rd best and so on. Is this possible in Airtable? The Max formula doesn’t seem to allow me to add these parameters. Regards, André

171
Justin_Barrett
S
Sarah_BrewsterNew Participant
asked in Formulas
Counting Check Boxes

I have a spreadsheet that I am using to keep track of when a document is returned. The primary field is the person’s name, then there is a column to record a specific program that person is in (set as a single select), then the next column has a check box. The check box will be selected when the person returns their document. I’d like to record how many people return their documents by Program. So how many people in Program A have returned their documents, Program B, and so forth. Is there a formula to create this? Ideally, I’d have a second table that has the Program name in the first column and then the total in the second column. Essentially, a countif formula.

242
S
W
West_AraluceNew Participant
posted in Formulas
Rank Summed Values

I have grouped ROI (a result of a formula) by Ad ID. There are multiple Ad Id’s that I have combined by grouping and summing the values to get the total ROI for each Ad ID. Now I would like to rank the summed values for each Ad Id. How can I do this?

61
M
D
Dirk_Jan_de_WitParticipating Frequently
asked in Formulas
How do I add DATETIME_FORMAT to this formula

Hi awesome community. I want to create a new field that calculates the previous delivery date + 1 month. So I used this: DATEADD({Last Delivery},1,‘month’) But the problem was that it gave me errors when the date was blank. So I tried using the IF formula. IF ({Last Delivery}, DATEADD({Last Delivery},1,‘month’)) But now it doesn’t come in a friendly format. I figure I have to use the DATETIME_FORMAT but I’m not sure how. I would like to see for example 6 April 2019 Thanks for your help!

307
S
J
Joseph_DesiletsNew Participant
asked in Formulas
Color by field using conditional with a long list

I have four fields: FirstName, LastName, Email, and Zip. I’m trying to set up a conditional where if the Zip is any of ~75 options from a given geographic region it will color all of them one color for quick identification. The base is connected to webforms through Zapier with several dozen new records added every day, so just need an automated way for team members to identify which new records are in their region without having to compare every entry to the list of zip codes in their region. Any help would be greatly appreciated!

130
Z
K
Kieran_BallNew Participant
asked in Formulas
How to automate SMS according to date?

Hi guys! Relatively new to Airtable. I’ve got what feels like an advanced question. I want to be able to send SMS messages to members of my database. I know there is a Twilio block in which to do this, which is great and very useful (I like the feature that pulls in Name and other fields that personalise the messages). However, I want this to be automated. I am currently building a base for general management of members data, including their upcoming appointment dates in a field entitled ‘Date of appointment’. What I want to happen, rather than doing it manually, is for an SMS such as ‘Hi {First Name, Last Name}, you have an appointment with {Name of doctor} on {Date of appointment}. Please call our offices if you’re running late on 07xxxxxxxxx.’ to be sent when {Date of appointment} is 24 hours away, for example. I’ve also had a look at Zapier to help with this but I’m not entirely sure how to do it. Can any body help/point me towards a tutorial or something that deals with this? I ho

276
K
J
Jason_PartridgeNew Participant
posted in Formulas
Multiple IF() formula but not working

Hi Everyone, I’m sure I have done something wrong but new to Airtable… Trying to do a multiple IF() formula but not working… the simple formula is to generate a rental bond amount depended on two fields… the “Rent Frequency” and the “Rent on Agreement” (The rental bond has to be equivalent to 4 weeks rent) What I have been trying is: IF({Rent Frequency}=“Week”,({Rent on Agreement}*4), IF({Rent Frequency}=“Fortnight”,({Rent on Agreement}*2)), IF({Rent Frequency}=“Month”,{Rent on Agreement})) Rent Frequency is a single select dropdown for either (Week, Fortnight or Month) Any guidance would be much appreciated Thanks Jason

75
J
S
Steven_JoinerNew Participant
asked in Formulas
Looking for a field that is "not empty" in an if statement

I’m sure it’s here or obvious, but I’m not sure what to search for. I simply want a formula that says: If Field A is not empty, then 1 else 2. Sorry if it’s obvious. Thanks.

4202
J
C
Cindy_HawkinsNew Participant
asked in Formulas
Formula to create a unique ID

I use an algorithm for assigning a client ID which is the static “0660” followed by the 2-digit year the client started, followed by a 2 number sequential. I am using the following formula. How do I adjust this formula so that clients who start in 2021 restart the sequential numbering? ‘0660’& (Year)&-REPT( ‘0’, 5-LEN( {AutoNbr}&’’ ) )& {AutoNbr}

1453
Justin_Barrett
W
Wesley_FlachNew Participant
posted in Formulas
Crop Mapping with Airtable

Hello everyone, I’m trying to create a crop map where every row represents a vegetable bed on my farm, labeled 1-A-1, 1-A-2…1-B-1, 1-B-2, etc. In my crop plan table, the location column is linked to to the map table so that I can choose that my second succession of lettuce will go into 1-A-1 and 1-A-2. Another crop may also go into those beds later in the season after the lettuce has been harvested. My hope for the map table is for each row to represent a bed and for each column to represent a week of the season. When the date of that week is between the planting date and harvesting date of a crop located in that bed, I want the formula to return the name of the crop. I got this basically working by using a lookup column to pull the planting and harvesting dates into the map table, adding columns to convert the dates to numbers, and then writing an If formula for each Week column. IF(AND({Plant Date to Numeric}<20200521,{Last Harvest Date to Numeric}>20200521),{Crop Plan},“False”

170
W
V
VALU_Co-opNew Participant
posted in Formulas
Date Conflict formula

Hi! I’m looking to create a formula that returns “CONFLICT” if collaborators are trying to sign up for the same resources at the same time. I have a “Start Time” column, and “Finish Time” column as well as a “Resources” column. I’d like to find a formula that returns “CONFLICT” if people put overlapping time in AND the resources are the same. It feels like a “IS_SAME” formula nested within an “IF, THEN” formula, but for the life of me I can’t figure it out.

110
V
C
Craig_MatthewsNew Participant
posted in Formulas
Days of week in a month

Hi all, Does anyone have a formula that would return a count of the number of a particular day of the week in a given month/year? eg can Airtable tell me how many Tuesdays there are in Oct 2021? Thanks.

60
C
A
Alvaro_VillacorNew Participant
posted in Formulas
Fecha y hora de encendido y apagado de una computadora

Hola, que fórmula puedo usar para calcular la fecha y hora de encendido de una computadora remota?. Y también para cuando se apaga. Gracias

20
A
A
Adele_RoyNew Participant
posted in Formulas
Setting publication dates based on event dates

My project will warehouse events and publication dates. I have publications on Thursdays and Sundays. A particular event could be scheduled for any day. I’d like a formula that queues publication 3 Sundays prior, and another that queues publication 1 Thursday prior, etc.

00
A
B
Brite_AdminInspiring
asked in Formulas
Nested emoji formulas

I have a formula that puts a checkmark in front of the Task name if the task is done. It works fine. IF({DONE}="",{Task Name},“ :white_check_mark: ”&{Task Name})&" "&DATETIME_FORMAT({DUE DATE},‘M-D’) I want to add another condition and can’t figure out the syntax. If the event is not done but is past the {Due Date}, I want a :no_entry: emoji in front of the Task Name. I am not sure how to incorporate that in front of the task name. Any help would be appreciated.

51
B
P
PJ_Saysell-RosaNew Participant
posted in Formulas
Expiry Date formula

Hello! I have created a food management base for my film production company and I want to create a formula that will flag food products that are: -Expired -Expiring within 28 days The name of the field with the dates in is “Use by Date”. Thank you!

111
Justin_Barrett

Badge winners

Show all badges
Terms & Conditions