Formulas

Hi all!I was wondering why ARRAYJOIN(values, "\n") is not working in my base.The only way I have been able to achieve line breaks is by creating a formula field.I added some screenshots!Thanks!

Hello!So i was wondering if it was possible to create a formula (or combine formulas) in order to use the data from one date field and add 7 days to it in another. and then filter it if the project status matches certain conditions.I got the first part to work using: DATEADD({Project Date}, 7, "days") which worked perfectly,  but im not sure if its possible to then have it say "not needed" or even have it be blank if the project type matches certain conditions and still show the date if it matches other conditions! I also tried this:IF(OR({Project Type} = "Wedding",{Project Type} = "Elopement"),DATEADD({Project Date}, 7, "days"),IF(AND(OR({Project Type} = "Couples",{Project Type} = "Family"),"Not Needed","Not Needed"))) which kinda worked but it only filled in the wedding date status and not of the other options. Let me know if that makes sense and if you can help please 🙂

Hello,I have a formula column where I dynamically build a string of characters. Everything is fine to build the new string.However, when I copy and paste (Command-C) the cell, I end up with a character " added at the beginning and end of the new string but also, by consequence, in the middle of my content if it detects a " character.It automatically doubles it! GrrrHow can I eliminate this problem? Thanks for your help. I am getting crazy.

I've HTML Code to use inside formula to insert virables inside the HTML code then export it as final HTML Code.. the problem is when i tried to copy the field it add some extra character (")...Here's the formula code:"<div style='position: fixed; top: 0; left: 0; width: 50%; height: 100%; opacity: 0.6; z-index: -1; background-image: url(\\"https://image-cdn-p.azureedge.net/offer-image/ARABGG/202304022245344534.png\\"); background-repeat: no-repeat; background-position:left;background-size:100% 100%;'></div>" & "<div style='position: fixed; top: 0; right: 0; width: 50%; height: 100%; opacity: 0.6; z-index:-1;background-image:url(\\"https://image-cdn-p.azureedge.net/offer-image/ARABGG/20230402224407447.png\\");background-repeat:no-repeat;background-position:right;background-size:100%100%;'></div>" & "<div style='padding :5px;background :rgba(255,255,255,0.1);border-radius :5px;\\r\nbackdrop-filter :blur(5px);box-shadow :0 2px3px rgba(0,0,0,0.1);'>" &

What could one do to include data validation in the conditional logic; e.g.: I am creating an internship application form and I want the applicant to select out of the list of employers. Once done, they can select the relevant internship position, nothing else. And also, how could we include the employer's email in it so that each entry is also sent to the respective employer?

HiIs it possible to use a single select field to populate the options of a multi-select field that is in a separate table?Thank you for your help,Collin 

I’ve got a bit of a puzzle which needs cracking here. I have a table in which each record is used to track jobs in my business. I have a Single Select field which tracks the job status of each record. Job status examples: Job Accepted > Worker > Reviewer > Manager > Complete I need to record the amount of time that a record spends in each status. I’m looking for something like this: Formula field - Time spent with Worker: 100 minutes Formula field - Time spent with Reviewer: 25 minutes Formula field - Time spent with Manager: 15 minutes The catch: records will sometimes move backwards and forwards through the statuses. i.e., record status will go from Worker > Reviewer > Worker > Reviewer > Manager. I can’t figure out how to make this happen (or if it is even possible). Can anyone think of a way to do this? I’d love to hear an alternate approach if there is one!

I have a "Person" table that has birth date. On another table, I lookup that birthdate to find the person's current age when "Event" happened. Next year, a new "Event" will have different age for the person (as they are a year older). But I need to know what age they were at each "Event"So how do I create a column of ages that are dynamic on entry, but don't change once they are entered?

IF(AND({State (from Government Body)} = 'Iowa',{Public Records Received (Yes/No)}= 'No',{Proof of Service (Differential - Proof of Service+(x)Days)}),IF({Proof of Service (Differential - Proof of Service+(x)Days)} <= 20,"Pending",IF(AND({Proof of Service (Differential - Proof of Service+(x)Days)} > 20,{Proof of Service (Differential - Proof of Service+(x)Days)} <= 50),"Delinquent",IF(AND({Proof of Service (Differential - Proof of Service+(x)Days)} > 50,{Proof of Service (Differential - Proof of Service+(x)Days)} < 60),"Complaint Submitted to Iowa Public Information Board",IF({Proof of Service (Differential - Proof of Service+(x)Days)} > 60,"Government Body Failure")))),IF(OR({Public Records Received (Yes/No)},{Page Count} = 0;"Completed"))) I am teaching myself and/or conditions for a status formula and I am requesting assistance with adding an or condition to the "Completed" section of the code.  The rest functions as desired.  

I have a table that tracks subscriptions for a specific product add-on. It tracks subscription movement for this product based on the unique account business id. Every time a user subscribes, upgrades, downgrades, or churns it adds a record with the new quota, business id, date of upgrade, month of upgrade, and the expansion or contraction MRR. A churn is recorded very similarly to a downgrade or contraction record so I'm trying to come up with a way to tell the difference for reporting purposes.  Basically if the business id doesn't occur in the list again, has a 0 quota, and a negative MRR then it's a churn. If it does occur again but has a 0 quota and negative MRR then it is contraction and not a churn.I'd like to add a formula field in Airtable that based on the instance of the business id it labels the record with either New subscription (if it's the first instance of the business id in the table), expansion (if it's after the first instance of the business id AND h

Hi there, thanks for taking the time to read my question! New to Airtable and it's formulas - what I'm trying to do is create a simple Hours of Operation setup where a user can select the days of the week a business is open (stored in Open Days), add the open hours for each day, then combine the values to show the complete hours of operation (combined in Hours of Operation field). I'm starting with just Monday and Tuesday and I don't seem to be able to put two IF formulas within the same record. Only the Monday IF/FIND formula works and not the Tuesday. If I delete the Monday portion and leave the Tuesday, then the Tuesday portion works. That's what makes me think that you can only have a single IF formula (as well as nested IFs) and not more than one. I included screenshots and my formulas. If there's a better way of doing this I'm definitely open to that.Formula in "Hours of Operation" field:IF( FIND("Monday",{Open Days},0)=1,"Monday "&{M-Start}&" - "&{M-End}, "Mond

Hi, I need to have three fields that have parts of a phone number in each field. For example, if the phone number is (123)456-7890, I need three fields that return:field1: 123field2: 456field3: 7890Can anyone help me with a formula that would accomplish this? Thank you!

Hello! I'm new to Airtable and trying to create a formula field based on a date field. The formula should add 1 year to the date (the easy part), then round up to the half year beginning January 1 or July 1. For example,  5/15/23 would calculate 7/1/24; and 10/2/23 would calculate 1/1/25. Any and all help is appreciated!

Hey Everyone, Trying to create a formula that counts how many records in a date field are todays date, let me know if you know how to do this.I am totally fine if let's say there are 10 records and 4 of them are todays date and the formula counting the ones with todays date just says "4" next to all of the ones that are today - I don't need it to be like chronological as in the first one saying "1" the second one saying "2", etc. Thank you for any help, greatly appreciate it!

Hi! I am looking for a formula to calculate the school year. I have a date field that I want to use. The formula I thought would work is not working. Please see below: IF(AND(Date>“08/15/2021”, Date<“06/30/2022”), “2021-22 School Year”, IF(AND(Date>“8/15/2022”, Date<“6/30/2023”), “2022-23 School Year”, IF(AND(Date>“08/15/2023”, Date<“06/30/2024”), “2023-24 School Year”, IF(AND(Date>“08/15/2024”, Date<“06/30/2025”), “2024-25 School Year”, IF(AND(Date>“08/15/2025”, Date<“06/30/2026”), “2025-26 School Year”)))))

Hi All, I'm working on a base that needs several fields to be adjustable to display a date or text. Right now the date is being pulled from some formulas but the text is an option should the user need to add a note there instead. I'd really like to keep this all in one field instead of having a date field and a note field since this is duplicated across several different date fields. I can't figure out how to add a step to the formula to first check if something is text vs a number. Any suggestions?   

I need some help! I'm trying to figure out how to create a formula to calculate the % of column groupings total.  As you can see by the picture.  The total building size is 997,821 (SUM at the bottom).  Each year is grouped together showing the SUM of total square footage of leases expiring that year.  In 2028, there are five records (leases expiring) totaling 19,070 square feet.  How can I create a new column in my pivot table that calculates SUM of the Year (19,070 RSF) and divide it by the total building size ( SUM at bottom 997,821 RSF) to get 1.9%, for each grouped year?Unfortunately, when i try to create a new column with the formula ({RSF Total} / SUM({RSF Total})) * 100, it does not work.  Is this because my RSF Total column is a Rollup field? Thanks!

I am looking for help with an IF(FIND) formula (I think that’s what I need it to be). Here’s the result I’m looking for. If {Order Line Items} contains “8009586” display 2.5 FT Core Display, If {Order Line Items} contains “8009587” display 5 FT Core Display, if neither display None. Here’s what I have written but it’s not working: IF(FIND(“8009586”, {Order Line Items}), ‘2.5 FT Core Display’, IF(FIND(“8009567”, {Order Line Items}), ‘5 FT Core Display’), ‘None’)) I’ve created a work-around by creating this formula: IF(FIND(“8009586”, {Order Line Items}), ‘2.5 FT Core Display’, ‘5 FT Core Display’). This will show the first part and defaults to the 5 FT Core Display if the first part number is not there. It works but could have holes if neither parts are actually listed. Any help on refining this would be appreciated!

I have data that I am importing from a CSV. Sometimes it will import into my date field without issues, and other times it will simply be blank. In the screenshots I have attached, the left column is an Airtable date field (screenshot of settings attached) and the right column is the raw date data for comparison. I am trying to determine why some data will format correctly and others don't, but I can't seem to find any correlation. Additionally, I have tried using DATETIME_PARSE() but that has not yielded the correct date. Any thoughts? 

I have a User field that allows multiple users to be tagged. I'd like to write a formula that will pull those people's first names into a comma-delimited list. I came up with the following:LEFT({Users}, FIND(" ", {Users})-1)...which does the trick if there's just one person tagged. But if multiple people are tagged, the formula only returns the first name of the first person.Is there a way to make this work? I have a feeling this might require a REGEX formula though I'm not so clear on how they work...Thank you!

Hello all! I don't know how else to submit a feature request, but the formulas' support of multi-select fields is counterintuitive, so I thought I would share my experience.I want to have a computed field named {Is Executive?} where the value is TRUE if the multi-select field "Role" contains any of "Manager", "Label A&R", "Agent", "Distribution" and some others.There are multiple ways I tried to approach this:OR({Role} = "Manager", {Role} = "Label A&R", {Role} = "Publishing A&R", {Role} = "Agent", {Role} = "Distribution", {Role} = "Music Tech", {Role} = "Executive")Obvious issue: Multi-select is not properly handled (the formula only works if the record has exactly one Role).FIND statements chained with OR (like https://community.airtable.com/t5/formulas/screen-a-multiple-select-field-for-a-certain-value-to-fill/m-p/66522#M5986)Issue: If any options in the multi-select have common substrings, false matches will result.It's really important that we get this right, becau

I'm trying to figure out if there's a way to update a number field annually based on date. I've got a Position Start Date field, a Year of Experience On Entry field which is a number field that reflects the year(s) of experience the person had as of their position start date, and I want to create a field that adds +1 to the number in the Year of Experience On Entry field annually on their Position Start Date, and that field will be called to Current Year of Experience. Is there a way to write a formula that will calculate the Current Year of Experience?POSITION START DATEYEAR OF EXPERIENCE ON ENTRYCURRENT YEAR OF EXPERIENCE1/15/2022016/12/23004/13/201816Thank you for any thoughts you might have on this!

Hi Everyone, I am struggling to develop a formula to determine which position in an array another field is located. Here is my example: The Array of Trans# is 2239, 4539, 4792, 8912, 10394 I am trying to find a formula that says Trans# 4792 is in position 3, Trans# is in position 1, Trans# 8912 is in position 4, etc. I can use FIND to tell me the starting position of the string, but cannot figure out to determine which position Trans# is. Thanks so much in advance.

Have spent the day trying to figure out what I'm doing wrong and I'm totally stumped. Trying to write a formula to populate a status. If a person's start date is today or after, and their end date is today or after OR end date is blank, return "Onboarding." If a person's start date is before today, and their end date is after today, return "Active." If a person's start date and end date are in the past, return "Alumni." Those are working fine. BUTI also want to return "error" when there is something in the start date and the end date is empty. In my table there are two people with a start date, missing an end date, and it's returning "Alumni." Formula pasted below...Help! I'm new to writing formulas and I'm going totally cross-eyed trying to figure this out. IF(    AND(      {Position start date} < TODAY(),      {Position End Date} < TODAY() 

I use airtable for marketing project management of creative requests. I would like to count the timelines that my team is given. I want to count the number of days from the moment a record status is changed to "Ready for design" to said record's due date. I have a DATETIMEDIFF from date created to date due, but that is no longer working for our team. TIA