Skip to main content

Formulas

Leverage this space to unlock the power of Airtable formulas.

  • 5,941 Topics
  • 18,238 Replies
5941 Topics
Michel_Billiau
Michel_BilliauKnown Participant
asked in Formulas
Is there a special field for column number in a view?

Hi, I would like to use the column number in a view in a formula. Example : If a record is listed as 5th is a view, I would like to use the number 5 in a formula. Is there an internal field giving me this number ? PS If this exists, is there a list of internal fields that can be used ? like RECORD_ID, …

102
Michel_Billiau
A
AM858Participating Frequently
asked in Formulas
Find the maximum of three date fields on one table

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. Formula to choose Earliest Date from list of 6 dates Dates and timezones Hi everyone! I have a table with a list of 6 different release dates - I’m trying to create a field that looks at all of those dates and picks the earliest/first one as “Initial Launch.” IS_BEFORE only seems to work with 2 dates and the factorial of 6 dates is 720 possibilities of combinations so I don’t thi

984
Kamille_Parks11
Pascal_Vallet
Pascal_ValletInspiring
asked in Formulas
Formulas for Date

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 ?

63
R
H
Heather_ParkerNew Participant
asked in Formulas
"Week of" vs Week Number - Issue with Formula

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(“Week of”," ",DATETIME_FORMAT(DATEADD({Due Date},-DATETIME_FORMAT({Due Date},‘e’)+1,‘days’),‘M/D’)) The specific error is as follows: Sorry, there was a problem saving this field. Invalid formula. Please check your f

342
H
A
Andrew_Miller2New Participant
asked in Formulas
Using Rollups to Return Text, Specific Records

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 aggregation formula of t

525
R
M
Matthew_RichardNew Participant
asked in Formulas
Complicated date difference calculation

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 be the datediff between the IN date and the last day of the previous month. Example would be (OUT 4/20/21, IN 5/14/21= 14 days) Combination 3, OUT date begin

112
M
L
LEROY_FredericNew Participant
asked in Formulas
How to check if attachement field is empty or not

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

729
Heather_Brown
R
rmmKnown Participant
posted in Formulas
Date format formula

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

34
R
K
kennypurnomoKnown Participant
posted in Formulas
How to filter via Search Record in Integromat

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

51
ScottWorld
Pascal_Vallet
Pascal_ValletInspiring
posted in Formulas
Help with a formulas

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 field is linked to an other tab named STUDENTS. I

32
Pascal_Vallet
M
Mark_ComishKnown Participant
posted in Formulas
Formula for rollup field and a comma

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.

32
M
M
Mark_ComishKnown Participant
posted in Formulas
Formula to mark status as "past" if not the latest or "current" if the latest

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.

00
M
W
Wayne_MerlinoInspiring
posted in Formulas
Using formulas to track current payments, progress payments, and balance due with dates to filer on - need help!

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

20
W
D
diegoaguilarNew Participant
posted in Formulas
Summarize information from a base table

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.

77
Kamille_Parks11
R
rmmKnown Participant
posted in Formulas
Replace urls in text string

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

2110
ScottWorld
R
Red_FoxNew Participant
asked in Formulas
Formula to replace/substitute certain numbers into another number

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.

323
Kamille_Parks11
L
leandro_medeiroInspiring
posted in Formulas
How not to concatenate a particular product from a list

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)

35
Kamille_Parks11
J
Jennifer_PoiryNew Participant
posted in Formulas
Calculate # of years of service based on next anniversary date

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.

31
Kamille_Parks11
W
Willo_Co_SupporNew Participant
posted in Formulas
Formula IF statement with DATEADD based on single select value

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

158
R
B
Brennen_McManusNew Participant
asked in Formulas
Need help removing circular reference

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!

273
kuovonne
T
Thibaud_DonzierKnown Participant
posted in Formulas
Formula to convert Degrees Minutes Seconds to Decimal Degrees

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 !

194
T
A
Alyssa_ThomasNew Participant
posted in Formulas
Expiration Date on Membership Database

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?

11
Kamille_Parks11
A
AM858Participating Frequently
asked in Formulas
Getting ERROR in Calculated DATEADD Blank Records

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.

113
Kamille_Parks11
P
Phong_VtNew Participant
posted in Formulas
Format number unit when combining in Formula?

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

32
Justin_Barrett
J
Jack_RichardsonNew Participant
posted in Formulas
Alternatives to DeDupe - any ideas?

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!

21
J

Badge winners

Show all badges
Terms & Conditions