Formulas

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),"Delinqu

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

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 "Ho

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

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

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/23

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(      

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

I’m using the following formula to take a record from one field to update a record in another field. IF({production Pipeline} = "Media Delivery", "Media Coordinator") & IF({production Pipeline} = "Ingest", "Media Coordinator") & IF({production Pipeline} = "Transcripts", {Assignee})Now I’m writing a similar formula but the record has multiple selections and there are too many combinations to use the same formula, so rather than use “=” I would like to use “contain” but I can’t find a way to express contains.I’m trying to get the formula to say  IF({production Pipeline} CONTAINS "Transcripts", Assignee) Is there a work around for thisI tried “!=” and “>=” and “<=” that does not return the desired results

In our scheduling table for projects (table is called "Deliverables"), we have 6 sequential dates that tend to move frequently:Kickoff DateCreative ConnectRound 1 to ClientRound 2 to ClientRound X to ClientDue DateI've been trying to write a formula (fairly unsuccessfully after researching much of the forum) that will look at all six of these dates and return the closest date that is TODAY or later. All 6 dates are in a single table and apply to all records. We're putting them in a column named "Next Due Date"What we currently have:  IF( OR({Kickoff Date}, {Creative Connect}, {Round 1 to Client}, {Round 2 to Client}, {Round X to Client}, {Due Date}), DATETIME_PARSE( MIN( IF({Kickoff Date}, (VALUE(DATETIME_FORMAT({Kickoff Date}, 'YYYYMMDD'))-VALUE(DATETIME_FORMAT(TODAY(), 'YYYYMMDD')))), IF({Creative Connect}, (VALUE(DATETIME_FORMAT({Creative Connect}, 'YYYYMMDD'))-VALUE

Is there a formula I can use to convert a date (6/28/2023) to a text (June 2023)? It'll make it easy for me to group by that month.

Good afternoon! How do I display the number of days in a month in the table, excluding Sunday. There can be as many as four non-working Sundays in a month, as well as five.

Hi all,As a new AirTable user, I am seeking assistance in creating a base to support my father's nonprofit organization in managing data. Currently, I have two relevant tables: 1) "Foundations Records" which contains information on grant agreements and applications with various foundations, and 2) "Foundations" which holds specific details about each foundation (e.g., related records, total funded amount, contact information). I would like to add a field that displays the most recent award granted by each foundation. While attempting this, I may have complicated the process, but I will explain my approach.Firstly, I created a lookup field in the "Foundations" table to gather all records associated with that foundation from the "Foundations Records" table. Additionally, I created a lookup for the year corresponding to each record related to the foundation using the same linked table relationship. My next step was to create an IF formula field that would use the highest

Hi, I'm having a little trouble with a project.I have a table called "Report" with the report names, submitted by, email address, and attachments and another table (Contact list) with a contact list with just names and emails.My customers have to fill in a form, and this fill in the main table (report table). My question is, how can I check if the contact that submitted the form (submitted by) is already on the contact list? I would like to do like a checkbox, so I can link an automation to this column and send me an email with the contact information of people that have not been on the contact list.I can't automatically add new people to the contact list, because that table is synchronized to another base.How can I solve this problem? I thought about the function lookup, but I wasn't able to apply it. Thank you very much!