Formulas

Hi all - I’m having an issue when I try to “group by” a formula column in my spreadsheet. The issue is that it’s formatting the same date in two different ways and then treating the same date as different items and therefore splitting the group into two. This is how it’s formatting: And this is how it’s grouping: That should be one group of 57 entries. The formula is an IF formula that grabs a time from one of two columns. One column is a lookup, and the other is a date column. Is there any way to make sure they’re formatted the same way when they come out of the IF formula? Thanks in advance!

Hello All, I have a base with 50 $ fields and I want to total all of them. Is there a simple formula to do this or am I resigned to {1 Cost}+{2 Cost}+{3 Cost}+{4 Cost}+{5 Cost}+ etc…?

Hey guys, I’m hoping someone can help me - I’m such a beginner at formulas! I am creating a table to hold all my email campaign data. At the moment I’m logging all my data fields. What I need is a formula that will take a field make like ‘full name’ and turn it into a personalization tag, so the space needs to disappear, it needs to be in caps and I need a % sign at each end, so it would end up looking like this: %FULLNAME%. I don’t have the very first clue how to go about this so any help you can give me would be pathetically gratefully received. Thanks in advance! Katie x

im looking to Create a date when a status is closed ( to track when a record was closed not modified )

Hi - I would like to be able to sum the individual digits in a date - e.g, a given date = the sum of its individual digits. Some examples: Date: 7/13/1969 = 7 + 1 + 3 + 1 + 9 + 6 + 9 = 36 Date: 12/20/2019 = 1 + 2 + 2 + 0 + 2 + 0 + 1 + 9 = 17 I have some idea that I could convert the dates to text, then do a “value” of each digit, but nothing I’ve tried has worked. I’m grateful for any ideas!

Hi there I’m building a table of URL’s of locations. The Columns are: Location Name Slug Meta Title Meta description I’ve already determined the copy for my Titles and Description, but I want to include the content in the Location Name into that and then do a drag down the column to fill the rest of the entries. Does anyone know how to do this?

Hi! I’m trying to create a new field on my table that is a combination of two different Single Select fields. One column is the Likelihood of an event happen (1- Rare, 2- Unlikely, 3- Possible, 4-Likely, 5- Almost certain) and the other column is the Impact of that event happening (1-Insignificant, 2-Minor, 3-Moderate, 4-Major, 5-Catastrophic). The new column that I want to create will give me a “score” from the combination of the Likelihood and the Impact (1- Low Risk, 2-Moderate Risk, 3-High Risk, 4- Critical Risk). So for example, if the event is Almost Certain (5) and but the consequences are 1- Insignificant that will mean 1-Low Risk. Could someone give me guidance on automatizing this score? Thanks!

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