Formulas

Hey there, In order to better manage my emailing campaigns and some of the auto replies I’m receiving I integrated my sending box with Airtable. To ease the cleaning I would like to add within a column a specific code based on the corpus of the auto reply. Here is an example of the formula I came up with: IF(SEARCH(“yyyy”,Corpus),“Click to validate”,IF(SEARCH(“XXXX”,Corpus),“Nothing”,IF(SEARCH(“aaaa”,Corpus),“Nothing”,IF(SEARCH(“bbb”,Corpus),“Nothing”,IF(SEARCH(“ccc”,Corpus),“Nothing”,IF(SEARCH(“ddd”,Corpus),“Unsubscribe & Check for address”,IF(SEARCH(“eeee”,Corpus),“Unsubscribe & Check for address”,IF(SEARCH(“fff”,Corpus),“Unsubscribe”,IF(SEARCH(“gggg”,Corpus),“Unsubscribe”,IF(SEARCH(“hhhh”,Corpus),“Unsubscribe”,IF(SEARCH(“iii”,Corpus),“Unsubscribe”,IF(SEARCH(“jjj”,Corpus),“Unsubscribe”,IF(SEARCH(“kkkk”,Corpus),“Click to validate”,))))))))))))) but it doesn’t seem to be working. Do you guys have any clue why? I may have to add new criteria moving forward. Thanks a lot in advan

I feel like what I’m trying to do is simple, but I just can’t make it work. I want to remove the text in the brackets. Example below: [SUPV PROD - STAFF] Doe, John [SrDesign/Ani - FL] Doe, Alice [PRO/EDITOR - Ani] Smith, David I want to remove the all the text in the bracket and the brackets themselves. Desired results below: Doe, John Doe, Alice Smith, David The Bracketed text is always in the beginning of the string. Thanks!

I can’t seem to figure out this formula. Here is what I would like to do. Compares today’s date with a specific field date. If today’s date is after field date I want to multiple field A by 10. If today’s date is before field date, then I want to multiple field A by 50. Regardless of which calculation is done above, if the sum of the calculation is 1000 or above, then I want 1000 returned instead. For example, let’s say field A has a value of 50. If today’s date is after the field date, then the value returned would be 500. If however, today’s date was before the field date, then the value returned would be 1000 (even though the calculation would by 50 x 50 = 2500, the 1000 limit would override it). For the life of me, I can’t figure this out. Can anyone help?

I have a table and there in that table 3 fields are there, which contains related id. I tried ND(groupid='recafQ3BugoXXXXX') and AND(FIND('recafQ3BugoXXXXX', ARRAYJOIN(groupid, ','))) still no luck.

Hello all, I’ve created a simple form that our staff can use to log when an employee is absent. This works great as every morning our managers fill out the form for the staff absent/on leave/working remotely etc. I then have a sheet which filters by the date column “today” to display the staff. However if an employee is on leave for let’s say a week, we have put in two date picker fields example: Date From Date To Is there a formula which will filter the employees name as long as the date appears on/between the two date fields. Thanks!

I have a tab with a list of hospitals. Some of them have the tag “COE”. On an other tab, I’m listing the feedback gathered from hospitals. Some feedback came from many different hospitals. So I add the name of the hospitals on the column “requesters” Now, I want to sort my list of feedback, and put on the top the feedback coming from “COE” hospitals. How can I do that? I thought to use a formula to have “COE” on the name of my hospital, and do something like “IF on the requester list there is an hospital with “COE” on it, then check the box” (for example). But I have NO IDEA how do do this… and there is maybe an even easier way to do it… Thank you very much for your help!

Hello, I’m trying to create a bill spreadsheet for myself. I have a column for “Next Due”. I want a field to be labeled what pay period that bill falls into. I get paid bi weekly, so how would be the best way to do this? Basically I want to be able to group my bills by pay period to see how much is owed during that time frame, what is outstanding etc Thank you!

Hi all, Is there a way to sum the number of years passed (as a whole number, rounded down) from a listed date to present? I’m using this for an asset register. There are 4 important columns: A) date of purchase B) original purchase value C) annual depreciation (as a %) D) current value I’m looking to write a formula for current value (D) that will multiply the annual deprectiation % © by the number of years that has passed since the date of purchase (years since A), then multiply that by the original purchase value (B). For example, if 3 years have passed since purchase date, the original price purchase value was $1,000, and the depreciation is 16.67%, it would be ((3*.1667)*1,000), returning a current value of $500.10. How do I get that 3 to caclucate automatically in a formula?

Hi, I am currently trying to use an if formula to display emojis to highlight the status of a license (date field). I currently have:- IF(IS_AFTER(TODAY(), {END DATE}), “ :stop_sign: EXPIRED”, IF(IS_SAME(TODAY(), {END DATE}), “ :calendar: EXPIRES TODAY”, IF(IS_AFTER(TODAY(), DATEADD({END DATE}, -1, ‘week’)), “ :warning: EXPIRES SOON”))) The formula is working fine but I wanted to add ELSE display “ :heavy_check_mark: ACTIVE” but can’t seem to figure it out. Thanks

So I know this shouldn’t be all that difficult, and I get the results for my first two outcomes (Blank and 1 Year), but no years past Year 1 provide an answer. IF({Last Service Date}=BLANK(),BLANK(),IF({Service Frequency}=“1 Year”,DATEADD({Last Service Date}, 365, ‘days’,IF({Service Frequency}=“1.5 Years”,DATEADD({Last Service Date}, 547, ‘days’,IF({Service Frequency}=“2 Years”,DATEADD({Last Service Date}, 730, ‘days’, IF({Service Frequency}=“3 Years”,DATEADD({Last Service Date}, 1095, ‘days’, IF({Service Frequency}=“4 Years”,DATEADD({Last Service Date}, 11460, ‘days’))))))))))) “Last Service Date” is a Calendar Type and “Service Frequency” is a Single Select Type (Single Line Text had same result). In Excel I can use a different formula that works just fine, but is not recognized here

Hello, How would I calculate a repeating date? For example the next second Thursday of the month? Cheers

Hi all, Trying to create a (complicated) IF statement. The idea is that we want to flag if a client hasn’t been contacted in a while, but we have different catagories of contacting. And just becuase the client hasn’t been contacted using one method doesn’t mean they haven’t been contacted using another. Essentially, our contact catagories are tiered and I need a formula that understands that: If the contact date in the ‘Initial Contact’ column is >=5 days from todays date, it should be flagged via the test “Needs Follow Up” UNLESS any of the other contact catagories have dates in them. It looks like this at the moment, but it’s not working: IF(DATETIME_DIFF(TODAY(), {Contact Date}, ‘DAYS’) >=5, “Needs Follow Up!”, IF(DATETIME_DIFF(TODAY(), {TWO WAY DATE}, ‘DAYS’) >=15, ‘Needs Follow Up!’, IF(DATETIME_DIFF(Today(), {Demo Date}, ‘DAYS’) >=30, “Needs Follow Up!”, IF(DATETIME_DIFF(TODAY(), {PROPOSAL DATE}, ‘DAYS’) >=10, “Needs Follow Up!”, “” We have the following catagories

Hey Community, I’ve been trying to extract any kind of value possible from a Multiple Select column (Number). This has proven to be extremely difficult. I tried to use a formula just to get the multiple select in a string format. I was then gonna try to use a location formula or Find formula to put each number in its own column in order to be able to add them all together finally. The biggest difficulty has been that there are 33 digits per cell. For example: Number List (Multiple Select format): 001, 002, 024, 048, 049, 051, 063, 072, 073, 096, 097 –> Number Fx (Formula format): 001, 002, 024, 048, 049, 051, 063, 072, 073, 096, 097 –> One column per number within each comma in the list. Is there any way of doing this? Am I doing this completely wrong? I’m open to any advice. Thanks a lot

Relatively new to Airtable. I’d like to be able to calculate the total number of workdays in a given month for employees who work six days/week in a museum, so Tuesday through Saturday. My approach has been to take the total number of days in a given month using DATETIME_DIFF and then try to figure out a way to count just the Sundays in a given month and subtract these from that total. Ideally, I could just define WORKDAY_DIFF parameters (in fact, it seems obvious to me that I should be able to, but I don’t see any reference to this/any way to do it . . . just the outdated(?) assumption that a workweek comprises one set of values).

Calculate lookup values against a calculated column and then sum. Example: Lookup results * calculated_column 14,15,17 * 9 SUM((14 * 9)+(15 * 9)+(17 * 9)) How can I do this?

I need some help with creating a formula that can calculate the price, depending on which week the purchase was made. We’re selling weekly courses from February till July (23 weeks in total, €15,- each) The full program is €345,- When customers book a year in advance they get a €20 discount. I got up to the point where I made a formula that works and kinda goes like this: IF weeknumber < 5, the price is €345 IF weeknumber > 28 the price is €325 Since people can join our courses at any giving time. I would like to add a formula that calculates the price depending on the week they joined. I’m looking for a formula that kinda goes like this: IF weeknumber is between 6 and 27, the price is €420 - (weeknumber x €15) Unfortunatly my coding isn’t that good yet… If anyone could help me out combining all of this in one formula, I’d feel blessed!

Hi there I’m building out a detailed budgeting base, which includes a section for documenting when we expect our income to hit our accounts. My husband gets paid every 14 days (fortnightly), for example. Is there a formula that would let me document this without doing every bit of income manually? If I was using Google Sheets, I’d be able to have a ‘date +14’ formula, but I can’t figure out how to do that in AirTable. Thanks for your help in advance!

Hey, So I have three tables, Sales, Purchases & Inventory. I have 3 items A, B, C. In each sale, a customer can buy all three, just two or one of the items in whatever quantity they wish. On each day I make purchases of varying quantities of A, B & C. I cannot figure out how to use rollup to give me a total of purchases, sales and purchases - sales in my inventory. Images of my three tables below.

So I have a “Members” table with “Name” and “Email” as fields I also have an Activity form for activity checks as “New Members” table. They put in their email in, and submit the form. This is then placed in the “Email” field. I want to display their name in the “Name” field of the “New Members” table using lookup. I know I can use the link to another record field, but that isn’t automatic. Is there a way to take the table’s “Email” and set a value to the “Name” field based on the email that was provided (executing a lookup to the “Members” table)

We have two columns that we need added to a third sum column. Our first column is our Admit Date for people using our program and the second is Exit Date for when they leave the program. We need our Admit Date and Exit Date columns to populate to a total days in care. Right now we have the formula as: SUM({Exit Date}-{Admit Date}) but it is giving us a message of NaN when an admit and exit date are plugged in.

I have a formula for calculating when I can plant seeds, according to the LFD (last frost date), which is and will remain 3/29/2020. Weeks is how many weeks before the LFD I can plant the seeds. The formula: DATEADD({LFD},-{Weeks Start},‘weeks’) Currently I have a whole column for LFD that is set to autofill 3/29/2020 into every row, of which I have 350+. Instead of this whole column for LFD, is there a way I can somehow reference this number one time within a formula, or within one cell somewhere instead of a column of 350 rows with the same number?

Hello Everyone and forgive if this question has been answered in a previous Post. Quick background. I created a StartDate using the Date field. Then I created a second column to enter the number of days that the project is expected to be completed. This field is a ‘Number’ field called Duration. Then I created a third field called EndDate which is a formula field where it calculates the number of days assigned to the project with the Start Date to acquire the End Date value. The formula is DATEADD(StartDate, Number, ‘day’) First question, is there a way to exclude ‘weekends’ so number of actual days would be business days? Second challenge - Using the EndDate information, if the project goes past the EndDate by more than 2 business days, I would like that to be populated in a PastDue field where it would state: ‘Past Due by 2 Days’. I would think this has to be tied into using the Computer Clock where it compares the value in the EndDate to the Computer Clock Date. This is my main ques

I have a formula to calculate how many plants I can fit in a 10x12" square. My formula goes: 10/Spacing * 12/Spacing. (Spacing is a column where I record the number of inches of distance a plant needs). The formula works, except for in two instances- 1. When I have not filled out the spacing column yet, the formula produces ‘INFINITY’ as the result. I’d like it to return a blank field instead of returning ‘INFINITY’. 2. When the plant requires two 10x12" squares (i.e. when the Spacing is 24"), the formula produces 0. I would like that changed to 2, or even better, an actual calculation with a decimal, like 1.5, 2.5, etc. Can anyone help? I’ve searched similar issues but can’t find anything that helps me.

Hi All I’ve been working on a simple time off/vacation tracker. My company has a service they use, but it is really difficult to interpret the data displayed. My company allows our time off to be fully available at your anniversary date of hire. We do not use accrual of time as some companies do. I’ve almost hit what I’m looking for, but need one more piece to button it up. I basically have two tables. 1 PTO Data holds the critical criteria - PTO types, alloted, planned, balance etc. There is a column which is linked to the 2nd table pulling in the time off dates requested Linked Calendar - is a table, but I display in a calendar view and I simply select a date in the calendar for the time off. I want the ability to change the time period of how balance is calculated off of the dates requested. Not sure of the best way to do this. Right now I have two columns…PTO Begins and PTO Ends…this is my range of when my PTO is good for the year. Any ideas what I could do when the new year rol

Hello everyone, I order to properly store geographic coordinates in Airtable, I need to create a formula to transform a field like “Address: Province: Country: Longitude: -1.4934866Latitude: 12.3614676” into “12.3614676, -1.4934866” I have tried a few formulas but I systematically failed. Anyone could give an hand on this please? I would really appreaciate. Warm regards from Burkina Faso Franck