Formulas

Hi, I have a situation that I am stumped on how to handle. I have invoices that we track payments against. We use four fields to do this: “Invoices”, “Current Payment”, “Paid To Date”, and “Balance Due”. Here is where I need help: We report weekly on what we collected to management. When we get just one payment that zeros out the invoice as paid, this works fine and gives me the data I am looking for. It is when we have progress payments (and not just one payment for the entire amount) that I need to figure out. To complicate things, many times we get multiple progress payments, and we never know how many payments we are going to receive (usually ranges between 1 and 6 - the joys of being an insurance restoration contractor!). I want to be able to take the “Current Payment” and copy it into another field so I can report on it as collected during this period. I then want to be able to add that to the existing “Paid to Date” figure so I have an accurate “Balance Due” amount. I am hopin

TLDR; From an “events” table that hosts records from different events I need to determine the highest value in two columns grouping by an event_id. So being events referring to football matches, the highest value would correspond to the last record with source_id 't3_nk5nz4'. What I want to achieve A single row with a coulmn that shows what which is winner team or it’s a draw. What I’ve tried Database view Grouping Formula Linking tables records What’s worked best and what’s missing Linking records from events grouping by match_id Missing is going over the different events, taking last considering there’s a timestamp field and comparing the two relevant columns to set a winner or a draw.

Hello, New to airTable and I love it. In setting up a database, I am running into a problem. Links from a long text string have to be replaced with new links. Is there a formula that an do this or does this have to be done manually? Here’s the text: <h4>UNIT OVERVIEW</h4><p><a href="https://apostolicfaithchurch.box.com/s/q77dw02m7v5mu11fg301ixmtbni56ebb">Men Who Trusted God</a><br></p><h4>MEMORY VERSE IN ACTION</h4><p>WHOSO — Point to others.</p><p>PUTTETH HIS TRUST — At eye-level, hold fists on top of each other as though holding to an imaginary rope.</p><p>IN THE LORD — Make an L with thumb and first finger of the right hand. Touch thumb to left shoulder, bring across in front of body touching right waist.</p><p>SHALL BE SAFE — wipe sweat from forehead as though just having a close call with danger.<br></p><h4>MEMORY VERSE VISUALIZED</h4><p>Though our memory v

We´re a learn platform with memberships. Each mempberships has its values in numbers. I need to replace/substitute those values into another values. This is what I want: 9=1 19=2 39=4 99=11 This is my formula: SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE({FOX XP AMOUNT},“9”,“1”),“19”,“2”),“39”,“4”),“99”,“11”) This is what I got: I think I need a solution to get the Formula know or read two digit numbers together. But I don´t know how. Would love if somebody could help me please.

hello guys!. I have an apparently simple situation to solve, but I’m still not used to the airtable formulas. basically I have a list of products and a product with the name “continue” which is an option that follows the flow of the system I am integrating with airtable. All products contact the Values ​​column. however I wanted the product “continue” not to concatenate. is it possible to insert this condition in the formula? The formula I’m using is: CONCATENATE(Products," ", “R$”, Value)

How can I calculate the # of years of service someone will have worked on their (future) anniversary date? The best I can come up with is # years they’ve worked as of today, but I want to list upcoming anniversaries and their years of service on those dates.

Hi there, I have a table with a single select field type called PROGRAM TYPE. It contains 3 different program types, which I’ll just call PROGRAM A, PROGRAM B, PROGRAM C. I also have a Date Joined field, and I’m creating a new date field called PROGRAM EXPIRES. PROGRAM A expires 12 months after its ‘Date Joined’ date, and PROGRAM B expires 3 months after its ‘Date Joined’ date. PROGRAM C does not need an expiration date and can be an empty string, or by default be set to 12 months as well. When I try: IF({PROGRAM TYPE}="PROGRAM A", DATEADD({Date Joined}, 12,‘months’), DATEADD({Date Joined}, 3,‘months’)) I get an error. I’ve also tried: IF({PROGRAM TYPE}="PROGRAM A", DATEADD({Date Joined}, 12,'months'), IF({PROGRAM TYPE}="PROGRAM B",DATEADD({Date Joined}, 3,‘months'), IF({PROGRAM TYPE}="PROGRAM C", DATEADD({Date Joined}, 12,'months'))) And still get an error. I’ve made sure all spaces/quotations are correct based on other suggestions in this forum. Would greatly appreciate any help with

Disclaimer: I’m moderately new to Airtables. I am working on a base that tracks commission earned for our sales department. Essentially there are two separate commissions rates that are earned: a pre-threshold rate and a post-threshold rate, where the threshold is an amount of commission earned (for example, the threshold could be $100,000, and the salesperson earns at a commission rate of 5% before they reach the threshold and at a rate of 3% after the threshold). I’m trying to write a formula that determines which commission rate to use based on the amount of total commission earned, but I keep getting a circular reference error, which I believe is caused by the fact that the total commission depends on which interest rate is used, causing the loop since both of these fields are dependent on each other. Does anyone have any advice on how to fix this? Sorry if the description is too vague, I can provide more information if need be. Thanks!

Hi everyone, I am looking for a Formula to convert Degrees Minutes Seconds (DMS) Geographic coordinates to Decimal Degrees (DD). Right now I have one column lwith this format : 57°34’57.8"N 6°19’30.9"W and I will need to split in two columns to have something like that : 57.582722 -6.325250 in order to use this mini estension : Embed Map of Airtable Records on Your Website(Embed Map of Airtable Records on Your Website - miniExtensions) Embed Map of Airtable Records on Your Website - miniExtensions If anyone has done this, thank you in advance !

Hello, I’m working on a membership database for a non-profit. I would like to have a column that denotes if the account is Active (meaning paid membership within the last year) or Expired (not paid within the last year). I have an expiration date column already in the table, but am having trouble creating a formula that works. I know it’s something like "If expiration date is before today, “expired” is after, “active”. However I cannot get any of my attempts at formulas to work.Thoughts?

I’m tracking properties that are on the market and haven’t sold. I can pull a data source which gives me “Days on Market” as a number. I can then use DATEADD() to subtract the num of days from the date entered 6/13/21 in this case, to get the start date of the listing. But there are cases where I know a property is being sold, but I don’t have a date it first hit the market, so it results in an error. Not sure if this will be a problem, but I tried to use an ISERROR formula but wasn’t able to get it working.

Is ít possible to format number for example 1,500,000 instead of 1500000 as display in the combined Formula? Thank you!

Hey everyone, I’ve been using Dedupe, which is ok but very manual. Is there a formula or automation that combines data if two unique identifiers are the same, and where columns are blank (then I can use dedupe) example in image: Any help much appreciated!

Hi there, So I feel like the Airtable formula support is severely lacking in a couple of ways, but here’s a really painfully obvious one: Let’s say you have a single base, single table, and there are two columns: Current Members and Valid Members. The Current Members column contains a comma-separated list of current members, like Bob, Sally. The Valid Members column contains a comma-separated list of ALL allowed members, like Bob, Susan, Tim, Jim Now, I want to create a third, formula-powered column called Are Current Members Valid? and that will return 1 if all Current Members are present in the Valid Members column; 0 will get set otherwise. In order to implement this correctly, I sort of need to: Collect each Current Members item as an array – which means I need some sort of SPLIT() formula that will split a string by each comma into a string array Iterate over each item in the string array and check to see if each item is present in the Valid Members column Because no Ai

Good morning everyone, I’m stuck on a simple problem. do I need to list items from a segment or else list a fixed item “exit” as would the formula? the first step I’m using the following formula: IF(OR({Company}= ‘@company’,{Type}= “Knowing the Services”,{stock}= “YES”) the second part of the formula in a dumb translation would be : or type = continue how would the assembly of this formula be? The intention is that the formula lists the products that are in the column “know our services” and also list the product “continue”

Hey, do you know how can i get in a field, only the hour format Hour/minutes like 09:34, without the date ?? thank you

Hello everyone! Thanks in advance for your time. I made a table to log tasks, where I put one column for level of complexity (S-small, M-medium, L-large) and another column for volume of work (S-small, M-mediu, L-large). Another third column sums it into SM, SL, SS, MM, LL etc and also sorts it by a custom order. How can I make a formula for one more column for URGENCE LEVEL, where I can put a formula that says like IF Hierarchy = SS, SM or SL then show the word “HIGH”, then IF Hierarchy = MM or ML, show the word “MODERATE” and IF Hierarchy = LL, then show “LOW”. Idk, it seems simple, but I advance I am a creative that managed into adulthood without learning many formulas, god only knows how. So after failing the whole afternoon I came to look for your wisdom. Thank you guys for reading me! :grinning_face_with_big_eyes:

Hello :slightly_smiling_face: New topic new question : I want to do a DATEDIFF formula really basic). I did : DATETIME_DIFF({FIN PE},{DATE ENTREE},‘days’) But it’s really weird it work for some lines and don’t for the others. No links between the lines there are working with … The two fields use in the formula are two dates with european format. I don’t understand where i’m going wrong … please help

I am building a competitive pricing sheet and have a pricing column that is formulated to give me pricing based on a specific margin, however, I then need a column that would round that pricing to the .95. Does anyone have something like this?

Hi there Airtable community! I’m having trouble with an IF statement. I have a lookup column with two values, Turned or H/L. If the value is Turned, I want my formula to add the values in each of the shoe sizes listed. I’m getting an ERROR every time I try it though. Is it because the value (H/L) is a lookup value? The formula saves correctly, but the resulting value is #ERROR! Screenshot attached too… thanks for your help :slightly_smiling_face: IF( SEARCH(“H/L”, {Toe Puff}), SUM({2},{2.5},{3},{3.5},{4},{4.5},{5},{5.5},{6},{6.5},{7},{7.5},{8},{8.5},{9},{9.5},{10},{10.5},{11},{11.5},{12},{12.5},{13},{14}) )

I know that these types of questions have been asked several times here, but I haven’t been able to replicate suggestions from other threads to get the desired empty cell for my formula. Thanks in advance for any assistance! My formula is: DATEADD({Associated Event Date}, {Days before/after event date} * 1, ‘days’) some of my tasks do not have a value for the {Days before/after event date} field. In these cases, I would like for the cell to show up empty rather than show #ERROR. Please help :grinning_face_with_big_eyes:

Hi there, Hope you’re doing well ! Can someone help me to formula-translate “has none of” please ? :slightly_smiling_face: What i need to do is to make a webhook call to filter some results and only take the ones which does not contains “SFR or Orange” in “which_network”. Thanks a lot

Hello :slightly_smiling_face: I am a beginner in the creation of Airtable formulas. Is it possible to create a formula IF(AND(value1,value2,value3) with at least 3 conditions ? I only succeeded it with 2 conditions. If it’s not possible, do you know how i can make it with others formulas ? Tx u very much for your help/time

I’m tracking bicycle tire air pressure fills and my current setup include 3 main columns: Begin Date, End Date, and Duration. Duration is a DATETIME_DIFF function, calculating the days between air refills. The problem is, I have to copy each End Date to the subsequent row’s Begin Date every time. This is quick on the desktop, but on iOS it’s extra steps. Ideally, I would have just one date column and the duration column would calculate the difference from the previous record. For example, in Google Sheets, the function would be able to easily calculate the difference between cells A1 and A2. I haven’t figured out how to do this in Airtable. This article mentions how to "find the difference between a record’s date and today’s date. Airtable Support | Calculate the difference between two dates I’m looking for a replacement for the “NOW()” formula to be the previous record. Thank you!

Hello Everyone! Would greatly appreciate any help on this matter. I have a base with two rows: people can enter their name and a date. Is there a formula I can use in the third row which would check how many entries are recorded for the same date, and could show a message along the line of: “Too late, there are already 8 entries with this date”. Thank You!