Formulas

I am using a simple formula: IF(TDay > TODAY(), 1, 0) It works fine in a table where TDay is a field containing Date values. However when I use the formula in another table where TDay is a field containing Date values that are pulled from another table via a lookup then the formula does not work (it either provides all 0s or all 1s and does not perform the evaluation function). Any thoughts on what I’m doing wrong? Thanks in advance.

Hi everyone, I can’t find anything about this in the documentation or in the Community posts: is it possible, in a formula, to refer to field option IDs instead of option values? Let’s take a basic single select field named “Choice” with 2 values: “1” and “0”. It’s likely that these values could be changed to “Yes” and “No” or “Acknowledge” and “Refuse” in the future. To avoid any issue with the formula when that happens, I’d like to refer to the option IDs matching the value “1” (something like “selQHcj58xhh44C1J”) instead of the values: instead of IF(Choice="1","blah","") I’d have liked to use IF(Choice="selQHcj58xhh44C1J","blah","") - but it’s not working. Any way this would be possible in a formula (not in a script)? Best, Ludo

Hi, having trouble getting rid of the “NaN” when trying to figure out the average difference in dates per sales manager. In one table (named “Tracking Log)” have a date field for “Transport Order Date” and a date field for “Commitment Date”. I used the following formula to look determine the difference in dates by number of days for each sale they made in a field I named “Time to Order Transport (days)”. IF( AND( {Transport Order Date}, {Commitment Date} ),DATETIME_DIFF({Transport Order Date},{Commitment Date},‘days’),BLANK()) Each manager is doing dozens of these transactions each month so I now want to see the Average days per manager for this date difference. I have another table (named “Sales Manager”) where I did a look-up of the field from Tracking log named “Time to Order Transport (days)”. I then created a new a rollup field, as well as a formula field using “Average”, however I have some new managers who have just started and have blank fields and I can’t figure out how

Thanks in advance to anyone who even scans this query! :blush: I have a rollup field that is flattening an array of dates - that’s working great! What I want to do is produce this same information showing only the month. For example, I have this list (a flattened list of dates from 3 different records showing only unique dates): 13/09/2021, 20/09/2021, 27/09/2021, 04/10/2021, 06/10/2021, 13/10/2021, 20/10/2021, 13/12/2021, 29/11/2021, 22/11/2021, 15/11/2021, 08/11/2021, 25/10/2021, 20/12/2021, 09/12/2021, 01/10/2021, 08/10/2021, 18/10/2021, 22/10/2021, 29/10/2021, 05/11/2021, 12/11/2021, 19/11/2021, 26/11/2021, 10/12/2021, 17/12/2021, 03/12/2021 I want to create a formula that converts this list of dates into (just the month of each date as an array): 09,09,09,10,10,10,10,12,11,11,11,11,10,12,12,10 etc… If I extract the month before producing the full array of dates, of course, it only ever gives me 1 instance of each month which is not what I want. I’ve been trying to work out the

Hi, I am trying to figure out why I keep getting this error message: The Impact Value is the average of the Confidentiality, Integrity and Availability column. (Confidentiality+Integrity+Availability)/3 The Risk Value should be multiplying the Impact Value with the Likelihood Value. But I am getting an error message with this basic formula {Impact Value}*{Likelihood Value} Please help.

I have 4 sets of formulas. SUBSTITUTE({Status}, “Floater Membership”, “Available”) SUBSTITUTE({Status}, “Pending Lease”, “Available”) SUBSTITUTE({Status},“Pending Payment”,“Available”) SUBSTITUTE({Status}, “Floater Membership”, “Available”) We wanted to merge them into one. I tried to do it with this formula SUBSTITUTE(SUBSTITUTE({Status}, “Pending Transfer”, “Available”), “Pending Lease”, “Available”), “Pending Payment”, “Available”), “Floater Membership”, “Available”) but it is not working. What is your workaround with this?

Hello ! I’m new to airtable and struggling to write a formula that allows me to extract dates from a field. I have tried many different formulas found online but nothing matches my case. I have a SPRINT column that hosts in each field a set of dates: 2022-01-03, 2022-01-10, 2022-01-17… (can go up to 53 dates). I would like to extract from any of the fields in the SPRINT column the FIRST date (2022-01-03) and send it to another field in a new column named START. Then, extract from any of the fields in the SPRINT column the LAST date (2022-01-17) and send it to another field in a new column named END. Do you think that is possible and if so could you help me find out the correct formula ? Thanks !

Hello, I am new to Airtable and am recreating an unwieldy spreadsheet into a (hopefully) elegant database! We harvest crops according to a varying ‘Grow Cycle’. The cycle is bookended from the ‘Seeding Date’ to the ‘Cleandown Date’. We record information like ‘Harvest Yield’ for each Grow Cycle. We also conduct daily checks of water and electricity meters to record our water and electricity consumption. In Excel using the SUMIFS function we can automatically sum all the water and electricity records between each Seeding Date and Cleandown Date to calculate the water and electricity consumption during each Grow Cycle. This is useful for calculating electricity and water intensity. I cannot work out how to replicate this in Air Table. Ideally I would like to use a Form so that Daily Checks Data can uploaded to the Data Base each day. I would then like to use another Form to upload Grow Cycle Data (e.g. Seed Date, Cleandown Date) and use a function to automatically sum all the Daily Check

Hi there! :grinning: Thanks in advance for taking the time to help me out. I’m new to airtable and I’m currently evaluating if I can use it for my small business to do the budgeting for my projects. I actually think what I’m looking for is too much of a spreadsheet functionality which isn’t supported by airtable as a database tool. But I would love to be wrong because it would make my life so much easier if it was possible: I’m trying to calculate the costs of each of my departments and usually just have a long list of items and staff. Some fields I need to be the percentage of the total costs (and update automatically if the total sum changes). For instance I would like to have a field with contingency costs of 5% of the total sum. Am I right assuming that there is no easy formula that can do that? And I would have to follow a complicated workaround with rollup fields and a linked table instead, because you can’t use summary values in formula fields? I feel like I’m really in a pick

Hello! Here’s what I’m hoping to accomplish: If {Inventory Arrived?} = ‘Inventory Arrived’ and HAS a date in {When did Inventory Land?} use that date. If {Inventory Arrived?} = ‘Inventory Arrived’ and DOESN’T have a date, use {Updated Estimated Delivery Date} date If {Inventory Arrived?} = ‘Inventory Not Arrived’, use {Updated Estimated Delivery Date} Thanks for your help!

Hey guys! I would like help to develop a formula that would give me the day “02” closest to the date of a specific field. For example: If in the {Purchase day} field the date is 02/25/2021, I would like the formula to show me 03/02/2021, if {Purchase day} is 05/14/2021, I would like the field show me 02/06/2021. I hope I managed to explain. Thanks in advance! PS: I also need help creating a formula that shows me the “02” day of the next month if the day of {date of purchase} is greater than 02. I’m trying to automate credit card billing. I’m racking my brain a lot.

I have a table where I track sessions with customers. I’d like each customer to have a session number that automatically increments over time. That means that this number would not be unique in the table. Is there a way to automate this? Something like this: John Smith 12/18/2021 1 Nancy Blume 12/19/2021 1 John Smith 12/20/2021 2 #autonumber

Hi, I have this situation: But I need to have December 2021 only on the first row created in December. When a new row will be added in January only the first row in January will have “January 2021” on column Month. Tried to do it with IF and AND but still gets an error. Could you please help me with this?

Hello everyone, I’m sure this is an easy one but well, hard enough for me it seems :winking_face: I have 2 working IF formulas, that run correctly independently but I need to run them together using IF(OR formula and it doesn’t work the way I do it. Thanks in advance for your help, surely an easy one for many of you guys. 2 working IF formulas: IF(LEN({Langue Maternelle})-LEN(SUBSTITUTE({Langue Maternelle},"Français",", "))>LEN("Français"),"Yes","No") IF(LEN(Langues)-LEN(SUBSTITUTE(Langues,“Français”,", "))>LEN(“Français”),“Yes”,“No”) Non-working combine IF(OR formula: IF(OR(LEN(Langues)-LEN(SUBSTITUTE(Langues,"Français",", "))>LEN("Français"),(LEN({Langue Maternelle})-LEN(SUBSTITUTE({Langue Maternelle},"Français",", "))>LEN("Français")),"Yes","No")) Airtable accepts it but it returns blank cells everywhere instead of “yes” or “no”. Thanks again ! Eric

The field name to identify the table (when linking a record) is often wrong. “Field on this table that links to the records you want to summarize” Any idea why this is and do others have this issue?

I have a column which has multiple linked records as Contacts in. Then I have lookup fields for the contact information (email and phone number) for those linked records. I am trying to make another column as a concatenate formula which shows “{Contact Name 1}, {Email Address 1}, {Phone Number 1}”. But because I have multiple linked records in a column it is instead coming up as “{Contact Name 1}, {Contact Name 2} {Email Address 1}, {Email Address 2} {Phone Number 1}, {Phone Number 2}”. Is there a way in the formula for me to limit how the information populates by linked record vs information requested?

Hello, I’m new to airtable and struggling to write a formula to ask the system to look at two separate fields and apply a label if the conditions are met. I have this formula already IF(IS_BEFORE({Materials Received Date (hide)},“12/16/21”),“Early Deadline”) and I’d like to add that the system also look at another field, application submitted, before 12/18/21 and also apply the label Early Deadline. Both statements should be true in order for the label to be applied. I was thinking I could just copy my original formula that worked and join them with an AND but I get an error. Thanks in advance!!

Hello! I’m trying to build a CRM tracker that allows me to calculate the number of days between each order placed by a unique customer. So far, this format I have is great and want to build on it, but what I want is to find the day range between each order number (so for eg: days between order #100090 and #100092, and then #100092 and #100132). Have been trying for awhile and still can’t figure this out Any help is greatly appreciated!

Hello all, I’m trying to figure out a clean way of writing a formula for a Lookup table that can hold up to 4 variables at any time (A,B,C,D). Based on these variables, I want the formula output the following: IF(Lookup contains: “A or B or C or A,B or A,B,C” - in any given order), output “Inhouse”, but if D is also present, still output “Inhouse”. If only D is present, then output “Foreign”. Is there a clean way to write this? Thanks so much in advance!

I am trying to build a table for my husband’s business that will show the commission earned for each of his employees. He has a column for Grand Total of Sale, Salesperson Commission %, and Salesperson $. He would put in the Grand Total and the Salesperson commission percent but needs the Salesperson $ field to populate. I know this is a very easy formula for all of you but I am obviously new to this. Thank you in advance for all your help.

Hello everyone, I am looking to create a formula to match candidates and job openings. Each “job” & “candidate” have hard & soft skills wich are tags (found each in separate tables)in our base. The goal is to create a function helping us find which candidates have the most tags (skills) in common with the tags found in the job descriptions. Would love some help from the community.

I can’t find any examples of exactly what I’m trying to do. It’s very easy to do in a spreadsheet and possible to build with queries but I’m trying to figure it out in Airtable. I’m a charity distributing funds to organizations across regions first and by size second. So a donation needs to be put into a regional designation first and then a size grouping and then divided amongst the orgs that qualify. What I’ve done so far is make a table with all the distributions and each field represents a Region & Grouping. I’ve labeled these RGIDs (Regional/Grouping ID). How do I put the dollar amount in the table into the proper RGID field? Let me know if I’m describing it poorly!

Hi beloved Airtable Community! I’ve got an issue with a VALUE formula. I have a Rollup field that does a ARRAYUNIQUE job from a Lookup Field which is formatted as a % (i.e. 36%). The output I get from the Rollup field is a string (i.e. 0.357142852346992). I tried various ways to convert it back to a number like: Within the formula of the Rollup, try VALUE(ARRAYUNIQUE(values)) Create a new formula field that performs VALUE(ROLLUP FIELD) But both outputs give me “#ERROR” I’ve read some articles about VALUE behavior but not sure to understand how it might help in that particular case. Would you have any hints? Thanks a lot, appreciate it!

We have created a scenario that I would greatly appreciate help on. The summary of the need is that we have a table that lists conversion data for each of our SKUs at our store (conversion data=if an item is sold in a case pack of 12 bags, then we need to convert the sale of 1 case into a total number bags we need to create…1 case coverts to 12 bags in Airtable Table. We have 75 skus and need to be able to automatically calculate total bags per SKU. Table 1 (current, This is a reference table and rarely changes) We have a table setup that defines the following: SKU in Column A the converted number for that SKU in Column B Table 2 (current) We also have another table that has raw sales data imported each day with the following columns: Order Date SKU Quantity (which, in this case, would be 1 case…hence the need for a conversion process) Table 3 (proposed) In a final 3rd sheet I would like to have the following display and be filterable based on Date Range (between 2 specific dates

Hi all! Thank you so much for helping with this math! I keep trying but short on solutions in how I’m thinking about this. I am attempting to write a formula that will assign Paid Time Off (PTO) to an Employee based on Start Date and consuming our Business Rules for assigning PTO. We have a Field labeled ‘Employee Type’ with values Hourly or Salary. Salary returns the result “Open PTO” and Hourly needs to do the math. Hourly PTO Rules Year 1 = 80 Hours PTO Year 2 and 3 120 Hours PTO Year 4 and beyond 160 Hours PTO Optional Override Field {PTO Override} - if a value is entered here use it and ignore the Yearly rules above. Ideally when an ‘Hourly’ employee ‘Starts’ they will get Annual PTO prorated up to 12/31/YEARNOW(). And the balance of their first 80 hours commencing on or before the {StartDate}. Then the pro-rated amount of Year 2 (120 Hours) up to 12/31/YEARNOW() +1. In other words, an employees Annual (1/1-12/31) PTO amount will be the combination of the remaining amount fro