Formulas

If my muliselect field has 25 things in it.I want to find Apples and return apples in the formula field, and I want to find Pears and return Pears to the formula field and if that multiselect field finds both i want it to return Error and if none i want it to return a blankthanks for your help!_Mary

I am such a novice at this, I hope I can accurately explain the problem. My goal for this to combine the sales and listing data from online source with my product data that I need to help me understand what is working and what designs I need to focus more on. Within a single Base, I have 2 tables with 2 sets of Data that are not currently linked. I will call them TABLE A & TABLE B. TABLE A - contains rows of listings for online products. Each row contains a single product with various details about the listing. This data is updated from an external source of my online vendor. For Example, A1 Title / A2 Listing Copy / A3 Number of Sales / A4 Product type / A5 UPC / etc… TABLE B - Contains my information about the online product. This is info and categories that I have created to help me track my designs and organize them for my use. For Example, B1 Design File Number / B2 Niche / B3 Thumbnail Image / B4 Ad Campaigns… etc I understand that I can link individual fields from one table

I have the following formula in a field names "Status":IF({Current Performance}=5,"5: Excellent",IF({Current Performance}>=4,"4: Good",IF({Current Performance}>=3,"3: Fair",IF({Current Performance}>=2,"2: Poor",IF({Current Performance}<2,"1: Very Poor")))))The Current Performance field is a lookup field set to show the last (most recent) item from the related table.If the Current Performance field is blank (no record in the lookup table) the formula will return "Very Poor" even though there is no number in the Current Performance field. I've tried various iterations of the formula, but can't seem to make it behave properly. Essentially, if the Current Performance lookup field is empty there should be no corresponding record in the Status formula field. Any ideas/suggestions?

Formula for 1st Column:IF(IS_AFTER(TODAY(), {MOC Requested}),  DATETIME_DIFF(TODAY(), {MOC Requested}, 'days')) Formula for 2nd Column:IF(IS_AFTER(TODAY(), {MOC Requested}),  DATETIME_DIFF({MOC Submitted Date (from Memo of Correction)}, {MOC Requested}, 'days')) What I would like to have is a formula (preferably in 1 column) that calculates the number of days between the 3rd column and today unless a date has been entered into the 4th column. Then I want it to show the number of days between column 3 and column 4. I can't find a way to combine my 2 formulas

Hey there, I'm trying to gain access to the Download Attachment URL (without using the API) so I can upload images from the Attachment of a record to a social media platform. I've tried using a workflow of AirTable --> Zapier --> Social Media Platform, but I can't find a way to get the image in my AirTable's attachment field to the social media platform. Let me know if you know how to help! PS -- The reason I don't want to use the API is that I saw AirTable's recent notice that they are disabling API access at the end of this year. 

I have a table with work months like here: I'm trying to calculate the start and end datetimes of the month but the formula is giving inconsistent answers. It's correct for some months but there's an error in End Dates for March, May, July, October and December.Here is the formula I'm using for Start and End dates:Start Date:DATEADD(DATETIME_PARSE(YEAR({Work Month}) & "-" & MONTH({Work Month}) & "-01", 'YYYY-MM-DD'),-7,'hour') End Date:DATEADD(DATEADD({Start Date},1,'month'),-1,'second')What am I doing wrong?

Hi I really need help for this and I have a deadline. So basically, I was wondering if there's a way that I can create a formula that add's up the number of time "" is used across all fields and shows the result as a number in the "s Used" field.. For example for the highlighted square, since "" was only used once, it would appear as 1. But for the cell above, because "" was used twice across all fields it would be entered as 2. thanks so much! 🙂

Hi ,I am creating an airtable automation for recurring task for weekly, daily, monthly, 3 times per week and twice a month  In the next to perform date i used this formula DATEADD({Due Date},SWITCH(Frequency,'DAILY',1,'WEEKLY',7,'MONTHLY',30,'TWICE A MONTH',15,'3 TIMES PER WEEK',3),'days') It works perfectly well for daily, weekly and monthly, twice a month but it doesn't work for  3 times per week i need (Monday, Thursday and Sunday)can you please help me with the formula?

Tracking nurse staff schedules with doctors.Column 1 lists nurse staffs, column 2 the doctor engaged with that staff, column 3 = # of patientsFor example, column 1 may have the following fields:A1 = Betty, Joel, CindyA2 = Suzanne, Betty, MikeA3 = Cindy, Beatrice, RosariaB1 = Dr RodriguezB2 = Dr BlakeB3 = Dr BakerThe goal is to be able to add up patients between a single nurse w doctor combination, i.e. Betty with Dr Rodrigueza normal CONCATENATE of A1 and B1 will say Betty, Joel, Cindy with Dr Rodriguezanyway to be able to recognize all 3 combinations?I have a junction table with a CONCATENATE of Betty with Dr Rodriguez but I don't know how to then go grab and sum the # of patients anytime where Betty is true in column A and Dr Rodriguez is true in column B.

Hi,I am trying to write a formula to write the wider region that the project is related to, e.g. if it's in Germany, it's in EMEA. This formula works; however, I now want it to be able to list multiple regions for projects that are taking place in two different countries that aren't in the same region.e.g. a project that is in Germany and in Canada should show 'EMEA, CAN'. Currently it just shows 'EMEA', not both. Please could someone suggest how to change this formula to print all that apply? (Tagging @Databaser who helped with my previous question in case they can help, "Complex Nested IF AND statement (dates and regions)")Thanks,  IF( REGEX_MATCH({Division}, "UK|ROI|France|Spain|Italy|Greece|Poland|Germany|Portugal|Belgium|Netherlands|Denmark"), "EMEA", IF( REGEX_MATCH({Division}, "Thailand|Indonesia|Malaysia"), "SEA", IF( REGEX_MATCH({Division}, "New Zealand|Australia"), "AUS/NZ", IF( REGEX_MATCH({Division}, "USA"), "NAM", IF( REGEX_MATCH

Can I set-up AirTable to use the German number format which uses comma (,) for decimal place and dot(.) for thousand separator ? I mostly hace to create reports which require german formatting. It’s a real pain to download in excel and reformat it. Thanks

Hi, I have a column where the tax of products is being calculated and in the field I have the formula to calculate the tax since it is the same for every product that can be taxed. I have certain products that do not include tax, so how to do write out a formula, or override my current one so these products do not automatically calculate the tax. Thanks!

Hello, I just started with Airtable, and to be able to use it, I need solve this problem. I am having a database of volunteers on a national level. I would like to make them write their post number when they apply, and in the base allocate a decided municipality and local chapter. This information we gather from a Typeform. I have matched the options in my Typeform and Base/Field as a single choice. It works well until den, but how to make it work in Airtable is the next step. This is not in the US. There are around 5000 post numbers that are going to be distribute in 15 "municipalities". Some chapters allocate more than one municipality.Is the single choice the best one for this purpose? Would an IF formula work?What is the most effective way to do this with that amount of data? 

Hi there - I've set up a table that I want to use to 'score' certain projects by quarter. I.e. at the end of Q1, Project 1 received a 50 score, Project 2 received an 80 score, Project 3 received a 100 score, so on and so forth. Currently in my table, the projects are separate records, and I have four columns - Latest Score, Q1 Score, Q2 Score, Q3 Score, and Q4 Score. They are all number fields. I've inserted a photo below.Is there a formula that would enable the Latest Score field to automatically reflect the current quarter we were in without me having to update it manually? Thank you!

Hello,I'm working with the following table in Airtable:My goal is to create another table that simplifies the data. Specifically, I want to generate a single row for each unique ID, where all the text from the 5th field is concatenated into a single cell. Here's an example of the desired output:"Consider installing a longer bath handle. Doesn't completely rest on the bathtub.""Repair chipped tile due to shower column installation. Replace the shower column with a wider one to conceal the defect. Alternatively, replace the tile.""Adjust bathroom cabinet drawer."Important Note: I have thousands of IDs, each with multiple text entries like these. I need to concatenate them to streamline the process of sending email alerts. I've tried various formulas, but I'm a beginner in Airtable.I appreciate any advice or guidance you can provide. Thank you in advance !

Hi All,how would I amend the formula below to include Working Days of Monday to Friday and working hours of 9am to 5:15 pm in the out put? INT(DATETIME_DIFF(NOW(), {Credit decision date}, 'hours')/24)&" Days "&MOD(DATETIME_DIFF(NOW(), {Credit decision date}, 'hours'), 24)&" Hours "&MOD(DATETIME_DIFF(NOW(), {Credit decision date}, 'minutes'), 60)&" Minutes" 

I have a complicated string of formulas that is setting due dates. The short of it is I have a "complete" column (checkmark) and "Date completed" (static field). A task is completed and marked with a checkmark in complete where it stays visible all day, then at 11PM, today's date is added to "date complete" which filters it out.  The date complete is the base for a series of formulas in another table which basically determine when to reopen the task as being due again. I've been struggling to figure out why my formulas aren't behaving as expected and I think it comes down to timezone. The most granular failure I can find is a plain formula for "today" - that is the whole formula just "today()" ... the automation pulls the data from that column to drop into date completed and "today" is wrong. I've tried manually setting all date fields to CST, I've tried leaving them all as the standard GMT, I've tried "use same time zone" in formatting and I've used the "set t

I have a table that records all of my business leads.I have another table that functions as a report, to provide a summary of all those leads, how much they cost, when they were generated, so on and so forth.These tables are linkedCreated Date column & Created Month column is automated to fill in this linking field when a record is created in the business leads table, to populate the reportHere's the problem, the business looks at weekly data from Fridays to Thursdays. How can I automate the data so that we can look at the report in the week range that is specific to our business?Currently, what I can think of is to create a formula field, {Week Range}The formula would look something like this:  DATETIME_FORMAT( IF(DATETIME_FORMAT(TODAY(), 'dddd') = 'Monday', DATEADD(TODAY(), -3, 'days'), IF(DATETIME_FORMAT(TODAY(), 'dddd') = 'Tuesday', DATEADD(TODAY(), -4, 'days'), IF(DATETIME_FORMAT(TODAY(), 'dddd') = 'Wednesday', DATEADD(TODAY(), -5, 'days'), IF(

Hi folks,Can anyone help my failing Friday brain out please? I have a lookup field pulling through a maximum of 3 payment dates from a payments table. I need to break those dates down across 3 columns; earliest date, middle date and latest date. I have a MIN and MAX in place displaying the earliest and latest values, it's the middle date value I cannot figure out. I don't suppose anyone can solve this with a formula can they? I'm hoping to keep the workarounds to a minimum if possible.Much virtual thanks in advance!

In a lookup field of a record linked to many records, I have a large string of dates. They are in no particular order.e.g:2024-01-09 4:30pm, 2024-01-04 7:00pm, 2024-01-08 2:30pm, 2024-02-09 11:00am, 2024-01-12 6:00pm, 2024-01-12 1:30pm, 2024-01-09 12:30pm, 2024-01-15 5:30pm, 2024-01-12 12:00pm, 2024-01-10 12:00pm, 2024-01-16 5:30pm, 2024-01-18 12:00pmHow would I use a formula to refer to a separate date field(date only, no time), and create a new string with only dates from the large string that match the specified date?e.g. If I specified "2024-01-09", for the data above this formula should return "2024-01-09 4:30pm, 2024-02-09 11:00am,  2024-01-09 12:30pm"Thanks!

I am trying to use this to automatically add 15 or 5 days to the contract date. The “calculation” field is an IF statement to calculate which amount of days to add. BUT I cannot figure out how to get the formula to add the days and give me a new date.

Hi there,I'm trying to create a field in a table (say Table B), where its output is takes into consideration a condition based on a field in Table B, rather than the original table it is rolling up from (which I know Airtable has a simple filter for).In my "Scheduling" table, I want to be able to enter a single date, or a start and end date. This field will look at all Shoots linked to the Team Member that is linked to this record, and spit back out a list of Shoots that have a start and end date/time that is within the dates I specified in the Scheduling table. [Context: This table is linked to a form, and its purpose is to notify a Team Member when their shoots have been scheduled and a short summary of their earliest and latest by email, triggered by an automated button in Interfaces.]The image below shows the structure I've got going now. Thank you!

Hello all,Hope you all had a good holiday season.I have looked, but can't find anything around this, which I think is simple.  Most of what I am seeing on here looks more complicated than it should be.I have a Date Formula field that I would like to populate with Today's date if a box is checked.  I can get that part working, but the rest of the fields show the #ERROR! message.  I just want to hid the message and show nothing.This is what I currently have:Thank you for any help,Matt 

Hi All, hoping someone can help with this.I have a database of clients for content creation, and there are various deadlines for each piece of content we write (for example publish due date, writer instructions due date, draft due date, etc.) We manually enter a publish due date and all other due dates are calculated based on that date using these formulas: DATEADD({Publish due},-10,'days') or DATEADD({Publish due},-5,'days') etc.Recently we've taken on a number of clients from new marketing sources who will have custom deadlines. We have added a column which specifies where the client came from. I'd like to have the dates calculated as above, but ONLY if the client came from Source A.  If the client came from any other source, the field should be left as a blank date field so we can manually enter dates. Is this possible, and if so what would it look like?Added example:So IF client source = sourceA, then DATEADD({Publish due},-10,'days')but if the source does

I just realised this simple formula, on most fields does not work . Test was done on a Single-Line field. IF(test1 != BLANK(), "Not Blank", "Blank") This will always return "Not Blank".  A workaround seems to be to do the below: IF(Not(test1 = BLANK()), "Not Blank", "Blank") Any idea why this is so? Or is there a simpler work to check for 'Not Blank/Empty'?