Formulas

Hi, i need help with a formula if this is possible. i have a table containing a “date” field and a “formula” field that calculates the age from that. ( DATETIME_DIFF(TODAY(), {Person nr}, ‘years’) & ’ years, ’ & MOD(DATETIME_DIFF(TODAY(), {Person nr}, ‘months’), 12) & ’ months’ ) everything is perfect here so far, but i would like to make a new field that contains age groups, like 0-10, 10-18, 18-30, 30-50, 50-100 lets say. is there a formula for making this? and can it change buy it self when a person goes from let say age 29 to 30 and so on? or is there a way to search, filter or group already for lets say ages between 20-40 or 30 and up? or 25-45? Hope you understand what i am looking for, and every help i can get would be much appreciated! Thanks, Viktor Update: if its more simple to remove the “months” thats ok.

I’m trying to do a setup as follows, all within one base: Tab One - People Person A is tagged to Event X, Event Y, and Event Z via a link to Tab 2 - Events. Tab Two - Events Event X, Y, and Z all have an event date. A formula field spits out a number of days since that event has taken place, which is then linked back via lookup to Person A on Tab 1 So I can look at Person A’s lookup field for days since the events they attended and see that, for example, they attended events 5, 10, and 15 days ago. But the only piece I totally care about is the minimum number. So is there a way to construct a formula such that I can see only the lowest number, even if in an additional field, in Tab 1? I tried MIN() but I suspect it isn’t working because the output of the lookup field isn’t just a number, it’s a sequences of numbers separated by commas.

I have calculations going beyond the hundredth decimal and would like to be able to format the formula into currency to the .00: IF({Unit} = “g”, {Price}/{Qty}*100 & “/100g”, IF({Unit} = “mL”, {Price}/{Qty}*100 & “/100mL”, IF({Unit} = “kg”, {Price}/{Qty} & “/kg”, IF({Unit} = “lb”, {Price}/{Qty} & “/lb”)))) Price Column is Currency format thanks in advance

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/Month} ), FIND( ‘Aug’, {Year/Month} ), ‘SUMMER’, IF( OR( FIND( ‘Sep’, {Year/Month} ), FIND( ‘Oct’, {Year/Month} ), ‘FALL’ ),"")

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