Formulas

I have three tables, Properties, Contacts, and Companies. Each table has a Last Unsuccessful and Last Successful contact field. Sometimes I have contact information on one or multiple of these tables (i.e. if a company owns multiple properties then I’ll create the linked relationship). I need a way to find the maximum date of when I last spoke to someone, whether I’m looking at either of the tables. On the table below I have two lookup fields and a natural date field for both successful and unsuccessful contacts. I referred to the thread below but not sure why I am getting an error.

I would like to create a formulas to add the specific school year if the date in the column “DATE” is between DATE1 and DATE2 For example: IF date is 5/20/2021 THEN in the column school_year I will have 2020-21 IF date is 9/20/2020 THEN in the column school_year I will have 2020-21 IF date is 9/20/2021 THEN in the column school_year I will have 2021-22 What formulas should I use ?

Hi everyone - I’m still very new to Airtable and ran into a formula error that I’m trying to solve. Ideally, we would like to group tasks by the week they are due. I was able to find the formula to output the week number but can’t seem to find one that works that will produce the following output: “Week of 06/21”. I did find the following two formulas from a previous post: Display the "week of" for a given date starting with Monday CONCATENATE(“Week of”," ",DATETIME_FORMAT(DATEADD({Date},-DATETIME_FORMAT({Date},‘e’),‘days’),‘M/D’)) DATETIME_FORMAT({Date},‘e’)+1,‘days’),‘M/D’)) However, when I go to add the formula to my calculated field it returns an error. Could it be because my {Due Date} field is calculated as well? This is the formula that I am using: CONCATENATE(“Wee

Hi Airtable Community! In my Airtable base, I’ve got two main tables: one for stories written by kids, and another for notes that volunteers write to the authors. These tables are related through a one-to-many linked record setup: there can be multiple notes for a story, but not multiple stories for a note. I’m trying to make two rollup fields: one that displays the text of the first two notes a story receives and one that displays the text of every note after the first two. I’d like to email notes back to authors after they have two notes and then again when they’ve accumulated several more, and would use each rollup field as part of the automation for the email body. The additional wrinkle is that the rollup should only count notes that haven’t been flagged (and therefore shouldn’t be returned), and there are handwritten notes too. My thought was to add an Autonumber field in the notes table–each note will have to have a higher number than the last. Then, the

Hi Community, I have a cruddy report that cannot give me the data i need which forces me to calculate the number of days a person has been helping our organization and I was hoping there might be a formula that can help automate the calculation a bit, but I cannot wrap my head around it (terrible with IF formulas). The report is run once per month and will produce 4 different combinations of dates for the in and out field. Combination 1, OUT date is prior to the start of the month, and IN date is after the last date of the reporting month OR blank . This calculation would then be the same as the number of days in the month. Example for May (OUT is 4/20/21 and IN is 6/5/21 or blank, would = 31 days) Combination 2, OUT date is prior to the start of the month, and IN date is within the month of reporting. This calculation would

Hi everybody, I’d like to check if an attachement field is empty or not. Someone knows how to do this ? Tanks a lot. Fred

Hi there, Can anyone please help me convert the date in this format, Fri Jan 13 2017 00:00:00 GMT+0000 (Coordinated Universal Time) to this 01/13/2017 format? Thank you

Hello, I am using integromat and i want to “Search Record” based on “Parent model” and “Map years”. So i want to achieve Parent model equals Accord AND Map years contains 2012

I am new to Airtable. I am building a tool to collect behavior incidents in the school where I am Principal. After testing multiple tool, I came to the conclusion that I need to create my own application and Airtable expends the possibilities. Here is what I am trying to achieve: In a first tab called INCIDENT I can create incident record with one field being the TYPE of incident. This tab is using variables located in an other tab named TYPES. On this tab TYPES, I have two information: the name of the type (bullying, cheating, etc…) and the point associated to the incident (a number value). When a type or multiple types of incidents are selected, it shows the point on the next column. If two types are selected it will show point 1, point 2. On the next column I have created a formula to add the points. In one of the column I have a drop down menu which can select the name of the students responsible for the observed misbehavior. This f

I need help with an IF formula. I have a roll-up field that sometimes has 1 number and sometimes 2 or more numbers separated by commas. I have a formula that removes everything after the first comma but if there are no commas the formula field is empty.

I pull in data via CSV files (28 of them) every quarter. The “FilingID” field is always a unique number. The “DateSubmitted” field corresponds to the FilingID as the higher the number the more recent for a given Company (1B1) field. and the “IE!” field is always the same for a given company. What I am looking for is a formula or script that when a record with a higher numbered FilingID or DateSubmitted (whichever is easier) is added any record with the highest FilingID for a specific company (1B1) or (1E1) is updated to a “Current” status and any existing (older) record is changed to a “past” status. I really only need this for 1 table. Any suggestions would be appreciated.

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 “

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

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

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

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 – wh