Formulas

Hello Friends, Needing to use datetime_diff to return days, hours, and minutes. I can only get the days to return the value. Tried all different types of formula nothing is working. Any thoughts?

Hello, I have been searching a quite universally used feature like split text to columns, which is something we are quite used to do. Should be as easy as: SPLIT function - Docs Editors Help Of course it might need indicating destination fields, but that’s all… Possible response: “We don’t know how many output columns there might be”-> just limit it to the number of output columns specified within the formula. If I set only 2 output columns, then too bad if a field had 3 strings, only 2 will get out of it. Instead, I have only found this “solution” around here: Split text in 1 column into 3 separate columns Formulas I’m guessing that there are line breaks after “Department” and “9701,” which we can use to create formulas to extract the pieces. First, create a formula field that contains the following: SUBSTITUTE({Credit Bureau}, "\n", REPT(" ", 50)) This will replace all line breaks with large blocks of spaces (50 characters each in this case). I’ll name

HI, I need some sort of way to say if my equipment is available or unavailable I run a small warehouse in my neighborhood I have a wedding soon and we need some of the equipment How do I calculate this and record to people that on such and such dates there will be less equipment left? And if I take everything then it should register that the equipment is out of stock Let’s say there are 60 tables I take thirty and I need them for 18 to 21 and someone comes that day to try to take It will say that we have only 30 tables left if you want more please come bake after 21 We have an ordering system in AIRTABEL so it should register him that on that day the tables are not available to try later It should also work by hours say if we have an event between 2 am and 4 pm on a certain date it should list that it is not available between those hours and can be returned later DO I need to use automation or I can use a formula? Thanks for the help

I have the following formula in one of my bases: IF(WORKDAY_DIFF(TODAY(),{Hold Start}) > 1, WORKDAY_DIFF(TODAY(),{Hold Start}),“0”) Hold start is a date for a project starting. Essentially all I’m doing is putting a zero instead of a negative if the workday diff is less than 1. Easy enough. Problem is, despite the fact that this spits out a numerical result, I am not able to format it as a number. And formulas relying on the output from this field are giving me an error. I tried just baking this formula into my next formula that relies on it, but the same issue of not being able to format the number persists (so I get odd results like .7777777778 and so on). Any idea how I could create the result of this formula but have airtable realize it is in fact outputting a number?

Webflow’s CSV import for their CMS now supports multi-reference fields (announcement link). Multi-reference field content must be separated by semicolons within it’s cell. When I use a Lookup on the linked record in Airtable, i get what i need but with content separated by a comma instead of a semicolon. I thought a simple SUBSTITUTE formula would finish the job for me, but I can’t seem to get it working, I get an ERROR! message every-time, and wondering if anyone can spot something obvious i might be getting wrong here? So i have been trying: SUBSTITUTE({name},",",";") and the field contains a bunch of slugs. So i have: blog-title-one, blog-title-two, blog-title-three and i am trying to use a formula to get: blog-title-one; blog-title-two; blog-title-three Any help much appreciated.

I’m trying to use data from a lookup field in a formula and keep getting #ERROR!. The formula (below) works with a plain text field created to test, but not the lookup field. IF({CITIZEN SLACKIDS}=BLANK(),’’, ‘<@’ & SUBSTITUTE(VALUE({CITIZEN SLACKIDS}),’, ', ‘>, <@’) & ‘>’) I’ve tried wrapping the lookup field in VALUE () to no avail.

Hi guys, I am having trouble with an ROLLUP function hoping for some help. See LOOM video attached How on earth can I ROLLUP using a GROUP of results from a list. Essentially this is my workflow plan. APP: to create a Nutritional Label from data sources in the airtable data. Workflow: Create new recipe Assign (link) ingredients to the recipe based on linked fields. Group the ingredeients linked to that recipe. Use formulas to create nutiriontal values based on the values from the linked ingredients. Where I am getting stuck is grabbing the values from a group of ingredients, especially in a ROLLUP. Loom 18 January, 2021 - Loom Recording Check out a video I made via Loom

Hello, I’m trying to create a formula to give a certain status to a record when 2 criteria are met. This is my attempt but I get the status “Onvolledig” in every cell although there are cases where the result should be “Volledig”: IF(AND({Betaalopdracht} = 1,({Huurders} = ‘’)), “Volledig”, “Onvolledig”) Field {Betaalopdracht} is a Checkbox. Field {Huurders} is a linked record => so if the checkbox is ticked AND {Huurders} is not empty, then I would like to get the status “Volledig”. Thanks in advance for the help! And I’m sorry if my explanation should be insufficient, I’m new to Airtable.

I have a weird one. I’m building a table of square footage calculations from a census of product types. I have a single select field for unit dimensions, like 10x20. And another field of the quantity of those units. So in an example of a 10x20 with 10 in the qty field, the proper formula construction would be =102010 for an output of 2000. I built a formula as a test to see if I could replace the x with a * to get closer to automating the math, but I can’t find a way to get that into a formula that then parses the * as a multiplier and does the full calculation. I’m missing some kind of step to get there. Any advice?

G’day All, I have linked to a Checkbox field in an Accounts table from a Billing table, which has then returned “checked” instead of the symbol. Which also shot the formula as well. Any experiences in how to show the actual Flag of the checkbox in the linked record? Cheers

Hi all, I’m looking to create two formulas: First: collecting all different contacts under the same account and apply a % based on how many contacts from the organization I interact with. For example: out of the 3 contact people in the customer account that are listed ((VP Sales, Sales Manager, and Sales development rep) I have had an interaction (added the date of it in a field) with only 2 of them, based on that, the field should present 66.6%. Of course that if I add a new contact to the customer account this should also be taken into consideration in terms of %. Second: I would like to build a second formula that counts the number of interactions that were done with each contact in the organization in a date range perspective of 3 months. Meaning, I would like to assure that my team reaches out to each contact at least once every 3 months. How can I do that? Thank you very much!

This one is simple, but I can’t quite make it work. Date field called: “event date” which is manually filled in Date field called “days until due” which is a calculated field Date field called “files due date” which is a calculated field Checkbox field called “review done” I want to calculate the “files due date” based on “event date” + 21 calendar days and the checkbox “review done” being blank. I have the following: Days Until Due = DATEADD({Event Date},21, ‘days’) Files Due Date: = DATETIME_DIFF({Days Until Due}, TODAY(), ‘days’) I want to combine the two above into a single formula which returns the number of days if the checkbox is blank, but returns nothing if the check box IS checked. Important: if the “Files Due Date” is today’s day, the result needs to be “0” not blank. Thanks in advance! Jeremy

Hello, I have a field that doesn’t present very clear data that I need to SPLIT. It’s a field that shows a form conversion by aggregating Page Name: Form Name. Ideally I would just be able to split on the “:” - however in my case sometimes the page names also contains “:”, so I have two. I’ve been looking at RIGHT formulas but they’re not doing what I want them to When I use: LEFT(Name,FIND(": ",Name)) I indeed get the text split on the first “:” found starting from the left However when I use RIGHT(Name,FIND(": ",Name)) the result comes out super weird, I can’t actually identify the logic of what it’s doing… Thanks!

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 per unit average : i.e on 01/01 I bought one unit of butter at a unit price of 2€, on 02/01, I bought

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 affect the order of the li

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.