Formulas

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 req

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

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

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

Hi, maybe I its to obvious and I don’t get it - but how can I work with the outcome of a formula field? Every time I build formulas with which I want to “work further” with the outcome of another formula field there is no output (blank). Any idea how to make that possible? Thanks in advance Onur

Hi, how to use a XOR formula with 3 and 5 arguments (with real world use cases)? I’m trying to understand how i can apply this in real life

Hi all, I may be missing something in which case apols! I have two fields: an integer field and checkbox field. I wish to set the checkbox field if the integer field is >x. I can see how to simulate this using a formula field formatted as text and a ‘tick emoji’, but this doesn’t work for me as I need to link my base to an external system that uses airtable checkbox fields to construct filters. Alternatively, would be nice to flag a feature request for the formatting option ‘Checkbox’ for formula fields where the output is boolean.

Hi guys Thank you for your help! and sorry for the noob question, but i’m struggling with below scenario: i have a service provider that services multiple branches The link on AT allows for multiple selections and all is good. Using the fantastic On2AIr widget (subselect) i was able to easily pull all the providers affiliated with a specific branch by using : Child Search Field with “Branch Affiliation::Branch ID” logic When a provider has only one branch affiliation results come up nicely, but once there are more than that no records are found. So the questions is: What do i need to change in “Branch Affiliation::Branch ID” logic to allow for the multiple affiliation ? thank you!

I have a holiday calendar that I want to plan for future years. Instead of typing in future dates, I would like a formula to calculate it automatically. I would like a formula that if the date type is fixed, add one year to 2020 date. I also am different and use the mmmm dd, yyyy format. Is there anyone that can help?

Hi, can airtable link a record to another one in the same table ? making recursive fields , or tree like fields or hierarchical structure ? like category sub category sub sub category ?

I’m looking to use Airtable as a full project management solution for my company. But one of the requirements is that projects must adhere to a certain project numbering convention: XXXX-0000-X XXXX is a 4-letter code, specific to each of our clients (1000+) 0000 is a 4-digit number, which auto-increments starting at 0001 for each client (not globally, each client counts up) X is one letter, based on the project type Our current system generates this number automatically upon entering a new project. Let’s say that my client’s name is ABC Tool Company. They already have 96 projects in our system. I’m going to enter a graphic design job for them. The client’s unique client code is ABCT. The next project number would be 0097. The suffix for a graphic design project is G. All I have to enter on my project request form is the client’s name and the project type, and the system would automatically generate this code: ABCT-0097-G