Formulas

Here’s one for you. We have a purchase order for a job, with a total amount in one table. We have multiple work shifts each be a varied amount depending on hours and rates of pay in another table. I’d like to be able to summarise all shifts and subtract them from the total purchase order to check we are under budget. Is this possible?

We are trying to add a formula that if one room is available even though others are not it should still says “Available”. If one room says Becoming Available but there’s no room that is available then it should say “Becoming Available”. If all rooms are rented then it should say “Rented”

Hi community, I am looking for a solution where I can compare two tables and update the main table with both status and also the new data entries from the second table. Appreciate if anyone can shed a light on how this could be achieved. Basically we have a data scraping code that writes the entire dataset to a table (let’s call this secondary table) on regular basis. And we have a main table used in production. What we want to achieve is to use the secondary table to update the main one. If the dataset does already exist in the main table and also exist during the latest script run on the secondary table (there is timestamp), then the “status” field in the main should be updated to “available”. If the dataset does not exist in the main table but show up during the latest script run on the secondary table, then the “status” field in the main table should be “new” If the dataset do exist in the main table but does not

Hi I am new to Airtable… big learning curve. I am using it for Shopify with the Shopify App Airpower for bulk uploading including images! (Amazing !). However I am changing themes and need to add to the text in my multiple select field. example. I use tags… so in a single field my current tage are formatted with a underscore… I have 20 tags…: color_blue, color_green, size_7", size_9", shape_tube, shape_slant, skirted_vinyl I need to keep the existing “tags” for now (during the transiton of themes) but for my new theme I need all the same tag but WITHOUT the first workd and underscore example: blue, green, 7", 9", tube, slant, vinyl… how can I creat an automation? I am not proficient in formulas other than excel. Please help! Thank you!

Hi I have two tables Transactions - with a date Periods of time - with a Title, Start date and End date. ie “Year 1” runs from 1st April 2019 to 31st May 2020, “Year 2” runs from 1st June 2020 to 31st May 2021. I’d like to have a field in the transactions table, which looks up from the periods of time table, which year a transaction took place. So if the transaction was on the 1st July 2020, it would be in the Year 2 fiscal period. If it took place on the 1st May 2019, it was in the Year 1 fiscal period. I know I could write that with a long IF statement, but that is cumbersome! If my fiscal periods were whole years, (ie 1st Jan to 31st Dec) it would be so easy! Any ideas or suggestions very welcome. Thanks, Andrew

I have a complex formula about 2 letter size pages long. The formula didn’t work and I spent 2 hours of my life trying to figure out why it didn’t. Looking at the formula, after procrastinating a bit I went on crunching through it but I was expecting to be rewarded by a nice catch worth my time going on through this fishing expedition. Instead I was taken by surprise of how sometimes complex problems require simple solutions. Here is the over-simplified formula. The issue was I wasn’t negating the formula on the LEFT correctly. To negate the formula on the LEFT, I need to negate also function “OR” and turn it into “AND”. I guess this is a 101 basic of Math but it has been so many years I don’t remmeber. I guess I am getting old now. The first column on the left is PRICE and the field can be empty, zero or non-zero. The formula is check if the field PRICE is empty or zero and show “YES”, otherwise show “NO”. The first picture shows the formula wasn’t conve

Hi everybody how are you? I am trying to create a formula that divides by 2 the amount of the field “Quantitat” only if in the “Tipus” field it says “sacas” and places it in the field “Tones”. Is it possible? I am using the following formula: “IF (Tipus =” sacas “,” Quantitat "/ 2) is it correct? Thank you very much for the responses, greetings.

Hello, I’m Phil And I’m working for a video production company. We create video for others companies, all the production follow up is made with airtable. I’m working on a formula that can increment a cell (0,1,2,3) if specific date cells are filled let’s illustrate my use case I need to log the delivery date of video production (SLA are calculated using those dates) if {v1} is empty = work in progress = 0 if {v1} contains a date = done = 1 if {v2} contains a date = correction done = 2 if {v3} contains a date = re-correction done = 3 then I’d like to use that “increment calculation” to calculate my production costs using another formula {unit_cost}*{increment_calculation} something like that. Tell me what’s your advices :slightly_smiling_face: thanks

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

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

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 al

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

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 ret

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!!