Skip to main content

Formulas

Leverage this space to unlock the power of Airtable formulas.

  • 5,944 Topics
  • 18,247 Replies
5944 Topics
Ashley_Jackson
Ashley_JacksonInspiring
posted in Formulas
How to search for what's missing?

Hi all, I'm working on a donor management system for a non-profit org.  Donors make pledges to give $x on x recurring basis, usually something like $50 a month.I've got a table for Donors, one for pledges, and one for donations.I need to have a way to check whether pledges are being fulfilled by donors.  So if John Smith pledged $50 a month, has he actually sent $50 a month?But how do you search for what's NOT there???  I am stumped on how to approach this one!Any thoughts???

134
pressGO_design
A
AndreavSNew Participant
posted in Formulas
Pulling Max Value

I have a table where different group clients submit weekly weights on a form.I'm calculating "starting weight - weekly weight = X". With each submission, I send them an automated email that records what their weight loss is, BUT even though their weekly weight loss differs, I want to only send them the max weight they've lost of all the weeks submitted.e.g. starting weight - 200lbsweek 1 submission = 194 (6lbs lost)week 2 submission = 196 (4lbs lost)week 3 submission = 197 (3lbs lost)So their record will continue to pull only the "194 (6lbs lost)" until a higher weight loss is submitted. How do I do that? The rollup fields are not working for me - or am I missing something! The current rollup field is reflecting all 0 but there are no zero entries.

11
TheTimeSavingCo
C
CSM_PFParticipating Frequently
posted in Formulas
Split text to multiple columns

I want to separate the following text in different columns :Column A : @Ben11//test-bug//Feb 22, 2024 3:54pm UTC//Budget//Test again//Test again//ModerateColumn B : @Ben11Column C : test-bugColumn D : Feb 22, 2024 3:54pm UTCColumn E : BudgetColumn F : Test againColumn G : Test againColumn H : Moderate

1141
kuovonne
S
SpelaUKnown Participant
posted in Formulas
Stop the countdown when status change to sent" or "refund"

Hi!I have a countdown for our warranty cases, which needs to be completed in the 45 days or less. Our customer fills out the Form, and the countdown stars when the Form is submitted. So the first field is "Form date" DATETIME_FORMAT(CREATED_TIME(), 'D. M. YYYY')The second field is "Days" DATETIME_DIFF(TODAY(), {Form date}, 'days') And the third field is "Countdown"45-{Days} This works fine, but the problem is, that when the status is "sent" or "refund" the countdown don't stop. So the point is, that I want to know how long one case was open. I can also use just one status, let's say "complete" but now I stumble upon one problem.I don't have a specific date field for the end of the case (it depends on the case - some wants refund, some wants exchange or repair). So in this case I have two date fields (date of a refund and date of a shipment). So it's little complicated. Hope someone knows the anser to my problem. 🙂Have a nic

81
TheTimeSavingCo
C
cathdresNew Participant
posted in Formulas
Separating string of text based on commas

I have a string of text in one column that i'd like to separate into individual columns. For example: Row 1: K, 1st, 2nd, 3rd, 4th, 5th, 6thRow 2: preschool (ages 4+), K, 1st, 2nd, 3rd, 4th, 5th, 6th, 7th, 8thRow 3: ages 3+, preschool (ages 4+), K, 1st, 2nd, 3rd, 4th, 5thFor my existing Row 1, I want a formula that will return each in its own column:K1st2nd3rd4th5th6thAs you can see, the length of characters isn't consistent, but the comma consistently separates the fields I want. How can I use the comma to separate out the text? I've found a way to extract the text before the first comma (LEFT({selection 4}, FIND(",", {selection 4}) -1), but can't figure out the rest. Thanks in advance!

661
ScottWorld
N
Nicholas_TimmsNew Participant
posted in Formulas
Assign a Month & Year based on if a date falls in period.

Hello All,I wish to create an if formula which will return a Month Year result based on if a date falls on or between two dates.For example, if date A is on or between date X and date Y then return Month YearThink along the lines of budgeting and expenses:if Paid Date is 01/02/2024 (Date A)and because Date A is on/or between 15/01/2024 (Date X) and 14/02/2024 (Date Y) (Budget/Expense Period)Then return January 2024 (because January period is 15/01 to 14/02)

202
N
J
jsaduakassovParticipating Frequently
asked in Formulas
Template help

Hello everyon, so I have created this project management based on the template. I have got around 10 people in the group and how can I do that when each person logs into that base it will show tasks by name individually? Cuz for now you have to do it in condtions 

97
TheTimeSavingCo
S
saasypNew Participant
posted in Formulas
Reproduce a INDEX-MATCH Excel formula

Hi everyone, I'm trying to reproduce the following Excel formulas in Airtable, which returns the third column from the "Tabella gest" table if the match is satisfied. INDEX('Tabella gest'!$B:$D;MATCH(1;('Tabella gest'!$B:$B=G2)*('Tabella gest'!$C:$C=BX2);0);3)I've two tables:- "Tabella gest", from which I have to retrieve the third column if the match is satisfied and use the columns B and C in the match, called respectively "Canale commerciale" and "Classe ABC" - "Input", which has the columns G and BX for the match function, respectively called "Tipo codice materiale" and "ABC ordinato" I don't have any idea if this is possibile and how I could do it, so I would appreciate anyone's help. 

696
S
A
Airtable_admin11Participating Frequently
posted in Formulas
Add condition to formula to use column IF not Empty

Hello! I want to add something to the formula below. I want to add the condition that IF the column 'Fixed exchange rate' is not empty (there is a number filled in) THAN that number should be used BEFORE using the Forecasted exchange rate. IF the Column 'Fixed exchange rate' is EMPTY, than use forecasted exchange rate. Current Formula: IF(AND(   {(Hide) Weighted Historical Exchange Rate ⚙}=0, {Bankbook historical exchange rate}=0),{Forecast exchange rate ⚙},IF(AND({(Hide) Weighted Historical Exchange Rate ⚙}=0, {Bankbook historical exchange rate}>0),{Bankbook historical exchange rate},{(Hide) Weighted Historical Exchange Rate ⚙})) Thank you!

31
TheTimeSavingCo
A
Alex_CurtisParticipating Frequently
posted in Formulas
Split 10 URLs (separated by commas) in One Text Field to 10 new fields

I’m pulling in 10 urls from Zapier in one field. (I can’t seem to split them despite trying the split text formatter) I have seen some similar formulas to split text but can’t get them to work either. I need some help so I can separate the URLs out.

2513
T
J
JustineC1New Participant
posted in Formulas
Formula with empty value in field

Hi, Could you please help me with a formula I have 3 values: Estimated start dateEstimated end dateEffective end dateToday's dateI want to display "in progress", if I have no value in my effective end date cell and if today is inferior than estimated end dateI've tried the following formula but it doesn't work: IF(AND(({Effective end date})=BLANK(), {Estimated end date (Deadline)} > TODAY()), "In progress")Could you help me please? Thanks,

186
Alexey_Gusev
auekk2787
auekk2787Inspiring
posted in Formulas
Identify duplicates from lookup array

Is it possible to identify if there are any duplicates in a lookup array? I have 3 tables, all linked together: Entities (companies)MeetingsMonthsEvery Meeting is linked to a month (for various uses) and to an Entity. From the Entities table, I have data as such - the linked meetings and a lookup of their linked months. What I'd like to do is identify if a month appears twice in any row. I don't want to eliminate duplicates (i.e. ARRAYUNIQUE). Instead I need to see if 2 or more meetings occurred in the same month for any company. Is there any way to do that? 

364
Alexey_Gusev
I
itsmeayyubNew Participant
posted in Formulas
Date formula

I have a field that includes:NameEmailPayment linkPayment_made (checkbox)Created at (Jan 14)Send_reminder_1 (checkbox)Send_reminder_2 (checkbox)If the payment_made is not "check" even after 3 days.I want a send_reminder_1 to be auto-check or something with a formula that will say true.-------If the payment made is not "check" even after 6 days.I want a send_reminder_2 to be auto-check or something with a formula that will say true.-------How can I do this using formula?

11
Dimitris_Goudis
Tt25_2
Tt25_2New Participant
posted in Formulas
Multiple IF statements

I'm trying to create the following automated options in a project management base:2+ days remaining1 day remainingdue today!1 day overdue2+ days overdueThis is my attempt, but I'm struggling to work out how to write the multiple IF statements required to make it happen: IF((ISERROR((ABS(DATETIME_DIFF(TODAY(), {Milestone Date},'days')) & IF((DATETIME_DIFF(TODAY(), {Milestone Date},'days'))<0," Days Overdue", " Days Left"))))=1,"",(ABS(DATETIME_DIFF(TODAY(), {Milestone Date},'days')) & IF((DATETIME_DIFF(TODAY(), {Milestone Date},'days'))<0," Days left", IF((DATETIME_DIFF(TODAY(), {Milestone Date},'days'))=-1," day left", " Days overdue"))))

1107
TheTimeSavingCo
S
seaTurtlesRUSNew Participant
asked in Formulas
Merging records with multiple parameters (script)

Hello, I'm trying to merge records in my table when 3 criteria match. I've been able to identify which records are duplicates, but I am having trouble merging them. Logically I believe my code should do what I want it do, I am not super familiar with asynch and I think that is what is messing me up. When I run this code it throws: Syntax error: await is only valid in async functions and the top level bodies of modules [script.js:33:9] I've been reading through documentation and discussion posts, but I'm not sure what is happening to cause it to throw an exception. Any help would be appreciated.   var table = base.getTable('Testing_500'); var query = await table.selectRecordsAsync(); //let records = query.records; let upPN; let upEM; let upID; //identify duplicates let duplicates = query.records.filter((record) => { return query.records.find((potentialDupe) => { let fName = record.getCellValue("First Names") === potentialD

323
S
H
hauwiInspiring
asked in Formulas
Convert numbers to pre-defined letters - to create a simple password

Hi. I have a very basic password system that uses letters to denote numbers. The numbers come from a client's DOB.I have a date (DOB) field and another field using DATETIME_FORMAT to only show the numbers (DDMMYY). That's all fine.I would like to have a new field that displays a particular letter for each number - to create a very simple password. Lets say: 1=a, 2=b, 3=c, 4=d, 5=e, 6=f, 7=g, 8=h, 9=i, 0=jIs there a formula that would do this please? 

172
H
P
perfectNew Participant
posted in Formulas
How to combine two fields in pair but separate lines?

My airtable has two fields, for example, 1st field name is "Test" and 2nd field name is "Solution". Records from both fields are come from Lookup of other fields so multiple records appear in "Test" and "Solution". i.e. one record of 'Test' contains 'Test item 1, 2 & 3'. One record of 'Solution' contain 'Solution item 1, 2, & 3'.I use below formula to present Test item 1, Test item 2, Test item 3 in separate lines under one record of  "Test" fields. Solution item 1, Solution item 2, Solution item 3 in separate lines under one record of "Solution" field. SUBSTITUTE( ARRAYJOIN({Your Lookup Field}), ",","\n" )However, I would like to present the information in pair but separate line in NEW FIELD?  e.g. 1st line - Test item 12nd line - Solution item 13rd line - Test item  24th line - Solution item 25th line - Test item 36th line - Solution item 3Would anyone propose how to do it? Thank you. 

191
Dan_Montoya
T
trischapmanNew Participant
posted in Formulas
switch formula error

I have a project stage field that I need to convert into generalized phases. I have the below SWITCH formula and I cannot get it to save. All of the articles I have read have examples formatted this way - What is the issue? SWITCH(  {Project Stage},   ’Discovery’, ’In Progress’,   ’Planning’, ’In Progress’,   ’Account Build/Import’, ’In Progress’,   ’Execution/Migration Prepped’, ’In Progress’,   ’Imported’, ’In Progress’,   ’Import Prepped’, ’In Progress’,   ’Complete - Migrated’, ’Complete’,   ’Migrated/Monitoring’, ’Complete’,   ’Project Closed’, ’Closed’,   ’Pending')

52
T
L
LenaGNew Participant
posted in Formulas
Birthday Formula & Automation

Hello! I have checked out the the community for an solution to my problem and can't find what I am looking for to help me with my formula and automation. I am trying to set-up an automatic alert announcing someone's birthday on the day of their birthday. It did work but it sent the announcement the day before as well as on the day of the persons birthday.This is my formula: DATETIME_FORMAT({🎂 Birthday}, "MMDD") = DATETIME_FORMAT(TODAY(), "MMDD") My automation is set up for when a record updates, it sends an email on the day of the persons birthday. However, it sent an email the day before and the day of.I only need it on the day of the actual birthday. Can someone tell me where did I go wrong?Thanks in advance.

112
L
A
AndreaRInspiring
asked in Formulas
If this or this then this otherwise that unless blank then stay blank

Hi there, I am trying to create an If/or formula and got it to work but I can't get the formula to be blank if there's no data in a field. I'm sure that makes no sense. Here's an example: Let's say I have a small list of produce in a column. I want to create a formula in a new column to give me the category of produce either as a fruit or as a vegetable. I got the formula to work but if no produce has been selected I'm still seeing "Vegetable" in the formula column and I want it to return a blank value. Any ideas? Here's the example formula. Thanks in advance!IF(OR({Produce}="Grape",{Produce}="Apple"),"Fruit","Vegetable") 

173
A
Kelly_Vorrasi1
Kelly_Vorrasi1Known Participant
asked in Formulas
Line breaks and commas within quotes

I have a large multi-select that I then need to turn into a list format. I did the usual substitute the comma for a line break but unfortunately the items in this multi-select (that I can not edit) also use commas so now it's adding commas where I don't want them. Is there a way to get the formula to avoid those commas but not the others?  SUBSTITUTE({Plot Themes},", ","\n")

82
Kelly_Vorrasi1
P
Peter4572New Participant
asked in Formulas
How to create a "click here" hyperlink from a field name

Please see screenshot below. I have domain names in one field and want a clickable "click here" text in another field that hyperlinks to the domain name, how can I do this? Thank you 

4850
A
F
flavichNew Participant
asked in Formulas
Counting task completion

Hi all!I've been scratching my head and couldn't solve the problem quickly, so I'm asking for your help.I need to display a progress bar with the percentage of completion of a specific task, under which there are subtasks. In order to understand the percentage of task completion, we take the subtask as the source. If out of 10 subtasks only one is completed, then we get 10% of the main task completed, a certain run rate.It looks like a similar task can be implemented through a formula, but I was unable to write a correct formula using if, counting and division.How can this case be solved?

71
TheTimeSavingCo
H
HeleneleneKnown Participant
posted in Formulas
Create a project Management table where start date changes based on end date of depended record

I want to create an Project Management Table with the following requirementsI have several project stageseach stage has a start date and an end date (automatically calculated based on duration for each project.)the start date of one stage should be dependent on the end date of the previous task. So if "task A" ends on July 5th, the start date of task B should be the 6th. The end date of task B should be start date of task B + duration,... and so onso whenever something changes, the following dates should change as well. (also reflecting the dedicated duration set for each task)I tried with formulas or dependencies but could not figure out a way to automate this, instead of updating all records manually if one date changes.ThanksHelenelene 

162
C
H
HearthNew Participant
posted in Formulas
The private share link you tried to reach is not available.

I'm trying to follow this guide, but everytime I reach the "Concatenate step" the link that shows up to me says "The private share link you tried to reach is not available.".That's a copy of the table I'm using to, why it doesn't works and how to fix it?https://airtable.com/appFaino3o1b2cqEG/shrHoTe908izs7JPn

361
TheTimeSavingCo

Badge winners

Show all badges
Terms & Conditions