Formulas

Hey Everyone: I have a table that links items to form submissions. I already have a link to see each item and all the form submissions that include that item. I also have a lookup that looks up the dates of the form submissions in the link to forms. I want to take that lookup and run a formula to count all the form submissions from today’s date. Any ideas?

I was able to do a simple subtracting formula to show amount owed and amount paid and balance remaining. Is there a way to expand on that formula to include when interest is charged so that it can be added to the balance? I want to have a more accurate picture of my financial spending. Any help or advice would be appreciated. Thank you.

Hello! I have a Jotform survey automatically going into Airtable. Some of the questions have “Check all that apply” with an “Other” response that prompts a free form answer. These free form responses are being added as multi-select option in Airtable (rightfully so). I’m trying to do an Airtable Interface on the survey responses but, obviously, those free form responses are being considered. In a column, I want the options and replacing the free form response with “Other”. Then in a separate column track the free form response (just so that data isn’t lost). Any ideas? This is essentially what I’m trying to do: The “Name” and “Q1 Answers” field is the only thing imported with survey responses.

Hello, I am new to this software but have written a pretty functional Airtable to keep track of the UK 50p coins I am collecting. I’m able to add costs and dates and photos and descriptions and have even been able to add columns up at the bottom using the Filled option. I have three columns (16, 33, 7) which I would now like to add together to give a total of coins. I would then like to multiply that number by .50 to give me the bare minimum value of my collection. I think it ought to be simple but I am held back by lack of knowledge of basic formulas and would really appreciate any help the community could give me.

Background: I have a table that includes a list of activities each customer has done. So there unique value is a random “Event ID”, another column is “Customer ID” and a third column is called “Activity Type.” I need to find a way to count how many times a Customer has done a certain Activity Type and then be able to use that formula to create a notification when a customer has their FIRST activity (first appears in the table.) The table is populated via API, so it’s not as simple as just knowing I did it. I found a Pivot table that can just give me the counts of Activity Type by Customer, but you can’t act on any of the information in that table. So essentially I need a way to find any time a new record is created that contains a unique Customer ID that does not exist yet in the table. TL,DR: I need a way to answer the question “Tell me when a new client has their first activity”.

Hello, I am horrible at formulas and it’s really becoming a frustration as I continue to learn Airtable. I am wanting to create a status in the formula field to then automate an email trigger. The formula needs to update to either 60-days or 30-days based on the contract end date. I already have fields for the start date and end date so the formula can work with either. Seems simple enough but I am a struggle bus. What formula do I need to enter? If we are 60-days out from the contract ending, update the status to 60-days, and if we a 30-days from the contract ending, update the status to 30-days. Start with IF(AND(, correct? IF(AND({Contract End Date} …blank stare…, “60-days”), repeat blank stare, “30-days”) Help :weary:

Hello Community! I need to calculate the score of a record based on that record’s numeric value. My records’ numeric values can be anywhere between 1 and 100, and I need to set a score between 1 and 10 to each value based on how high that value is, like so: 1-3 => 1 3-5 => 2 5-10 => 3 10-20 =>4… you get the idea My problem is with setting up a formula - whet I try to set it like this: IF(Value>1,1,(IF(Value>3,2,… etc. the result only reads the first parameter (value>1 => returns 1) and it doesn’t compute when the value of the record is more than 3, it still returns me 1. How can that be fixed?

Hey there…look for assistance with formula that uses the TODAY() function with a set time. I.e. I want the field to have the current date but with a set time so that it would show for example the current date that updates each day but always has 12:00 PM. Thanks in advance for help

Hi there! Looking for assistance on a project management table. Each project will have a Project Type, Review Date and Delivery Date. There are three potential Project Types (Lead/Partner/Advise), the Delivery Date is a manual entry, and the Review Date needs to change based on the Project Type. Problem: 1) I keep getting an invalid formula notification, 2) I’d really like the review date to only land on weekdays/workdays but the formula needs to account for weeks out, not days out. Additionally, I’ll be duplicating this formula for various types of Review Dates, and sometimes based on Project Type, I’ll need the answer returned to simply be “Blank”. Here is the nested IF statement I am attempting. IF({PROJECT APPROACH} = “LEAD”, DATEADD({DELIVERY DATE},-15,‘WEEK’), IF({PROJECT APPROACH} = “PARTNER”, DATEADD({DELIVERY DATE},-14,‘WEEK’), IF({PROJECT APPROACH} = “ADVISE”, DATEADD({DELIVERY DATE},-13,‘WEEK’),’ ))) Would really appreciate help on getting this sorted out. Thank you!!

So I’m not sure how to ask it differently, but if I were to try, here it goes. NEW to Airtable (so TY for being patient & detailed) So I have a base with three tables in it, Customers, Vehicles & Sales. I’m trying to collect all of the sales for each customer. All of the tables are linked, and I have been able to bring each of the sales into one cell (example: $0.00, $170.00, $125.00). But now I need to total those numbers! Any help would be greatly appreciated! DG

Hi all, Trying to make an interface with a “maximum possible spend” for each project launch day. Projects launch twice a week and run from 2 days to 15 days. I need to find a way to ask the question “Is this project still running next Tuesday” and “is the project still running next Friday”. Does anyone know where to start? I’ve tried a few things, I’ve tried lots of nested if statements, but I’m truly stumped. EDIT: A better way of formatting the question is “Will this product still be available for purchase next Tuesday/Friday” as two separate columns with a true/false value.

Please could you help me to understand how to make this work? Thank you! IF(NOW(IS_AFTER({Announcement Start Date}&IS_BEFORE({Announcement End Date}, ‘ACTIVE’, ‘INACTIVE’))))

Hi there, I want to create a field that gives a different message to the user, depending on whether they have filled in a particular field ‘on time’ or not. I’ve attempted to explain it in text. If anyone could suggest the functions I need to use, I would really appreciate it. The problem for me is a. how to say ‘IF time is less than 5 minutes towards the time in the field OR IF time is more than 3 hours after time in field’ and to link that to b. ‘… AND field x has not been filled in THEN ‘show message a’’ and c. linking several options together (x3 optional messages). IF time is less than 5 minutes before end of lesson AND there is nothing in lesson reports field THEN “Please enter your lesson report, concrete targets and homework now” OR (IF time more than 3 hours after the lesson end time AND there is nothing in the lesson report field THEN “Your lesson report is late. Please enter your lesson report, concrete targets and homework now”) OR ((IF the report field is filled in BEFO

hey community, I’m working on this formula to return a simple “Y” or “N” based on whether or not a date is past. The items in the grid could either be on a single date, or could have both a start date and an end date. I’ve tried a couple different ways of writing this formula but getting a lot of errors. What I’d like to do is: “if the end date field is empty, determine whether or not the event is in the past based on the start date field; otherwise [if the end date field has a date], determine whether or not the event is in the past based on the end date field.” Here’s what I’ve got: IF({End Date} = "", IF(IS_AFTER(TODAY(), {Start Date}), "Y", "N")), IF(IS_AFTER(TODAY(), {End Date}, "Y", "N"))) Could somebody help me figure out what I’m doing wrong? Thank you!

Hi All! I have a nested IF statement that is set up to find if the value from Column A, B, C, D, or E matches Column F. If one matches, it produces the name of the column. What I’m looking for is a way to produce more than one name if more than one column matches. Currently it looks like this: IF( OR( ColumnA, ColumnB, ColumnC, ColumnD, ColumnE ), IF( ColumnA=ColumnF,“ColumnA”, IF( ColumnB=ColumnF,“ColumnB”, IF( ColumnC=ColumnF,“ColumnC”, IF( ColumnD=ColumnF,“ColumnD”, IF( ColumnE=ColumnF,“ColumnE”, ) ) ) ) ) ) This only gives me one result. If more than one column matches, I’d like it to somehow give me both or all results Thanks!

I have a Fx field and for some reason, it’s showing " at the beginning and end of the words. Is there a way to remove it? Formula: {Contact} & “—” & {Type} How it’s showing: “John Smith, Acme Inc”—Email How I’d like it to show: John Smith, Acme Inc — Email

I am building a simple recruiting database in AirTable. Here are the tables that I have Candidates - (names, all information, name of recruiter linked to Recruiter table) Recruiters - Name, Email Position - Position, company, salary, etc. Submission - This is where a candidate is linked to a position. Link to Candidate Name, Link to Position. I also have the Recruiter Name here though Lookup. How can I display recruiter’s email so it can be included in email automation?

Hi all, I’m using Airtable to create a mood tracker. I have a form question which asks which of the following 20 emotions I’m feeling, multiple answers = ok. :green_square: Balanced :green_square: Calm :green_square: Cozy :green_square: Loving :green_square: Fulfilled :yellow_square: Energised :yellow_square: Focused :yellow_square: Happy :yellow_square: Inspired :yellow_square: Motivated :blue_square: Bored :blue_square: Drained :blue_square: Lonely :blue_square: Sad :blue_square: Tired :red_square: Angry :red_square: Anxious :red_square: Hungover :red_square: Restless :red_square: Stressed :red_square: Tense What’d I’d like to achieve then is to have a pie chart look at these emotions and display an ‘average’ with each of the 4 colours. My plan was to use IF(FIND) to create a kind of mood average column in the database to direct the pie chart to, but I’m having a bit of difficulty writing the script. I was going down the track of: IF (

I’m trying to figure out the difference (in percentage) between two numbers. I’d like to set up a formula that gives the percentage (positive or negative) difference between two numbers. So in the screenshot above Row 1: Percentage decrease from 100 to 50 Row 2: Percentage increase from 25 to 50 Row 3: No change between 50 to 50 My formula so far: -(({Starting #} - {End #})/{Starting #})

The piece I am really struggling with is the comma delimited split. This is the order as done in power query. Select entire tab/Table Source = Excel.CurrentWorkbook(){[Name=“Table1”]}[Content], #“Changed Type” = Table.TransformColumnTypes(Source,{{Many Columns/Fields}}), Select only the Columns/Fields #“Removed Other Columns” = Table.SelectColumns(#“Changed Type”,{“Key”, “ManyLinked”}), Remove empty cells/rows #“Filtered Rows” = Table.SelectRows(#“Removed Other Columns”, each ([ManyLinked] <> null)), Split the ManyLinked into one row for each comma delimitate value - each row gets the value from Key and a single value from ManyLinked #“Split Column by Delimiter” = Table.ExpandListColumn(Table.TransformColumns(Table.TransformColumnTypes(#“Filtered Rows”, {{“ManyLinked”, type text}}, “en-US”), {{“ManyLinked”, Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), “ManyLinked”), Thanks, Teri

Hi everyone! I’m adding a date field {Fecha de inicio} and a duration field {Tiempo de impresión} to get an end date with hour. But I need to know if the result can skip/exclude non working hours and days so the results are not for example, Sunday at 4 am but Monday at 8am This is what I have now: DATEADD({Fecha de inicio},{Tiempo de impresión}, ‘seconds’) I’d appreciate if someone can help! :slightly_smiling_face:

Hi! I’m tracking loan payments (for a 0% loan support-community). One column should show the monthly payment made. The other column should aggregate total payments made thus-far. Suggestions? In the screenshot I manually entered what I’m aspiring to have auto-calculated. Thank you!!

For a casting agency db I need to set a personalized filter column I have a field with the age, now I need a formula column that gives me the segment :point_down:t2: AGE CALCULATED COLUMN < 15 kids => 15 & < 18. teenagers => 18 & < 31 young => 31 & < 45 mature => 45 older I’ve been trying to make an if then concatenated formula but I’m always getting errors. Any help? :pray:

I’m trying to add a column to a view that calculates - if X date (date a specific record was updated) is older than 2 weeks, show text “2 week” in the corresponding cell. And so on… for all the weeks - 2, 4, 6, and 8. Is this something that can be done? I’ve tried playing around with IF statements, but I’m not sure how to have it detect today’s date minus 14 for 2 weeks, today’s date minus 28 for 4 weeks, etc.

I have a little brain exercise and I’m running in a hamster wheel and cannot see to find the logic to use or if there’s a function out there that would solve my problem. I’ll take any help I can get :))) How can I create a formula doing the following behavior ? Variable : paymentDate - formatted in YYYY-MM-DD (ISO) I want to make each line appear if it fits the criteria. A paymentDate can belong to Quarter 1, 2, 3 or 4. Q1 is from Jan 1st to March 30th Q2 is from Avril 1st to June 30th etc… We have one month to prepare our reports after a quarter ends. What I want is to hide all the records from a current quarter until the deadline, i.e. all the payments made during Q1 2022 for instance will not be visible by my partners before April 30th (March 30th + 1 month). What I need is to be able to calculate a date (quarterDeadline, for instance) that will be April 30th, July 31st, October 31st, and then January 31st (of the next year). But this date of course needs to carry the year… because