Formulas

I am attempting to create a formula that finds the latest lastModified Date from a group of records. The group is a single select column. = greatest lastModified date of “group1” Thanks

I have a formula where if condition 1 and condition 2 are true then show data from another column. What I want to do is have it show data from two different columns if the conditions are true. Here’s the formula: IF(AND({Contract Ended?}= “No”,({Exclusive?}=“Exclusive”)),Rights, BLANK()) What I want to do is add another column - Holdback Rights - to the formula so the data in both shows up in the new column. It seems like it should be simple but no matter what I do it won’t show the data in both. Thanks in advance! Jennifer & Mary

Hello AT community! I have a question regarding creating formulas. In my base I have 2 tables: a recipe table with each ingredient in its own field, linked to my ingredient table. In the ingredients table I have several single select fields for each allergen that a particular ingredient contains. So let’s say I want to summarize the allergens in my recipe for lemon meringue pie. I’m using a frozen pie crust that contains dairy and wheat, allergens which are indicated as “yes” in their respective fields in my ingredient table, plus eggs (another allergen). Back in the recipe table, I would like to summarize all the allergens present in my recipe for lemon meringue pie, so I could easily look at the recipe and see that it contains eggs, dairy and wheat without having to go back to the ingredient table and look at each ingredient individually. Is it possible to create a formula to summarize all the allergens present in my recipe, within the recipe table? I’m an AT newbie so not entirely s

Hello, can I join three fields un one? I want to mix three fields in one automatically. Is that possible? I dont know wich rule I have to follow. In A I have different categories (A) which belong to another column (B); and I want to mix in a third field the categories which are the same. Is that possible? A: blue B=house; C= Carla=blue+ 13yearsold D: I want to mix in other names in C, the words that are the same. thank you Cristina

Hi all, this is my first post on the Airtable community. :slightly_smiling_face: I have a base that I use to track my FB groups activity. So I have a column with a checkbox to mark when I’ve completed something in that group. What I’d like to do is have those checkmarks automatically clear off at the beginning of each new week. Is that possible with a formula? If so, can someone point me in the right direction of where to get started? Thanks so much for any help! Valerie

Hi! I’m new to Airtable, so what I want to do must be something very easy, but I can’t figure it out. Table A Employee Name | Current Company | *Other Companies Mark | Facebook | Amazon, Google Sergey | Google | Amazon, Facebook Jeff | Amazon | Facebook, Google The above is Table A, where it has a field Current Company. This field is linked to Table B: Table B Company Name | Employee Name Google | Sergey Facebook | Mark Amazon | Jeff Can I make *Other Company field in Table A automatically populated by Formula or something else?

Hi all, I’m quite new to Airtable and I’m trying to transition my logical thinking to relational databases from Excel but i’m struggling with something… I’ve recently imported an Excel worksheet into Airtable that contained form data and formulas on the same worksheet. In Airtable, I have been creating the tables I need and linking them to the main Data table where possible. In the Excel file, there is a form that I have also replicated in Airtable. The use case is that the form is filled out by a user and once submitted, a new line is entered into the Data table. The first 20 fields/columns within the Data table are for the inputted data from the form. The next 10 fields/columns are supposed to be calculations based on some of the data from the form and some data from other tables (vlookup from Excel). I am now trying to replicate the formulas that the Excel worksheet had (Vlookup) in Airtable but I don’t know how to use the value of another table in a formula of the current table. So

I run a pet relocation company and we have many clients who hold their pet files with us in case of a future move. We need a formula that red flags when a vaccination expiry date is imminent (e.g. in 14 days, in 28 days, etc). Really struggling with how we can incorporate this into airtable.

I run a pet relocation company and when preparing pet travel paperwork we need to ensure that there are a certain number of days between the dob and a vaccination, or travel date, etc. We need a formula that red flags when one date is not within/above a specific range of another date (or the current date). Thank you in advance

The formulas are calculating in real time, while I’m typing values in another field. Is there a way to set it to stop behaving like that and calculate only after I type Return?

Hi guys! I need help figuring this one out. I was approaching the problem using MAX and MIN formulas, but that doesn’t help me sorting the 5 possible dates I have (just 2). Problem goes like this: I’m working a logistic solution. The operator works by areas, visiting a zone one day of the week. But during peak seasons, he may decide to visit the area every day of the week (hence 5 possible days). My table calculates the next possible visit day from the autodate it assigns when a new request of service is submitted. So, if the order is posted on a monday and the operator visits the client’s area on tuesday, is the next day. But if it’s on mondays, is 7 days from the request date. I hope you’re following me. Calculating which date it will be for that area is easy, with: IF({retiro 1}-WEEKDAY(Autofecha)<=0,DATEADD(Autofecha,{retiro 1}-(WEEKDAY(Autofecha))+7,‘days’),DATEADD(Autofecha,{retiro 1}-(WEEKDAY(Autofecha)),‘days’)). where ‘Autofecha’ is request day, and ‘retiro 1’ is the day of

Hello Everyone, New to Airtable and enjoying it so far. I couldn’t find help on this and I am guessing there’s a better way to do this. Appreciate your help in advance. I want to followup in 10 days if a checkbox is checked, or 30 days if it isn’t. Here is an elaboration of the condition followed by the formula that gives an error: If the checkbox {A} is checked, {B} < 10 days “Looks Good” else “Needs Check In!”. However, if the checkbox is not checked, {B} < 30 days “Looks Good” else “Needs Check In!” Here is the formula that gives me an error: If ({A} = 1, IF(DATETIME_DIFF(TODAY(), {B}, ‘days’) < 10, “Looks Good”, “Needs Check In!”), IF(DATETIME_DIFF(TODAY(), {B}, ‘days’) < 30, “Looks Good”, “Needs Check In!”)) Thanks, Sunny

Hi, I have a subscription startup and we are using airtable as our backbone. In one table I have all the information of my customers with the usual information: name, address, email, phone number, etc. A couple of weeks ago we did a digital marketing campaign using Instagram where we gave a present to the health professionals to celebrate them and thank thank them for their service in this pandemic. In another table I have the instagram user name, name, phone number and address. Some of them turn to customers and now I do not have a simple way to check who are the new customers that decided to purchase a subscription. Is there any way to cross check both tables and find those that received a gift and are now customers? Adding a label or anything to identify them? Thanks in advance. Hope someone can help me.

I have 2 tables. One is for form submissions where people attending trainings enter their information and the numeric ID of the training they attended. The second table is key value pairs matching a training ID to its name. I need to look for the ID that matches the number the user entered and if found display the training name. If not found flag the record for followup. I will not have the people filling out the form select from the list of trainings as it will eventually be thousands of lines long.

I have a Main Database Table. Within the main database I have created tab for an event. How would I automatically fill in the next column with the company name after I’ve enter Attendee name from the main database?

I am wanting to know if there is a formula that can make a record use ALL CAPS like the picture. Ideally I would like for my First Name and Last Name inputs not to be in ALL CAPS, but would would like to have a formulas that mimics this style for the records. Thank you in advance for anyone who has a suggestion!

Hello, I have a field that based on a closing date of a sale, converts the closing date to the ‘dddd’ so as i can see which day of the week i sell most. If I have not closed the status of a product it shows #error in the value. I’d like tobe empty. DATETIME_FORMAT({Closed Date},‘dddd’)

Hi Airtable Community, A newbie here, so apologies if this has been answered 1,000 times - just not sure what to search for… Essentially, we have a form on our website which is a CV submission for jobs. When this form is completed it populates the fields that we have in our Base. However, we have some additional fields and I was wondering if there’s a way for these to be populated when the form is complete without having to ask them on the form? When the form is complete, we basically wish for the Base to complete the ‘Source’ field as ‘Website’, as all other entries will be manually inputted. If that doesn’t make sense I do apologise, but would greatly appreciate any help. Many thanks, Thomas

Dear wizards of Airtable, We run a program for kids this summer. Within a timespan of 4 weeks they can choose which day they’d like to join us. On their last day, they get a “good bye gift”. Unfortunatly this is easily forgotten. Since we run our registration and administration thru airtable I was hoping for you guys might now a solution for this. I’m looking for a formula that kinda goes like this: IF: only on the last [sign up date] equals [today] status should change to, “good bye gift” For example: Johny is with us on monday, 4th, 5th and 6th of July. Only on July the 6th his status should change (not the other 2 days). The “sign up date” field is a multiple-select field. I need a formula (and probably a little detour) that understands which date is the latest date in the sequence. Thanks in advance!

Hi there, I’ve taken to Airtable recently to catalog all my acquired clients. I have a single select field called ‘$Flow’ that tells when the current status of the client in the sales funnel, for example: Made contact, Talking, Email, Won, and Lost. What I want is a formula that can tell me exactly when I win a client over. I.E. when I turn the single select field from any other option to ‘WON’ the date (and time, if possible) should be captured in the formula column. I used the formula: IF({$Flow}=“Won”, NOW()) But the date always resets every time I reopened the table, not when I originally turned the option to WON. Does anyone have any suggestions that could help? Thanks Yashwant.

Thank you for taking your time to look at this. The company I work for offers services that are modular, so not all clients are going to request the same services. The client will pick and choose the services that they need. To help automate the quotes we give for our pricing, I’m trying to use a formula field to calculate the final cost, however I’m running into problems. As of right now, I want to use multiple single select fields with the name of the service with the options of selecting YES or NO I’ve written up the following formula. IF({Service 1}=“Yes”,13,0) I can assign a dollar amount to the service.The problem I’m running into, is getting all the services to add together to give one total amount. What I would like to do is, 1. Give each service a dollar amount. 2. Then using that number, calculate all the services the client has requested into one final total that uses the currency format. Lets pretend that there are 3 services. Service 1 is $13. Service 2 is $67. Service 3

Hello, I would really appreciate some help here. I have a big list of client birthdays, the majority of them are partial dates (DD/MM), but some are complete dates (DD/MM/YYYY). Now, I have 3 fields: Date of birth This is a single line text field because I can’t enter partial dates in a date field Age This is a formula field I use to get their age when I do have their full date of birth IF({Date of birth}, DATETIME_DIFF(TODAY(), {Date of birth}, ‘years’)) Birthday This is a formula field I’m using to strip away the year of complete dates and make both, partial and complete dates, recurring dates that I can see in the calendar view IF({Date of birth}, DATETIME_PARSE(DATETIME_FORMAT({Date of birth},‘MM/DD’)&"/"&YEAR(NOW()))) The problems I’m having are the following: We use the European date format (DD/MM/YYYY) here, but when I enter a partial date in the date of birth field (single line text field), say, 1/6 (June 1st), the birthday field (date field, European date for

Hi, I found a script enabling a percentage count: https://airtable.com/shr1uUxBtpin68DsN/tbl83MENluhVCm20y/viwuf0HQjtKfYVvmb and I would like to adapt it by counting the number of cells completed, in place of the direct input in the “Current” cell. What type of formula can count the number of cells completed for a specific row? Thanks for your help.

I’ve been trying but it seems like it’s not possible.

Hello! I am having trouble with what I think would be an IF formula, but maybe a switch? These are my fields: Product Unit Price Shipping Cost We have our shipping costs listed as a line item and it will show in the product column. I need to extract the shipping cost for our P/L down the road. What I am looking to do is: IF the product is “shipping” , I want to show the unit cost in the Shipping Cost field… Can’t quite figure it out…Thank you for your help