Formulas

Could somebody help with a formula? Thanks in advance :slightly_smiling_face:

Hello Community, I am struggling with a formula which I thought would have been easy and I would need some help: What I am trying to achieve is: if column a is empty, return value A but if Column B is empty, return value B, but if Column A contains the value X in the string, return X. This is what I have been trying so far: IF(({COLUMN Al} = BLANK(), “VALUE A”, IF({COLUMN B} =BLANK(), “VALUE B”, IF(FIND(“X”, {COLUMN A), “X”))) My problem is that even if the formula doesn’t trigger an error, I am not getting the result connected to the last part IF(FIND(“X”, {COLUMN A), “X”))) What am I doing wrong, please?

I have a base to manage tasks. For sake of this example, let’s say I have 3 types of tasks (Update Photos, Clean Email List, Review Ads). There’s a table for Tasks and another table for the Schedule. Here’s the formula I’m using on the Schedule table. I can easily get the total count for each kind of task using a lookup field but I cannot figure out out to number each task in the Schedule table. {Task Name} & " " & “#” & “of” & {Count)} Assume for the year we have 4 tasks. This is how I’d like them to look. Update Photos 1 of 4 Update Photos 2 of 4 Update Photos 3 of 4 Update Photos 4 of 4 Clean Email List 1 of 4 Clean Email List 2 of 4 Clean Email List 3 of 4 Clean Email List 4 of 4 Review Ads 1 of 4 Review Ads 2 of 4 Review Ads 3 of 4 Review Ads 4 of 4 If anything is unclear please let me know. Thanks for anyone who reads this offers any thoughts or help.

Hi, I am looking to write a formula that counts the total number of marks of a question and sum them up together as a value in a column. marks of a question may be identified by square brackets in a field (e.g. [1] means 1 mark, [2] means 2 marks) A sample of the field looks like this: "Study the diagram below. a) Circle what happened to the amount of food in the bowl after a few days. [1] b) Based on the diagram above, name one substance this animal needs so that it remains alive. [1]" The total number of marks for this question would be 2 ( there are 2 [1]s which sums up to 2) I was thinking that I may count the string “[1]” but the formula does not seem to have this function. I would need to find another way to work around it. Any inputs/ ideas would be very much appreciated. Thank you in advance!

I’m trying to have a field formula yield the later year found between two date fields (even if one of the fields is blank). I’m completely confused how to add a IS_BEFORE function into the IF(OR) statement… The fields I’m working with is: {Last Modified/Reviewed} and {Date Pub}. I want a third column to return the YYYY that is later of those two (or the only one not empty of the two), and if the year is before 2018, to give me the label “2017 or older”… Formula to output the later YYYY of two date fields Formula to output the later year of two date fields?

Hi there, unfortunately, I have not yet found an answer to my question in this forum so I am asking myself. I have a single line text cell which is always structured similarly to the following: Brand Name Product Name (Color / Color / Color) I would like to extract only the portion in between the brackets into another cell. Meaning my output would be Color / Color / Color Unfortunately, the number of characters before the first bracket and within the brackets will vary. That is my main concern and why I have not yet found a good formula to fix this. Any help is very much appreciated. Thanks in advance!

I have been using a base that I found and successfully adding rows. Now, when I add a row, the initial box (First Name/Last Name), the formula isn’t there. How do I copy and then paste a formula?

Hi! I’m trying to get a field to output one of 4 results based on what it finds contained in a single Formula Field (Date reformatted field to YYYY-MMMM, pulling from the MMMM portion). If {Year/Month} is “Jan” or “Feb” or “Dec” or “Nov” = “WINTER” If {Year/Month} is “Mar” or “Apr” or “May” = “SPRING” If {Year/Month} is “Jun” or “Jul” or “Aug” = “SUMMER” If {Year/Month} is “Oct” or “Sep” = “FALL” This is what I tried but I’m getting an error. I managed to get it for one IF(OR) statement but am having trouble linking all four: IF( OR( FIND( ‘Jan’, {Year/Month} ), FIND( ‘Feb’, {Year/Month} ), FIND( ‘Nov’, {Year/Month} ), FIND( ‘Dec’, {Year/Month} ), ‘WINTER’, IF( OR( FIND( ‘Mar’, {Year/Month} ), FIND( ‘Apr’, {Year/Month} ), FIND( ‘May’, {Year/Month} ), ‘SPRING’, IF( OR( FIND( ‘Jun’, {Year/Month} ), FIND( ‘Jul’, {Year/Mon

Hi team! I have this formula IF(NOT({Action}= “”), DATETIME_FORMAT(SET_TIMEZONE(LAST_MODIFIED_TIME({Action}), ‘America/Los Angeles’), ‘M/D/YYYY h:mm’),"") however it’s not converting the time into the LA timezone. What do I need to tweak to make it convert?

I tried to make a custom formula with this expression, but it fails. If(Or(Phase= “5. On Hold”, StartDate>Today()), “5. On Hold”, If(Phase= “Complete”, “Complete”, If(Or(Phase= “1. ToDo”, And(DueDate<=(Today()+7), Phase <>“Complete”, Phase<>“5. On Hold” ) ), “1. ToDo”, If(Phase = “2. Planning”, “2. Planning”, If(Phase = “3. Doing”, “3. Doing”, If(Phase = “4. Controlling”, “4. Controlling” )))))) Do you have any advice?

Hi, I’m looking for a formula that can help me understand my “task capacity” for the day. In other words, let’s say I have “estimated time required” set for all tasks for that day, and that equals 7 hours… how can I then: a) First create a formula or setting to tell Airtable my capacity for the day is 8 hours, and… b) How can I then create a formula that takes that day’s tasks and lets me know if I’m over or under capacity? An example… If my max was set to 8 hours for the day, and I have 7 hours of tasks, AT would tell me I’m in the green (Green check symbol). If I have 9 hours of projected tasks, AT would tell me I’m over capacity (Red cross symbol)? I’ve attached a screenshot of my current “Tasks” view inside my own project manager, in case it’s of help. Thanks in advance for any assistance! -Christopher

Hey All I am stumped and have not been able to find a solution. Any help would be much appreciated. Problem: I have 3 columns that match “Champion” I have a 4th column I want to print “Champion” but only if all three columns match Champion I have tried SWITCH({R segement} & {F segement} & {M segement copy}, “Champion”, “Champion”) but is not working Thanks

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:

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

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!