Formulas

Hi all! I’ve run into an odd issue that I’m hoping someone out there has found a workaround for :slightly_smiling_face: I have a table consisting of recurring tasks, where each task is assigned a value ($). A zap creates assignments based on those tasks and values in a separate table. I need to be able to override the value of the task in the new table; which is pretty straightforward… but I’m running into issues if I want to override the price to be $0.00. Airtable seems to treat Currency fields (and probably number fields too, haven’t tested it) the same whether they are BLANK() or have a value of $0.00. Here is the formula I’m using (in a rollup field): IF({Override Task Price}, {Override Task Price}, SUM(values)/{Step Count}) Has anyone found a way to force Airtable to recognize ANY entry in a Currency field, even if it’s $0.00?

I have a rollup field (or lookup field) that usually contains an array of 8 numbers. I would like to have a formula that gives the sum of the smallest three numbers. Right now, it seems that it is impossible without an external application (Zapier). Are there any array formulas that would help me to achieve that? Note that these are numeric values and don’t necessarily have the same “length”.

Hi All, I am trying to go around this nested formula and hope someone could help me try understand why is not working. I have three {columns} that indicate if a condition is either 1 or 0. So: column 1 can be 1 or 0 column 2 can be 1 or 0 column 3 can be 1or 0 I have a fourth formula column where I want to indicate the following: IF column 1,2,3 are all 1 then show “status A” IF column 1,2 are 1 and 3 is 0, then show “status B”, IF column 1,2,3, are 0, then show " status C" I have tried this formula but it wont go through: IF( OR( FIND(“1”, {column1} ), FIND(“1”, {column2} ), FIND(“1”, {column3} ), ), “status A”, IF( OR( FIND(“1”, {column1} ), FIND(“1”, {column2} ), FIND(“0”, {column3} ), ), “Status B”, IF( OR( FIND(“0”, {column1} ), FIND(“0”, {column2} ), FIND(“0”, {column3} ), ), “Status C”, ) Thanks for whoever takes time to help out with this.

Hello, I’m trying to make a formula that counts the difference between start date and end date, however if end date is blank calculate the difference between start date, and current date. Sorry if it sounds simple, i’m very new to airtable! Thanks Matt

When entering a new record were I have sort perimeters, the new line covers data already entered, as I am entering the new record. I need to see the previously entered line while entering the new record… Is there a way to stop this? Tony SIms

Hi I am trying to add a formula that will show a renewal date 3 years from the subscription date (yr 1) but not show anything for implementation period or yr 2, yr 3. IF ({Impl /Sched Fee} = Subscription Yr1), (DATEADD({Inv Date}, 3,’ years’), "None "))) I continue to get errors on this formula

Hello, I have a table of People. Each person is linked to a Property via a lookup field. I want to add a field to the People table that assigns them a value based on which Property they’re linked to and when their Date Created is. So for example: for all the People in the table who are linked to 123 Main St, I want the first (by Date Created) 100 records to have the value of 1, the next 100 People records to have the value of 2, and so on. Does anyone have any ideas for how to go about doing this? Thanks so much!

As a relative novice with IF Formulas, I am hoping for help with the following. My table has columns for First Name, Last Name and With First, With Last. I am using Airtable to track RSVPs and would like a Formula field that provides the following result. When Last Name is not blank and With Last is not blank, enter 2. (John Smith is bringing Sally Smith, so 2 people are attending). When Last Name is not blank and With Last is blank, enter 1. (John Smith is coming by himself, so 1 person is attending). I appreciate any guidance on how to achieve this result. Thank you!

Hi! I am trying to do a COUNTIF type formula but I cannot find anything to work. Not sure why I cannot upload images or links to this post. Basically, I have a lookup column that looks up info from another table and shows an array with emojis (checkmark, x, triangle); so, for example, the first cell shows :heavy_check_mark: , :heavy_check_mark: , :heavy_check_mark: , :x: , :heavy_check_mark: , :heavy_check_mark: . I want the second column to count how many green checkmarks are in the first column; that is, I want it to show the number 5. Any ideas?

I’ve searched the forum and tried to figure the formula out on my own but I’m not even sure if this is possible in Airtable. Any help would be appreciated. I have multiple columns where entries are either numbers or blank. I’d like to be able to generate a phrase that puts all these numbers and field names together as text. xfieldname is the name of the column. All cells in the column are numbers or blank. xvalue is the number in the column. Final field is text If xvalue > 0, return “xvalue xfieldname” If not, return blank [insert comma] If yvalue > 0, return “yvalue yfieldname” If not, return blank Final output is: xvalue xfieldname, yvalue yfieldname I’ve tried: IF({Set of Pins}>0, “{Set of Pins} set of pins”, “”) Which of course produced “{Set of Pins} set of pins” instead of the actual number in the column. Example: If the “pairs of socks” column has “2”, the “hats” column has “3”, and the “set of pins” column is blank, the final output is: 2 pairs of socks, 3 hats

Hello AT Community I’ve got a formula partially working but I’m having some difficulty tweaking it to just the right look. Looking for numbers in the SAL field that result as a negative to show the emoji - so far so good. How do I make positive numbers in the ‘test’ field show as a percentage with only two numbers behind the decimal and with proper rounding up? Thanks in advance for your brilliance!

Hi, I have a table with 3 columns, “Usage”, “Clients” and “Status”. “Usage” is a single select and can only be “Platform” or “Client”. “Clients” is a link record that allows me to select multiple client records from another table. “Status” is a lookup field returning the status of each client from the client field, it displays the results in a comma separated list. Possible values for “Status” are “Live”, “Suspended” and “Terminated”. I need to write a formula that satisfies the following conditions: If “Usage” = “Platform” set the cell as “Active” If “Usage” = “Client” AND the value “Live” is present anywhere in “Status” column then set the cell as “Active” If “Usage” = “Client” AND the value “Live” is not present anywhere in “Status” column then set the cell as “Inactive” If “Usage” = “Client” AND the “Status” column is blank then set the cell as “Inactive” The formula’s I’ve tried so far don’t work. I’ve tried using IF(AND statements and IF(SEARCH statements but I can’t get them t

HI, all! Looking forward to your kind help. My end goal is to sort out people who have attended 3+ workshops. I have a workshop business, and we have three main ticket types: Deposit, Remaining Balance and Pay in Full. I am working on a way to automagically sort our orders table to show only people that have attended 3+ workshops. That means either Deposit + Balance OR Paid in Full. I figured if I can make a formula that uses logic to see if Deposit and Remaining Balance fields both return “checked” then… something. And this is where I get hung up writing the formula. I’m sure I can then use another field or view to examine that result and the Paid in full to find a total quantity to be 3 or greater. Some people sometime pay in full, and sometimes put down a deposit and then their remaining balance. So I have to examine both fields to determine their total attendance count. Essentially, I am having a hard time achieving my goal of determining if they attended 3+ workshops. Each order i

When using Array Join, if my price was 410.00 it removes the decimals on output and becomes 410 and it does not look good on my print sheet. How do I force a number to output two decimal places inside an ArrayJoin? Example, 410 becomes 410.00 Also, 0.1 becomes 0.10 etc Thanks! Joey

I am trying to figure the correct syntax for a consistent name concatenation. I have two fields, First Name, and Last Name. I have used the following formula to capitalize the first letter: UPPER(LEFT({First Name}))&LOWER(MID({First Name},2,LEN({First Name}))) & " " & UPPER(LEFT({Last Name}))&LOWER(MID({Last Name},2,LEN({Last Name}))) Which works great… Unless the last Name is TWO Names: First: “mary” Last: “sue beth” Becomes “Mary Sue beth” I need a way to check for two names and capitalize both, and also take account that some people may use a “-” between names. The goal is a formula that makes: “Mary Sue Beth”

I have four name fields that I wish to concatenate. First Name-1, First Name-2, Last Name-1 and Last Name-2. Objective: If First Name-2 and Last Name-2 two are both black, return First Name-1 Last Name-1 If First Name-2 is not blank and Last Name-2 is blank, return First Name-1 & First Name-2 Last Name-1 If both First Name-2 and Last Name-2 are not blank, return First Name-1 Last Name-1 & First Name-2 Last Name-2 I am new to Airtable. Thanks

I’m looking to automatically duplicate a row if the cell in a certain is ‘xxxx’. For example, if Column ‘B’ is ‘NO’, I want that row duplicated. Is this possible?

OK, hope someone can help here: I have a table names “ProdCo” - companies I’m following: In this table, I have a field named “Owner” - that links to a table - same name - where I put, among other things - email addresses: I now want to target certain companies for a sales initiative. I made a “check box” field in “ProdCo.” table to tag these companies. My question: How can I most efficiently generate a list of these Companies with their owners with their email addresses, I cannot for the life of me figger that out. Thanks in advance for any thoughts, Perry

Hi. maybe the free plan can not be integrated with Zapier or Integromat? I want to make a scenario on Integromat which takes action on my base free plan like delete a record and create a record, but for some reason, the actions do not take place.

Hi there, I’m trying to get a total number of years worked for my company, based on original Hire Date, Termination Date, Rehire Date, and Today(). I’ve been trying IF statements for a good day now, and while I’ve managed to get it to add up the years, it always rounds to the nearest whole number, even if it adds the decimals .00 after it. Not every employee has a termination date or a rehire date, but some do. I need the formula to determine whether fields are missing and calculate based on the information it has. IE: (today - rehire date) + (termination date - hire date) or if fields are missing, to calculate accordingly. This is what I have so far: =IF({Hire Date} = BLANK(), 0, IF({Term. Date} = BLANK(), DATETIME_DIFF(TODAY(),{Hire Date},'y'), IF(RehireDate = BLANK(), DATETIME_DIFF({Term. Date},{Hire Date},'Y'),SUM(DATETIME_DIFF(TODAY(),RehireDate,'Y'),DATETIME_DIFF({Term. Date},{Hire Date},'Y'))))) (I can’t figure out how to get the code to display properly, sorry) I’m not sure whe

Hello there, I’m trying to write a formula that calculates the sum of values in another column. The tricky part is I want each row to add only the numbers above it in this other column, adjusting each time it goes down the row. For example: DATE EPISODES PRODUCED TODAY TOTAL EPISODES PRODUCED 7/11 2 2 7/12 3 5 7/13 1 6 Where the “TOTAL EPISODES PRODUCED” column is automatically tracking what’s being input into the “EPISODES PRODUCED TODAY” column.

I have a date field including M/DD/YYYY. I want to have a formula column that will use the date field to display just the name of the month in this format “01-January.” Being new to formulas I’m not sure how to do this. At first, I tried to identify the month using IS_BEFORE and IS_AFTER. That seemed cumbersome since it has to deal with the entire date. Then, I thought perhaps I could just identify the month within the string and repeat it with text, but I don’t know how to do that efficiently and couldn’t find a help thread that would explain parsing data that way. Any help would be appreciated.

I am brand new to airtable and have no experience of using this type (or any really) of database. I understand that there is a lot of assistance through the various guides but I made an error the other night which could have been costly had my database been up and running, rather than being tested by me. My error was, I thought I was deleting a cell, instead all of the records I had created disappeared. I’m sure that, in time, I will find the answers to my questions in the guides. However, time is not on my side if `i make the same error when the data is actually important. So, can you help? How do I not delete entire records by mistake? However, in the event that I did, surely there’s an “undo” button? Also, I’m sure that I read that your bases and their contents are automatically saved? How can I make back up bases in case I make my original mistake? Apologies for my questions, you may think I’m being lazy. However, I hope you understand that I want to progress quickly as the datab

How would you write a date time formula for Beginning of current month to End of current month? The trick is, it’s the current month and the month always changes. Any Jedi out there?

I’m trying to track the dollar amount of a donor’s remaining pledge. Say they pledge 500, then donate 150, then donate 75. I want a formula that will calculate the remaining pledge as it goes down. Thanks!