Formulas

Greetings Airtable Community! I am hoping a more experienced user can provide guidance on a formula. I currently have a formula field: IF(AND({Guest Code}=‘DONOR’,{Status S1}=‘Yes’),First&" "&Last) This is returning the correct data (e.g. Robert Jones); however, I need to expand the formula to also include the guest of First Last (e.g. Robert Jones, Sally Smith). The guest field names are With First and With Last. Any assistance would be greatly appreciated. I’m up against a deadline, so it’s time sensitive. Cheers and thanks, John

I have an issue with the formula in the ID field not displaying correctly as it has several decimal places even though the column it’s getting the data from is set to currency integer (2). How do I get it to display correctly? This is how it’s showing: This is my formula for calculating the Amount Outstanding: And the format: And this is my formula for the ID field: Anyone know a way to fix this?

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…

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!

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)

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

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)

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!

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!

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

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.

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é

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.

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?

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!

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!

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

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

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.

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}

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”

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.

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.

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

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.