Formulas

Hi! I am using airtable to keep track of several types of info connected to individual clients. However, sometimes I need partners/spouses' names to be in the same column. Is there a way to write a formula that only runs when certain conditions are met?For example, as seen in the picture below, my current formula for IN PROGRESS: Client and Spouse column is {First Name (from Spouse/Partner)} & ' and ' & ({Full Name}) I would like that formula to run IF there is a spouse, and I would like the IN PROGRESS: Client and Spouse column to only populate info from the Full Name column if there is no spouse/partner.I tried writing IF statements, but I couldn't figure out.Thank you!

Hi everyone! I have an Airtable base for a virtual event my team is planning for Learning at Work Week. The base includes the following:An "Activities" table with all of the activities that learners can complete, each worth a certain point value. The people who have the most points at the end of the week win a prize! A form (linked here) where learners input their email address (their identifier) and can multi-select (via a lookup from the Activities table) the different activities they did during the week. The form can be submitted throughout the week as various activities are completed, or at the end of the week once all activities are done - it's the users' choice! A "Form Responses" table to store all form responses.A "Participants" table that is automated to create a record that links the users' e

Hi! I'm having trouble with a formula where it brings back text and adds " " when it is done. Here is the layout:Column 1: Formula cell with the following formula {Client} & ' - ' & {Total Volume}Column 2: Linked to another table, displays {Client} names (Example: John)Column 3: {Total Volume} quantity (Example: 10)What I want to get from this formula is = John - 10What I'm getting from this formula is = “John“ - 10Is this because I am linking to another table? It does not happen with every value from the {Client} column! 

Hi friends! I am hoping this is easy, but I'm noob enough to not even know how to search up an answer in support/forums.My specific use is retreat registration. I have one table for Retreat Participants and a second for the possible Retreat Rooms.The "Retreat Rooms" table has a field for the rate for Solo Occupancy, and a field for the rate for Shared Occupancy.In my "Retreat Participants" table, I have a checkbox to indicate "solo occupancy". I would like there to be a field in the Participants table that then returns the correct room rate based on whether or not this checkbox is ticked.Let me know if you need further information or explanation, and if you have any tips! I don't know if this is an "If/Then" or "Switch" or what 😵‍💫 Thank you!

 Hello airtable community, I have this following formula but dateadd does not display the operation in the result thank you for your feedbackFranck IF({Date début package} != '', {Date début package},IF({Date début expl 23} != '', {Date début expl 23},DATEADD({Date début package},{Nombre jours d'Activité},'days'))) 

I have an attachment field containing images. I see that the stored attachment object has a url property. In another formula field I would like to show just the attachement url. Any ideas? Thanks.

I know I am late to the game here. I have been ignoring the updates until now. I currently have uploads and the links for the uploads through a formula - which allows me to create a download button.I use these so in the public share link to my base - users can quickly select the download button instead of opening the record and downloading the actual file. The airtable documentation says the url will stop working when in formulas. This sounds like my use case scenario. Does anyone know if this will this continue to work going forward?Obviously I thought this was meant to stop working in November, so either they haven't got to my base yet or I am in the clear (because it still works)...If this function will stop working - is there an alternative way to achieve what I am doing when the change happens?

I have a user groups as individual records. Each month, my team adds a field with the number of members in each group. This means I don't have a specific field i can put the 'latest' number in for more public views to the wider company. I am trying to create a formula field which says "If this field is blank, try this one, if that field is black, try this one" until a field with values is found... What I have tried, but got no success with (I am not a natural with code/formulas). Can someone help? 😄  IF(NOT(BLANK({2026-01 Members}, {2025-12 Members}, {2025-11 Members}, {2025-10 Members}, {2025-09 Members}, {2025-08 Members}, {2025-07 Members}, {2025-06 Members}, {2025-05 Members}, {2025-04 Members}, {2025-03 Members}, {2025-02 Members}, {2025-01 Members}, {2024-12 Members}, {2024-11 Members}, {2024-10 Members}, {2024-09 Members}, {2024-08 Members}, {2024-07 Members}, {2024-06 Members}, {2024-05 Members}, {2024-04 Membe

I have a score card with a computed percentage grade (e.g., 84) that I want to turn into an easy letter grade for my stakeholders.Rather than using a huge nested IF formula I thought I'd try to use SWITCH to compare and create some values.SWITCH( {% GRADE}, >=93, 'A', <=64, 'F')But I keep getting a generic Sorry, there was a problem creating this field. Invalid formula. Please check your formula text. error message.I can't find anything in the help documents talking about using comparison operators like =, >=, <= with SWITCH.Is what I'm trying to do even possible or am I stuck with that massive nested IF statemen

I’m working on a project management system in Airtable, and I need to set up a counter system to assign unique numbers to tasks within a vertical hierarchy. The goal is to create a structured numbering system that reflects the task relationships and levels. Here are the details:Project Structure:I have a hierarchical structure with parent tasks ("Predecessors" field) and the associated subtasks ("Next task" field).Each task can have multiple subtasks, forming a vertical hierarchy.Requirements:Assign a unique number to each task.Ensure that the numbering system reflects the hierarchy (parent-child relationships).Use a formula or automation to generate these numbers automatically.Below is what I have tried:I have tried to create 3 different fields as below:1. tesst2:Formula: IF(COUNTA(Predecessors) = 0, "1", "")Explanation: This formu

Hello,I need help with figuring out how to lookup the right price of an item for a particular customer from a table.I have an order line item table that contains "Customer Name" and "Item Name". Using this information together I want to look up the price from the price level table.This is the image of the order line item table. I have a customers table where I store all information about my customers. I have an inventory table that contains all the items. The price level table contains all the pricing information. Each item can have different prices for different customers, hence the separate price level table.  

Okay i have a table that has input information that i have added formula to and get the desired results fields out of itresult fields are:Drawer Height, Side length, FB length, inner length, bottom width.note that each one of these fields has a value to it.I would like to take this information and have it out put into a table or view of only two fields:in a combination of different results:field one "drawer height", field two "side length"one all results of this combination have be added then the next set would followfield one "drawer height", field two "FB length"and so on. how would one go about this?thank you for taking the time to look at this 

I'm struggling to figure out a good way to determine if a projects start and end date span any part of a specific year where start and end dates could span multiple years.So for example, I have records with the following columns for projects:Project A  3/1/23 [Start Date] | 2/2/24[End Date]Project B  10/1/22 [Start Date] | 5/1/23 [End Date]Project C  7/1/22 [Start Date] | 3/1/24 [End Date]Project D  1/1/24 [Start Date] | 2/1/24 [End Date]I'm trying to determine which projects were in any part of 2023. In the example above, it would be Projects A, B and C.Ideally I can create a formula that just outputs "2023" for any in the 2023 range.

I have a base with records that consist of the URLs of several images inside my site's midia folder. They look something like:https://mysite.com/news/april/banner1.pnghttps://mysite.com/news/may/banner2.pnghttps://mysite.com/news/april/etc/week2/file.htmlhttps://mysite.com/tutorials/folder/name.pdfEtc, fake examples. I would like to extract just the names of the files inside each folder, like:banner1.pngbanner2.pngfile.htmlname.pdfI figured it might be possible if I could FIND the last '/' on each string and extract wha

Good day all. I am trying to create a formula to name my Clients based on the following: If {company} is not blank, use {company} and if {company} is blank use {last name}, {first name} - and if there is a spouse name use {last name}, {first name} & {spouse name}I can't quite get it to work - can anyone help? 

I'm new here so I do not know that many formula. I imported my tables from excel and I have it generate name of the product with product name + weight. However, I want the numbers that are generated in 2 digits format. And here is my current formula :"[90.0]"&" "&{product name}&" "&{weight)}I tried using Round(), but it didn't help.  

I am trying to have a due date automatically calculated, but don't want weekends or holidays included. I am using the WORKDAY function, but still getting weekends. Workback from is the date to begin working on each task and Workback is the number of days allowed for each task. Can someone help?Here is what I am using: WORKDAY({Workback from}, {Workback},'2023-10-23,2023-11-23, 2023-11-24, 2023-12-22, 2023-12-25, 2023-12-26, 2023-12-27, 2023-12-28, 2023-12-29,2024-01-01, 2024-01-15, 2024-02-09, 2024-02-19, 2024-03-29, 2024-05-06, 2024-05-27, 2024-07-01, 2024-07-02, 2024-07-03, 2024-07-04, 2024-07-05, 2024-09-02, 2024-09-20, 2024-10-25, 2024-11-28, 2024-11-29, 2024-12-24, 2024-12-25, 2024-12-26, 2024-12-27, 2024-12-28, 2024-12-29, 2024-12-30, 2024-12-31, 2025-01-01')

Dear community of people so much smarter than me!I am using Airtable to build a film budget that has a number of fields and formulas that are causing me headaches. Table 1) Budget top sheet (two rollup fields: budget & actuals)Table 2) Budget (above the line and below the line)Table 3) ActualsTable 4) Cashflow forecastCHALLENGES: 1) On the budget - certain line items are subject to fringe (payroll fees, withholdings on a state-by-state basis, admin fees, etc.) and need to be calculated on a per line item basis. This is not consistent across the budget. How to solve for this? 2) From the above - - for things subject to fringes - we need to have a number calculated and roll up to the first table outlined above (the budget top sheet). How best to do this?3) We also need to calculate contingency - which is only calculated on Below the Line costs... how best to delineate this - calculate this - and have the

Hi - I am trying to update a forecast in case the field hasn't been updated for over a month. The idea is that if there are no updates, the forecast would lower by 10%. It all looks great, until I have to "Update record", I pull the record ID (dynamic) and added the field "Forecasted" and also make it dynamic. I add the field "Forecasted" and multiply it by 0.9. However, this creates an error. Here are the different versions and the results: "Forecasted"*0.9 --> Error"Forecasted"(0.90) --> Error"Forecasted".9 --> If the original value was $15,000 it shows $150,001 (maybe it thinks I want to move the decimal place?)"Forecasted""Multiply by" --> I created the field "Multiply by" to all equal 0.9 in the data, but whenever I place both of them together, no changes"Forecasted"*"Multiply by" --> Error"Forecasted"10% --> ErrorThis should be simple, the formula is pretty easy, but for so

Hello,I'm trying to create a mapping using IF and OR statement, in order to map a set of values for the 'Status' field to this new formula field.  This formula doesn't cause an errors, but it only partially works.  Does anyone know why this doesn't work?IF({Status} = "Complete",  "Completed",  IF(    OR(      {Status} = "Low Risk",      {Status} = "Candidate"    ),

I've read through several help topics and I am still having issues, hoping someone can help me. I have a field that is counting the days between the due date and todays date. I need to use these numbers to create a formula to provide an aging report. I'd like the 'Aging' Field to use age count to represent I need to group my table by aging, which I am trying to do by creating a formula based on the age count.if age count has a number between 1-30, aging is 1-30if age count has a number that falls between 31-60, aging is 31-60if age count has a number that falls between 61-90, aging is 61-90 Daysif age count has a number that falls between 91-119, aging is 91-119 Daysif age count has a number that falls between <120, aging is over 120 daysI’ve read through many articles and forum discussions about the nested formulas, but continue to run into errors, as a lot of the examples are not fo

Hello! I need to create a formula that pulls records from fields that correspond to answers of multiple-choice questions, like "What language do you speak"? Our multiple-choice questions also have an "Other" field. "Other" fields hold records which are either "no" (i.e. the person didn't select the "Other" response) or they are "yes, + [response e.g. Portuguese]". I want the formula to be able to pull the text from the "Other" fields IF it's a "yes, [response]" record, but to NOT include the "yes, " part, ONLY the response part (e.g. "Portuguese"). Can anyone tell me how to build that into the formula? Essentially, I need this part of the formula to say "IF the "Other" field contains a "yes, [response]" answer, then extract only the [response] text.Thanks!

I have the primary field set as a formula right now as CONCATENATE({Client Last Name},', ', {Client First Name}). I want the formula to include the spouses name after the client's name if the marital status (separate field) is married. What would be the formula for the primary field if I wanted it to be Client Last Name, Client First Name & Spouse First Name Spouse Last Name if the marital status field is married?

Hello!I have a list of names under a multi-select. Everyone in Blue is a FT staff member, everyone in yellow is a contracted worker. I want a count field (or formula field) that will automatically count all the Blue multiple select to give me the total number of FT workers, and another field to count the Yellow multiple selects for the total amount of contract workers. Is this possible? Best,H

Hello there,I am trying to create a formula that calculates the total cost of items based on their quantity and unit price; while also factoring in a discount percentage if applicable. I have tried using nested IF statements; but I am running into some challenges with getting the formula to calculate correctly. Here's an example of what I am trying to achieve:Item A; Quantity = 10, Unit Price = $20, Discount = 10%Item B; Quantity = 5, Unit Price = $15, Discount = 0%I want the formula to calculate the total cost for each item taking into account the quantity, unit price, and discount, and then sum up the total cost for all items.Could someone please guide me on how to structure this formula correctly?Also, I have gone through this; https://community.airtable.com/t5/formulas/need-help-with-formula/