Formulas

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.

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?

I’d like to have a column which will display if it’s time for a page to be updated. It should look at my “Last Updated” column date and if today’s date is less than 1 month from then it from then it would say “Current”, if more than 1 month it would say “Needs Update”, if more than 2 months it would say “Update Now”. Any thoughts? Thanks!

Hi I am designing an inventory management system I have a column in our master inventory table which says ‘sold?’. it is a checkbox column. I want the record to be moved to my other table called ‘sold items’ when i check the box so I can keep a track of the total asset value. How can I do this? Please consider that I am new to this. Many thanks

If I’m remembering my FileMaker days correctly, I had the option to have a field filled by a formula/action, and I could then enter something into that field if I wanted to change the info. For example, I’m working on an attendance database in which I check students by creating a record. The creation time of that record is used by a formula field to determine if they were on time or late. I’m trying to figure out what to do if, for example, I step out for some reason and end up logging them in later than they arrive. In that case, I’d want to override the “calculated late”. The best I’ve come up with is to have 2 fields: 1. a Manual Entry Late/Present field and 2. the Formula Based Late/Present field that includes an addition to use the value from the Manual field if it’s not empty. Is there a better way?

Hi, I have a table with rent items. There is a single select field “availability” with “available” and “rent”. Right now i set the “availability” manually if a new linked record “customer” has rent an item. Now I want to automate the things :slightly_smiling_face: As soon as I choose a customer from a list, the single select field “availability” should switch automatically to “rent”. This doesn´t work… IF(Customer = BLANK(), {Availability}=“available”, {Availability}=“rent”) Can someone please help me out? Thanks

I know this has been discussed before and I have read the questions and comments. Unfortunately I am stuck. I have a multi select field that I am trying to extract information from. The formula works fine in Excel =IF(O2=“Individual”,15,IF(O2=“Family”,20,IF(O2=“Social”,10,IF(O2=“No Cost”,0,“0”)))) I have tried the following and none work: =IF({Membership Test 2}=“Individual”,15,IF{Membership Test 2}=“Family”,20,IF{Membership Test 2}=“Social”,10,IF{Membership Test 2}="No Cost”,0,”0”)))) =IF({Membership Test 2}={Individual},15,IF{Membership Test 2}={Family”,20,IF{Membership Test 2}={Social},10,IF{Membership Test 2}={No Cost},0,”0”)))) =IF({Membership Test 2}=Individual,’15’,IF{Membership Test 2}=Family,’20’,IF{Membership Test 2}=Social,’10’,IF{Membership Test 2}=No Cost,’0’,0)))) Can anyone help? Thanks

I run a cat sanctuary and I’m wanting to keep track of vet charges for each individual cat. So I would make a Table for Harry and then I would create a record with all the charges for a certain date. But can I actually do that? The way I’m looking at it now, I would have to create a separate record for each individual charge. Is there a way I can create one record for each invoice?

Has anyone else found that the SEARCH and FIND formulas count unicode characters (like emojis) as two positions within a string, as opposed to one position. This means that both functions seem to return the incorrect index if the matching string starts after any unicode chars in the text to search. Short of stripping out any unicode characters, does anyone have ideas for other workarounds?

I have volunteer applications imported from 123formbuilder to airtable using Zapier. Some parts of the application have radial selections, where an application can choose a predetermined option, or they can choose “something else” and enter a custom value in a short text field that becomes available. When this data is sent to my base, there becomes two columns, one that populates with a primary selection and a second one that will either be blank or has the applicant’s custom response in it. I’m trying to organize this data into one column that either pulls from the first column if the second column is blank, or chooses the second column if it is not blank. What is the best way to accomplish this? I’m fairly certain I need an IF formula of some sort, but I don’t know enough to know if there is a better way. An example of this can be viewed here: https://airtable.com/shrje2Jcz0ww2BrP1 I can’t share the actual table due to all the personal information we collect but the sample tabl