Formulas

Right now I have a basic quarterly reporting function working in one of my bases, but I’m curious how I would improve it. I have a formula that looks at the starting date of a project and uses that info to tag it with the correct quarter and year. Great. That works fine. Where I’m a little more confused is how to then get that info consistently out onto a reporting tab. As of the moment, I’ve created a tab with each quarter of 2021, and I have an automation made for each quarter so that when a record appears with a 1Q-21, 2Q-21, 3Q-21, or 4Q-21 tag, it will be linked to the corresponding quarter record in my new tab. The issue, though, is that I would have to do this for every quarter of every year. Not the end of the world, but a little annoying for the long term. Is there any reasonable way to automate the process of creating the new quarter records for each year and automatically adding any records that should go into those quarters going forward without manually adding each quar

I’m sure I’m missing something simple here, but I’m trying to make formula that outputs the correct Quarter and then Year based on a relevant date field. So like Q1 - 2020 What I’ve come up with so far isn’t working, possibly because I’m missing something or have been a bit too confused by the nested IFs IF(OR(Month({Date Start})=1, Month({Date Start})=2, Month({Date Start})=3), “Q1”, IF(OR(Month({Date Start})=4, Month({Date Start})=5, Month({Date Start})=6), “Q2”, IF(OR(Month({Date Start})=7, Month({Date Start})=8, Month({Date Start})=9), “Q3”, IF(OR(Month({Date Start})=10, Month({Date Start})=11, Month({Date Start})=12), “Q4”," ")))) & " - " & Year({Date Start}) Any guidance is appreciated!

Hi guys im trying to utilize a formula from excel to calculate a score based off of two different fields but I cant seem to get it working. here is the formula =Total*(10^(0.751945030*((LOG10(Weightclass/175.508))^2))) Total and Weightclass are two of my fields and are set to integer inputs. any help is greatly appreciated.

Hi, i have another thread here but it changed into this problem instead. in the end i want a “age” field calculated from a “date” and i want to make a “formula” field that categories the age to groups. so I have a “date” field and a “formula” field that calculates the age in one number “15” lets say DATETIME_DIFF(TODAY(), {Date}, ‘years’) and then have another “formula” field that groups all ages 1-100 in “1-12”, “13-18”, “19-29”, “30-45”, “46-60” and “61-100” Formula found on internet but doesn’t work for me? IF( {Age} <= 12, ‘1-12’, IF( {Age} = 13, ‘13-18’, IF( {Age} = 14, ‘13-18’, IF( {Age} = 15, ‘13-18’ IF( {Age} = 16, ‘13-18’ IF( {Age} = 17, ‘13-18’ IF( {Age} = 18, ‘13-18’ IF( {Age} = 19, ‘19-29’ IF( {Age} = 20, ‘19-29’ ) ) ) and so on… then i could have a “app” with a “Chart” for: “1-12”, “13-18”, “19-29”, “30-45”, “46-60” and “61-100” right??? or use the built-in group feature and group them all.

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.