Formulas

Looking for help creating a formula that can determine how good a certain price is compared to an average price calculated in Airtable. For example, let’s say the average price of something in a record is $100. If my price is within 5% (+/-) of $100 (i.e. $95-$105) it should conditionally show “Average”. If between 5%-15% higher (i.e. $105-$115) it should conditionally show “Fair”. If more than 15% higher (i.e. $115+) it should conditionally show “Poor”. Alternatively, if between 5%-15% lower (i.e. $95-$85) it should conditionally show “Good”. And if more than 15% lower (i.e. $85-) it should conditionally show “Excellent”. Alternatively, a rating system (e.g. 1 star being the worse and 5 stars being the best) would work too. Anyone know if something like this is possible and if so how to do it? Thanks in advance!

Hello, I have this formula below: IF( AND( {Course Payment Duration}, DATETIME_DIFF( TODAY(), {Course Joining Date}, ‘months’ ) > {Course Payment Duration} ), ‘Payment Complete’ ) this formula is meant to tell me when as user has paid a subscription based on the amount of months it been since they joined but it seems to only show payment complete when the next complete month has passed. An example is if the Course payment duration is 3 months the payment complete only changes when it it’s 3 months + the days of that month have passed. I need the payment complete to change when the 3 months have been reached. Hope someone can help Chris

What I am trying to do is use 2 nested if statements to check if 2 variables are blank (A and B). If A isn’t blank it will post A, but if A is blank it will then check to see if if B is blank and if B isn’t blank it will post B. But if B is blank as well it will post the safety string C. I thought below would work but its not working. A and B are cells and C is a string that I have as a safety net. IF({A} =BLANK(),IF({B}=BLANK(),“C”,B),A) Any help would be great thanks!

Hi! My formula to flag an older blog post as in need of an update isn’t working and I’m not sure why. Here’s the formula: IF(DATETIME_DIFF({Date Last Updated},TODAY(), ‘months’) > 12, “Update”, " ") The logic I want is this: If more than 12 months have elapsed between the date indicated in the {Date Last Updated} field and today, I would like the cell to say “Update.” Else, blank. Can anyone advise?

I want to use airtable so bad to solve problems, but I’m struggling to justify the pro plan when I can’t just simply use summary values in a formula or be able to have a column of totals to then auto sort. Here I am every couple months trying to reverse engineer this crazy multiple table, linked records (which I can’t even auto add, I have to manually enter for new records), rollups, with formulas on top of that… and each time I have a variation in the problem I’m trying to solve, it means cracking open a new can of worms to get it done, often spending hours doing something that google sheets can do in seconds. If I hear one more person say “It’s not a spreadsheet, it’s a relational database” again … or if someone casually says that zappier can fix your problem without mentioning the high monthly cost and added work of integrating it all together with some other form software … Let’s take Airtable to the next level and address so many people’s issues by allo

I am currently working on setting up a time sheet on one of my bases. I am having an issue with a formula to ID my entries. I am trying to Concatenate the date, name of person, and total worked time all in one. I have the date and name figured out but I can’t get the total worked time to format in anything but seconds. CONCATENATE(DATETIME_FORMAT({Start Time},‘l’), " - ", Who, " - ",{Total Hrs}) {Total Hrs} is currently this formula, DATETIME_DIFF({End Time},{Start Time}), which is fine because I can change the field to be a duration. I have explored the community but haven’t been able to find a solution. Thanks in advance for your help!

Could someone help me create a formula for the following scenario? We run an accreditation system, and have a policy that went into effect on January 1, 2018 saying that programs have five years from the date of their application to submit their report to us or their eligibility will expire, but programs that applied before that policy went into effect were grandfathered in and have five years from January 1, 2018. If it’s helpful, my application date field is in MM/DD/YYYY format, and I’d like the expiration date to generate in the same format. If (application date field) is before 01/01/2018, (expiration date field) is 12/31/2022. If (application date field) is after 01/01/2018, (expiration date field) is 5 years after (application date field). If (application date field) is empty, (expiration date field) is blank Thank you!

Hello, I’m trying to figure out a way to calculate my “Price without Tax”. I wrote something like this but it’s not working. IF({VAT}=20, {Price with tax}/1.2), IF({VAT}=5.5, {Price with tax}/1.055)) VAT is a Percent As you can probably see i’m a true beginner. Any help would be appreciated. Thx

Hi there. I am working with a Rollup up field linking between a Series table and ID table. The ID table has a field called Episodes. What I am trying to work out is how do I display the number of episodes linked from the linked ID table and the count of each. Example: Episode Roll Up 9, 5, 3, 3 (so there are four ID records linked to the Series table and from the ID table’s Episode field there is 1 x ID from Ep 9, 1 x ID from Ep 5 and 2 x IDs from Ep 3) What I would like to display either directly in the RollUp field or in a companion Calculation field is the following: 3: 2 5: 1 9: 1 (so I’d like to be able to order the Episode numbers as well to make it cleaner to view). I have searched thru the Community posts but can’t find anything that addresses this particular query. Any help would be very much appreciated!

Hi, Im trying to create a table in Airtable that would auto-populate some totals from other tables in the same base. Basically, I want to see what the sum of a total is in a specific view in a separate table. I have a field labeled “Total Words” and the sum of those numbers changes depending on the view. I know how to do this in Excel, I would create a SUMIF formula. I want the cell to display the sum based upon if a record is categorized as “Ready to Post”, “In Progress”, or “Needs Assigned”. I can’t figure out if this is possible. Please help!

Hello, here is my formula below IF(DATETIME_DIFF(TODAY(), {Course Joining Date}, ‘months’) > {Course Payment Duration}, ‘Payment Complete’, ‘’) What I need is to ignore the course payment duration field if it is blank, at the minute this is still getting marked as payment complete. Thanks in advance Chris

Hi! Very new to formulas and slogging my way through thanks to all the great advice in this forum! I’m trying to get the formula to return nothing if {days b/w successions} is blank, and to calculate the next date if there is a number in that field. I wrote this, based off what I gleaned from other posts, but it isn’t working. IF({Days b/w successions}=BLANK()," ", DATEADD({Field Date 1},{Days b/w successions}, ‘days’)

Hi there, I’m hoping someone may be able to help with a formula to update a status, based on the date being entered in another field I’m going along the lines of: IF: DATETIME_DIFF({Date Cleared},NOW(),‘days’)<30,“ :open_book: Recently Closed” DATETIME_DIFF({Date Cleared},NOW(),‘days’)>30),“ :closed_book: Closed” “ :exclamation: Open” Thank you

Dear All, I’m trying to build an app to generate the YouTube timeline text that should show the running time of a segment based on the episode start time. I was able to manage everything, but the time, that should be presented as 0:05 is being presented as 5. Is there any way to concatenate a time field on a text field with all numbers? Thanks in advance. Best regards, Joao Coutinho

Hi Community! I’m working on a table that allows my company to easily track and time rental property application processing times. Fields I have are listed below: Unrelated fields are hidden. We want {Hand Over Timer} to start timing once a record gets updated to “Processing”. We don’t want to track time over weekends. For this, we decided to implement the help of the WORKDAY_DIFF() function. The problem is, this works horribly when time is involved. I’ve had to create this monstrosity to make it accurate: IF({Processing Activated (Automation Requirement)}, IF( {Application Tagged As Handed Over}, IF( (WORKDAY_DIFF({Application Started Processing}, DATETIME_FORMAT(SET_TIMEZONE(NOW(), 'Australia/Brisbane'), 'M/D/YYYY h:mm') ) >= 1), (((WORKDAY_DIFF( {Application Started Processing}, DATETI

Hello, I have set up a integration of airtable and asana with integromat. Now i would like to update task in asana with a comment on the due date. I would like to add this comment at 5 pm on the day of the due date since our office closes at 6 pm. The assignee could then reply with reason why the task was delayed. Currently, the Due date column doesn’t keep time enabled and i would like to keep it that way. Default is 12 am i think. Now, i created the same column ‘due in’, but i want it to be in hours which become zero at 5 pm on the day of the due date. In Integromat, i can simply filter all records where the ‘due in’ is zero or less to add a comment. Using, Now () in formulae in returns in hours but i want it to be in my own time zone. I am using this formulae. DATETIME_DIFF(NOW(),{Due date},‘hours’) Please guide.

Hi I found a lot of information in this website, thank you very much to all the helper ! In fact, I practicly found my answer in this thread : Parsing Text without Split() But I’v still have a problem. For different class, I’v student. Sometimes 3 students, sometimes 4 or 8. It depend. So I’v a form for student who want to apply to the course. Here the result for example : Brad Pitt, Christian Bale, Matt Damon, George Clooney I want a column “Brad Pitt”, an other “Christian Bale” etc… @Bill.French help me a lot with the thread and i’m globally ok except for the last name who never comes up. It’s a lookup field btw. For the first name I have = MID({xxxx (from xxx)}, 1, SEARCH(",", {xxxxx (from xxxx)})-1) For cut the last name = RIGHT({xxxx (from xxxx)}, (LEN({xxx (from xxxx)}) - FIND(",", {xxx (from xxx)}))) Then the s

We have a table we’ve created where we need to bring a SUM of multiple records in another table over. For instance, we’ve grouped users in TABLE1 which then shows us the SUM spent for that user. In TABLE2, we want to show the SUM of that group in a field so we can export it with the additional information contained within TABLE2. Any ideas?

Hey everyone, I’m trying to add a field that will return either a calculation or text based on whether another cell is empty or not. I have a working formula for the difference between two dates, but I want to put that inside an IF formula that will return the value of the difference in days if the field is populated or a text “Not Yet Resolved” if the field is empty. Here’s what I have at the moment: IF({Resolved} != BLANK() , VALUE(DATETIME_DIFF(Resolved, Created, ‘days’)) , “Not Yet Resolved”) But it is returning an error. Can somebody provide a solution? Thanks

I can get a portion of the formula to work properly, however, I need additional steps and I’m stuck with the formula portion. My goal is to use this setup to generate an email using zapier. My current setup Current Formula: IF(DATETIME_DIFF({Request Status} !=“Pending Quote”, TODAY(), ‘days’) <= 3, ‘Send Reminder Email’) I only want the formula to work if Request Status is “Pending Quote”, but, it’s currently reading the blank fields too. My current formula also is set for a Reminder email at 3 days, but I’d also like to send an email at 5 days, and every day thereafter if the Request Status has not updated. Also, is there another way to run zapier without creating a view? I prefer not to move these records to a different view only to send an email. Any help is appreciated.

I’m trying to put together a formula IF statement to extract certain parts of a URL depending on what is found in a specific cell for that record. In plain language: IF {Published on/by} is “Website.com” pull 6 characters after “post=” in the {URL or Permalink}, OTHERWISE, IF {Published on/by} is “YouTube.com” pull 15 characters after “watch?v=” in the {URL or Permalink}, OTHERWISE say “no URL found” I tried this but it doesn’t work: IF({Published on/by}=“Website.com”),MID({URL or Permalink},FIND(“post=”,{Published on/by}), 6), IF({Published on/by}=“YouTube”),MID({URL or Permalink},FIND(“watch?v=”,{URL or Permalink}),15),“no URL found”) HELP! Thanks! IF({Published on/by}=“DBC/YouTube”),MID(FIND(“watch?v=”,{URL or Permalink}),15),“not known”)

Trying to workaround the fact that can’t do both OR and AND filters by using an IF statement. This is just the beginning but I can’t get it to throw the correct result. Suspicious it is because it doesn’t work for Fields that are linked to other tables, but not sure why it wouldn’t. Am I missing something else? Perhaps because it is a string? It seems like a really easy IF statement, but it throws “No” when it should throw “reponsible”). Help! If this can’t be fixed perhaps you have other ideas for how I can show only those entries for which a specific entity is either responsible or accountable (ideally with the ability to group by which they are)/

Hi! In my base I have a list of my customers by phone number. We are trying to link if they attended a zoom session. I have a script that can link if it find name or phone number matches. Sometimes though customers dial in by phone. Registration reports from zoom provide numbers in a string of digits with a country code at the front. Ex. (123) 456 - 7890 would show up as 11234567890 For my script to work I need to have the two columns that have the same values. Is there a way to format using formulas either column to make them match. Any ideas? Gabi

Hi… First post here… I’m trying to simply search a lookup field for a certain Order ID number (397147) and have the formula field display “VIP” if it is found, and “NON-VIP” if it is not found. The formula is only showing “VIP” in those records where the Order ID number that is being searched for is either the first or last Order ID in the lookup field. If 397147 is in the lookup field, but not the first or last Order ID, it displays “Non-VIP”. Here’s the formula: IF(FIND(“397147”,{Product1 ID (from Orders)}), “VIP”,“Non-VIP”) Thanks in advance for any help!

Want to create a Full Name value from name elements. Wrote this simple formula: CONCATENATE( {First Name}, " ", {Middle Name}, " ", {Last Name} ) If there is a value in all three fields, it works fine, returning a result like (say) “William Jefferson Clinton”. But it also works fine if there isn’t a Middle Name value – and that is contrary to what I expect. If Middle Name is left empty, it will return “Bill_Clinton”: I’m using an underscore there to represent a single space. What I expect is for it to return “Bill__Clinton”, with two spaces in there. Why doesn’t it? What am I missing?