Formulas

Hello, I’m trying to build a lookup between a list of web conversions that are classified by type and a text field for each of these types. (The use case is to use for email personalisation in a CRM based on different types of conversions) The issue I’m running into is that my type today is set by a formula field Here’s an example of what I want (unrelated, I know nothing about cars it’s just an example :winking_face: ) Type = IF (Find(“Volkswagen”,Name),“Volkswagen”, “Other”) Volkswagen Mini // Type = Volkswagen // Lookup = I’m happy to hear you have a Volkswagen. Volkswagen Van // Type = Volkswagen // Lookup = I’m happy to hear you have a Volkswagen. Blue Honda // Type = Other // Lookup = I’m sorry to hear you don’t have a Volkswagen. I want my lookup to be a central list in a table, do I need to be working on a JS script here? thank you!

Hi all, so I’ve seen this question here and there on the community but for the life of me I can’t quite figure out how to do this - would appreciate any help! What’s happening here is: This is my Findings Table. Each Finding is made up of several Quotes, and the Quotes are by different Participants. What I’ve done Link Findings to my Quotes table. Look up Participants from Quotes, for each findings. What I want to do Count the unique number of Participants under the Lookup Field: Participant Source. (see the table below, my current formula is wrong and returns a count of 1) I would like to Participant column to remain clickable as well, so roll-up isn’t an option for me. Would appreciate some advice on how I can do this easily, thank you so much!

Hi, I have 2 databases that are used in the context of restaurant management for inventory tracking, valuation : Database 1) A list of products that I am likely to buy (ingredients such as butter, milk) Database 2) A list of purchases Since the unit price of these items changes often, I want to track the valuation of my stock. So on data base 2) I have : Column A) Order number (i.e : #12584) B) A linked field that allows me to select a single item from database 1) (i.e : butter or milk) C) A number field in which I indicate a number of items bought (2 or 3) D) A currency field in which I indicate the unit price (€2,50 or €3) E) A formula/currency field which calculates the total expenses for the item (i.e €2,50*3 items = €7,50) In my first database, I would like to add, for each field (each item), a valuation of my stocks thanks to my purchases tracker, and another column giving a cost p

Hello everyone, I have two linked tables (“Projects” and “Issued items”). Issued items have a date field. They are not entered into the table chronologically - they are entered in batches, so the linked items are often out of order. (also note that the # of issued items per project is not consistent, could be as few as 2 or as many as 17. ) I am trying to calculate the average time between issued items for each project. I know I can do this with a series of rollups and formulas, but the solutions I can think of would require the linked records to show up in the correct order, and I can’t guarantee that. I can create a concatenated string of all the issued dates, then use find (left or right) to pick out the issued item’s own date from the string, and then select the immediately adjacent characters / date. However this will only work if the links are in the right sequence. From what I can tell, I can sort the issued items table by issued date, but that doesn’t affec

I am looking to format the date that is returned by the IF statement but when the date is blank it give me an error. When I just had the if statement without the date formatting it was fine no errors. The formula I used is below! Thanks in advance! DATETIME_FORMAT(SET_TIMEZONE(IF({MiX STATUS} = “MIXED”, LAST_MODIFIED_TIME(), BLANK()), ‘America/Los_Angeles’), ‘M/D/YYYY’)

Increment a Date - I want to calculate another field by adding 30-60-90 dates to a start date. Is that possible? I’ve tried using the DATETIME_FORMAT function - DATETIME_FORMAT({Start Date}+30,‘YYYY-MM-DD’) and with brackets around the 30

Hey Airtable community, Here’s my formula. The formula itself works, but I want to format the output as a currency. Why is Airtable saying it’s not a number? IF({Score} = 1, "75", IF({Score} = 2, "120", IF({Score} = 3, "180", IF({Score} = 4, "240", IF({Score} = 5, "225" ) ) ) ) ) Any help is much appreciated! With warm regards, Alex

Hi everyone, I have a “Days Remaining” formula in a Project Management Task View, and the formula looks like this: DATETIME_DIFF({Due Date}, TODAY(), ‘days’)… Naturally, if the “Due Date” cell is empty, the “Days Remaining” cell shows “NaN”… Is there anything I can add into the formula to make the “NaN” disappear, in other words, be replaced with a Blank cell? It’s purely for visual purposes… I’ve tried to add “BLANK()” into the formula, but that didn’t seem to change it so I must be doing it wrong. DATETIME_DIFF({Due Date}, TODAY(), ‘days’, BLANK())… Thanks, Christopher

I’m struggling with the syntax to have the Aging field go blank or 0 instead of NaN when there is no invoice date. IE, calculate/display the # of days since invoice was issued, return blank or 0 if invoice has not yet been issued. This is the formula I am trying to use. The Invoice Date field is InvoiceDate and I’m using the TODAY function: IF( AND( {Start}, {End} ), DATETIME_DIFF({End},{Start},‘days’), BLANK() ) Thanks!

Could someone please help troubleshoot this? I’m trying to calculate an expiration date based on the service provided. I believe I shouldn’t really be using “or” here, but included this just to help illustrate what I’m trying to do. Each IF statement works individually but not when I try to string them together. I have about 10 of these conditions that I’d like to check for. IF({Service}=‘Heartguard Plus’,(DATEADD({Date Expires},1,“Month”))) or IF({Service}=‘Annual Exam’,(DATEADD({Date Expires},1,“Year”))) or IF({Service}=‘3 year Rabies’,(DATEADD({Date Expires},3,“Year”))) Thanks for your help!

Hi, I am having a hard time display a specific text string from a longer text string since the string I want to show doesn’t have a fixed number of letters. Example: The main String in the field is; Status: Paid /Store: abc-store.dfeg.com /Owner: Firstname LastName I want to display abc-store.dfeg.com in the record. The “Status: Paid /Store:” and “Owner” are always there but the Store name ( what I want to display) and the Firstname Lastname part changes Any idea how to tackle this? Thanks in advance!

The primary table contains an appointment date (date field - ‘Appt Date’) and pre-set appointment times (single select field - ‘Appt Time’). We have configured a single-select field (‘Action’) to manually add the record to a view, which successfully triggers the sending of a text message via Zapier and Twilio. We want to automate the sending of the text 30 minutes before the appointment time, but we have almost no experience with formulas. The formula should set the ‘Action’ field to ‘Send Appt Text Reminder’ 30 minutes before the ‘Appt Time’ when ‘Appt Date’ is today. Your help is appreciated and suggestions for a better way are welcome. We are slightly limited, I think, by the fact that the Zapier/Airtable trigger is when a record first appears in a view. But we have everything working manually; now we want to automate the sending.

Hello, anyone know how to delete every characters/words after X number of words plz ?

How do I aggregate the sum of records in one field from one table to one record in another table? The key condition is that the Unique ID should match in both tables before the aggregation. So if the Unique ID is the same, only then the aggregation should happen. I have attempted multiple hacks and read all about RollUps and Conditional RollUps, but still cannot figure this out.

I am setting up a data base to track our job postings on various job sites. For a given job posting, we have a “status” field (this is the one I want to conditionally format), a “date posted” field, and a “date expires” field. The date posted field and the date expired field will be manually populated at the same time, once the posting goes up, although they obviously won’t have the same date. What I want to happen in the status field is: (1) when the date posted is blank, I want ‘need to post’ to appear. (2) when the date posted is populated and the expiration date has not passed, I want ‘posted’ to appear. (3) when the date posted is populated and the expiration date has passed, I want ‘expired’ to appear. Here is what I have… IF({Date Posted},AND(DATETIME_DIFF(TODAY(),{Date Expires})<=0,‘ :white_check_mark: ’,IF({Date Posted},AND(DATETIME_DIFF(TODAY(),{Date Expires})>=0,‘ :x: EXPIRED’,IF({Date Posted}=0, ‘ :raised_hand: Need to Post’))))) Airta

Hello, I am having an issue with the NUMWEEK Function that returns me week 3 instead of week 2 (according to google) for the following dates 13/1/2021 and 12/1/2021. What does it mean ? Am I doing something wrong ?

Scenario: Airtable is used to track visits to a facility. The facility sells 10-pass “punch cards” that expire after 10 uses. If the person doesn’t use the 10 punches before a specified end date, the punch card expires. I have a base set up with 3 tables: People Visits Punch Cards People is linked to Visits and Punch cards I’m pulling an array of all dates a person visited the facility via a rollup function into the People table. In the Punch Card table, I’m using a lookup to bring in the array. In the Punch Cards table, I want to return the count of all visits that took place within a specified date range (the valid range of the punch card before it expires). For example, if a person visits the facility 3 times- in April, May, and Jun, but the date range the punch card is valid is for April, the count should be 1. Example base here:

i have the following colums that iam trying to combine in a formula : customer billing (with only two possibles choices: direct or indirect) indirect total sales revenue (a formula which multiplies a monthly amount by a number of months) I need a formula in a new column which combines the two following conditions: IF “indirect” is chosen, then copy the same amount IF “direct” is chosen, then apply a 12% on the “indirect total sales revenue” i hope it is clear enough. Thyank you very much in advance! fsabate@isadviser.com

We are building out an inventory control system through Airtable. I’ve found examples and been able to build out the functionality to have the inventory adjust up and down based on receiving, production runs, and shipping. However, I cannot figure out how to incorporate physical inventory counts into the current inventory number. Example of what we would like to do: THIS PART WE HAVE BUILT: May 31 Starting Physical Inventory Count of Item A = 100 Throughout June, 30 Item A units were received Throughout June, 40 Item A units were shipped June 30 calculated inventory of Item A = 90 PART WE NEED HELP ON: June 30 physical inventory count of Item A = 85 (5 were lost to waste, etc.) What is the best way to enter this new physical inventory so that items received / shipped before June 30th are not adjusted from it and ONLY items shipped / received after June 30th until the next physical inventory count are deducted from the June 30th physica

I have a date field that is on a third Friday called “Date Started” I need a date formula that calculates the following 3rd Friday of the month for my “Due Date” field. Thanks

I would love if someone could help me work through how to set up a formula for the following situation. We have a volunteer application form for which we ask for applicants to provide 3 references. When determining whether to approve their application, we require 2 of the 3 references to be positive. If one of the three references doesn’t respond or identifies concerns, they are still approved if we receive the other 2 and they’re both positive. Basically, I need a formula that says if any 2 of these 3 fields has the value of “No concerns”, the approved column value becomes “Yes”. I’ve figured out how to use IF-AND parameters to set up a formula that says if all 3 are labeled no concerns, it’s marked yes, but can’t figure out how to incorporate an OR in there to allow for approval if any 2 of the 3 fields are labeled no concerns regardless of which 2 they are. I’m sure in plain English, my formula should probably be something like: If (reference 1 & reference 2

So I have done work with formulas on other projects but this one i havnt been able to figure out. I have a table where i am going to keep track of equipment hours and another where i keep track of fuel used. For the hours table i will add a new column for each day with the equipment hours to track how many hours that machine operated that day but i need to have 1 column that reports the last hours input. I know that i can put a “Ceiling” formula in but that would require modifying the formula to add a new column each day and after awhile that will get stupid. I would need one column that would take the highest number of all columns. For the Fuel table, i will be adding a new column for everyday to track fuel consumption. This would be easy using the Sum formula but just like the Ceiling formula, i would have to modify the formula everyday, adding a new column for it to include. I would need one column that could be updated with the sum of all columns related to fuel.

Hey Everyone! I have a lookup column with dates like this 5/30/2020, 5/29/2020, 5/15/2020, 5/7/2020, 9/21/2020, 11/27/2020 I need to only show the last date. I also need one for only showing the first date. Thanks!

Hey all, we are trying to get unique project numbers for our airtable. (We are managing video projects). Last year, we just did an auto number and had a formula to add 20000 to that number. 20001, 20002, 20003, etc. This year, we want the unique project number to be P21001, P21002, P21003… P for our name, 21 for Year, and 001 for the unique project number. We also want 2020 projects to be P20001, etc. I found in the forum the formula for getting the YY: MOD(YEAR(CREATED_TIME()), 100) Can someone help me figure out how to make the formula that will work for any year in this way? Thanks so much in advance!! Austin

Imagine I have 2 tables: STORE and ITEMS STORES are linked to the items table so that multiple items can show up in each store. The ITEMS table has a column called “Last Updated”, which is a bunch of dates I created a lookup field on the STORE tab that looks up the linked ITEMS “Last Updated” field. This returns a list of dates in a string: 10/20/20, 10/19/20, 11/2/20, etc Question: I want to take the Max of this field but I don’t know how to do this. I tried a formula like: Max({Items Last Updated}) but that doesn’t seem to work. Sadly, Airtable doesn’t seem to have a Split() formula equivalent. Any ideas? Thanks in advance!