Formulas

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 tutoria

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}&g

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.

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.

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!

I’m not even sure how to name this topic :frowning: Here’s what I want to do. I have a table of Instagram posts and a table of hastags. I have the hashtags organized by Group. I’d like to be able to link each post to a hashtag group and then display the hashtags within that group in the Posts Table. P.S. Why can’t I add images to this post?! I’m sure I’m missing something dumb. Any ideas?

I’m trying to make a formular to that’ll use certain information if it’s after a particular time stamp. However no matter what I do I seem not not be able to figure out how get airtable to tell me if a certain date is after another. Sofar I’ve been trying variations of the following. Possible someone has an idea to what I need to do? IS_AFTER(DATETIME_PARSE(‘22/1/2020’, ‘DD/MM/YYYY’),{Date stamp})

I’m trying to return the same number if the value of a cell is within a specific range. If the cell C value is within .875-1.125 it returns $29. If outside of that range it returns $56. This is the formula I tried that didn’t work: IF( C>=.875, 29, IF( C<=1.125, 29, 56 ) )

Hello everyone, Context : I am trying to calculate how many days one task, and a whole project (which is a compilation of many tasks) takes to be done. The difficulty here is that I need to exclude in the days the hours that are not worked. We assume that we are working between 9:00 and 19:00, which makes 10 worked hours a day. We also assume that we are not working on Sundays. Knowing this, I have 3 different columns : The first tells the date and hour of the beginning of a task The second tells how much total time the task takes to be completed The third one is supposed to give me the end date and time of the task when it is completed, knowing all of our assumptions. So I am trying to build this formula where I could calculate the 3rd column. Does anybody have an idea ? I tried to look in the forum and I was surprised that this question had never been raised Thank you very much

Hi, I have a column that has a drop down box that has different options: Very Low, Low, Medium, High, Very High. I have set up a substitute formula in a new column that replaces the above phrases with a number: Very Low = 1, Low = 2, Medium = 3, High = 4, Very High = 5 My substitute formula looks like this: SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE({Level of Influence}, “Very Low”,“1”), “Low”,“2”), “Medium”,“3”), “High”, “4”), “Very High”,“5”) The results that are returned work unless you select “Very High”. It shows up as “Very 4” rather than “5” in the column with the new number value. I can’t figure out why the first four work fine and the last doesn’t. It looks like the substitute formula is replacing the “High” in “Very High” with the “4” Can someone help me figure out how to fix this?