Skip to main content

Formulas

Leverage this space to unlock the power of Airtable formulas.

  • 5,943 Topics
  • 18,244 Replies
5943 Topics
A
Anna_Doehner-DoNew Participant
posted in Formulas
How to make calculations in two tables using currency fields

I’m brand new to Airtable and getting to grips with it all so forgive me if this is a simple and stupid question. I’ve got a table logging expenses by two people. I’m using a currency field to add the amounts spent by each person. Now I want a second table that shows the overall funds both people start out with and then a field that shows the current funds available which updates according to the expenses logged in the expenses table. Can someone explain to me how to do that? I don’t even know what sort of keywords to look for in the help sections. Thank you so much for any help! Anna

232
T
C
ChaumeilDigitalNew Participant
asked in Formulas
Sum the two smallest values between three values

Hello,I need to sum the two smallest values between three values.Example:- Value 1: 10- Value 2: 20- Value 3: 30The two smallest values are "Value 1" and "Value 2".How to do this via a formula (or other

72
C
R
RhettNew Participant
posted in Formulas
Change "invalid date" to another value

Hello all,I currently am trying to create a formula in which I calculate days since last contact, with some of the source cells either being blank or having "N/A" as a value. I plan to make cells either all blank or "N/A" if the person hasnt been contacted. what I am trying to do is have a tonow(date of last contact) formula in which if the cell is blank or N/A it states "N/A" instead of "invalid date".Thanks!

22
R
M
ms_eaNew Participant
posted in Formulas
Formula to put a distinction on fields with the same data

Hi there!I'm wondering if there's a way I can add a distinction for fields that have the same data. For example, there's two entries that are on the same date (09-22-2023). I have been trying to find out a way to somehow, say, put 09-22-2023a and then 09-22-2023b so I know which one is entered first and so on.I'd appreciate any insights. Thanks in advance!

34
A
D
DreamersNew Participant
posted in Formulas
Filling in Name + Surname from email Address

Hi everyone! 🙂In one table, i got Name + Surname + Email AddressIn another table, i only got Email Address, but i need to fill in the Name + Surname that i have in the other table.How could i do this?Thanks a lotMarco

82
A
airballer86
airballer86Known Participant
asked in Formulas
compare date fields only if first field is not blank

I cannot decide if I'm better off with an automation or else I'm just not meant to do if else formulas. I need to first check if a date field is not blank. If it is not blank, then I want to compare it with another date field. If it's later than other date field I want to show 'launch date moved out'. I know this formula is not comparing but it's what I ended up with (and it's not working)IF({Original Launch Date} & "-"THEN {Launch Date}>{Original Launch Date},'Launch Delayed') 

143
airballer86
D
djibouti33New Participant
posted in Formulas
Manually edit a formula field

Using the "created_at" field, I need a new date field (due_date) that takes created_at and adds x number of days. It's easy to do this with a formula field, and I have that set up already.However, I also need the flexibility of being able to manually overwrite the due_date if necessary. Forumla fields can not be edited. How can I benefit from the efficiency of auto-calculating a due date + the flexibility of manually adjusting if necessary?Thanks!

762
S
A
ArnHubNew Participant
posted in Formulas
If statement that checks if array < 2

Hi, I'm trying to make a formula that combines different records to a readable title.I'd like to add the Product field to the formula, but only if it has not more than one value and only if the value is not 'Algemeen'. The last part I can fix, but I need some help with the check for the amount of values. The current formula combines 5 fields, with only a check for the Product field. Any help for the extra check is appreciated. {Team} & "-" & {ID} & " | " & IF({Product} != 'Algemeen', {Product} & " | ", "") & {Section} & " | " & {Name}  

379
S
A
andrewb9New Participant
asked in Formulas
Lookup... Maybe?

Hello below is a table of scores and the corresponding status. I also have a risk register for each risk I want to be able to select the probability (1-5) and the Imapct (1-5) and it automatically returns the status as defined by the table below. Is it possible? Many thanks if you can help! Andrew  

32
A
Z
ZoeGoughNew Participant
asked in Formulas
IF STATEMENT not working

Hi,I am trying to create a simple IF Statement to produce the result PASS or FAIL if the value in another column is >= 80%:  In Column 1 ('% germination rate on second check') are % figures with values ranging from 0% to 100%. The purpose of the Column 2 ('PASS/FAIL'), is to produce the word 'PASS' if the value of Column 1 is >= 80%, or 'FAIL' if otherwise. Currently all cells in Column 2 are showing 'FAIL', irrespective of the % figure showing in Column 1. Is this some kind of syntax or formatting error on my end? Here is my formula: IF({% germination rate on second check} >= 80, "PASS", "FAIL")Thanks for the help! 🙂

185
Z
S
SophieULBNew Participant
posted in Formulas
Formula into a single select

Hi,To explain a bit : We have workshops that happen on different dates and people can register to those in a form.So I created a formula with the name of the workshop + the date of the workshop. I want that, in the form, people can only chose on date at the time. Now they can register for multiple dates and it creates complicate data for confirmation emails and reminders.So I was thinking that if the formula (Name + date) was a single select, they could only register at one but if there is another way, I take any advices 🙂Can anyone help me? 

01
S
J
JaxtrawNew Participant
asked in Formulas
Empty Space in search formula

I am using a mid formula to extract a string of text. The string appears as expected in the cell, but when Make (Integromat) runs the search it is adding an extra space before the string. " <string>" If the empty space is present, wouldn't I see it in the cell?I have checked over an over again to verify there is no extra space in the formula in Make. I have also changed my formula to +2, +2.Variations of this MID formula works with other use cases, this is the only one inserting an extra space. Is there a better way to do this? MID( {Summary}, FIND( ':', {Summary} )+1, FIND( ',', {Summary}, FIND( ':', {Summary} ) )-(FIND( ':', {Summary} )+1) )  

312
J
K
KCoatesNew Participant
asked in Formulas
Leveraging Formula Fields - Converting a Single select value to a numeric value for use in a cal

Requesting some guidance here. I'm new to Airtable and have read some documentation but have not fund any examples similar to my use case. I have a single select data field that I need to convert each selection items to a numeric value that can then be leveraged in a calculated field that will score an item in the table.How can this be done in Airtable?

543
M
D
drrashonNew Participant
posted in Formulas
Formula Problem

Trouble with date formula: September 14, 2023 + Coaching=Coaching 2023-09-16T04:00:00.000Z. CONCATENATE({Mtg Type} & ' ' & {date test})The date always comes out wrong in the Concatenation.

01
A
A
Alexblomme_New Participant
posted in Formulas
How to create an invitation with a limited number of guests?

Hello, I would like to create an invitation form for a party with 150 people maximum. The first 150 registrants receive an automatic email to confirm their participation and the others an email explaining to them that they are on the waiting list.Each guest can come accompanied by a +1, how do I know when the 150 guests are on the list to create the email automation?Thank you so muchAlex

140
A
R
Ringthebells86New Participant
posted in Formulas
Number formula not formatting

I have 5 fields (WBS1, WBS2, WBS3, WBS4, WBS5) which are all different levels of WBS and I have a formula that combines it all into 1 number: WBS1& IF(WBS2="","","."&WBS2 & IF(WBS3="","", "."&WBS3 &IF(WBS4="","","."&WBS4 &IF(WBS5="","","."&WBS5)))) The output of this formula is a number, like for example: 3.1.2 but it says the result type is not a number so I can't sort by it like I want to. I tried changing the "" to BLANK() like I saw on other posts but that isn't working. 

51
A
T
TSE-619New Participant
posted in Formulas
Sum my monthly duration

Hello, all.I'm new to Airtable and I need to create a form that tracks how many hours my coaching team is meeting with individuals each month. Each entry would be a new meeting, and I meet with individuals only once each month. Ideally, it would all exist on a single table. Each row would need to have the following information: Individual's name, date, duration, coach, Notes. Ideally, I would have another column that sums the month's hours as I go, call it "Hours this month".I assume there should be a formula that can do that for me, but I haven't for the life of me been able to utilize my Excel knowledge to create a working formula for AT.Any recommendations or alternatives to how I can approach this?

291
G
D
Data_thingsNew Participant
asked in Formulas
How to force nested if to format as date

Hi folks,I'm have a dataset of events in Airtable with each event as a row, and each row has a different number of sessions that can make up the complete event. This means I have 8 end date & time columns to capture the finish time of each session. I need a column that will dynamically show the end date & time of the whole event, regardless of the number of sessions so that I can use this in various calendar/timeline views in Airtable.I've attempted to pull them all into an IF statement, like this:IF({Session 8 end time},DATETIME_FORMAT({Session 8 end time},'DD/MM/YY h:mm A'),IF({Session 7 end time},DATETIME_FORMAT({Session 7 end time},'DD/MM/YY h:mm A'),IF({Session 6 end time},DATETIME_FORMAT({Session 6 end time},'DD/MM/YY h:mm A'),IF({Session 5 end time},DATETIME_FORMAT({Session 5 end time},'DD/MM/YY h:mm A'),IF({

142
ScottWorld
J
janickbergeronNew Participant
asked in Formulas
Field resulting in NaN breaks other formula.

Hi, I am creating a CO2 emission calculator on airtable and i can't seem to get my formula right.The formula in question is:({Émission véhicule en Kg single} + {Émission véhicule en Kg total} + {Émission repas en Kg}) / 1000 The formula either calculate using one destination ({Émission véhicule en Kg single}) or from multiple destinations ({Émission véhicule en Kg total}) In every case either {Émission véhicule en Kg single} or {Émission véhicule en Kg total} will be empty which result in the formula being:(NaN + {Émission véhicule en Kg total} + {Émission repas en Kg}) / 1000or({Émission véhicule en Kg single} + NaN + {Émission repas en Kg}) / 1000 The formula for {Émission véhicule en Kg&n

61
pressGO_design
P
plyskeInspiring
posted in Formulas
IF Field A has X value and Field B has Y value give me abc

Hello everyone.  I am trying to create a formula with the following logic: I have two fields (A and B) and these fields can contain different kind of values. Let's say that field A contains value A and field B contains value B, then I want the formula field to return a specific price. The same goes for an example where field A contains value C and field B still contains value B (then it's a different price). How would a formula like that look like?

81
Alexey_Gusev
B
bartdubsParticipating Frequently
asked in Formulas
Combining different data fields into one column

Hi there,I want to have a column which displays two bits of data. Those are a cumulative $ amount and below that a cumulative % amount. I have these two lookup fields separately however need to combine them into one field column. Any help would be greatly appreciated,Bart

229
B
K
Kate_JacobsKnown Participant
asked in Formulas
Formula to pull a Headline on Content Calendar

I'm trying to improve the content calendar we are using. I have a Content Field, which is the full post that we're going to copy and paste into Facebook. I'd like to have a column that will pull just the first line of the post using a formula. Any ideas on how to do this? For example, here is a post that would be in the content field:🌟 Fun-filled Learning at Ditebogo Daycare in Video Informal Settlement! These awesome educators are all about hands-on learning experiences for their kiddos. Recently, they organized a super-fun day filled with science experiments, face painting, and, wait for it... ice cream! 🍦🎨🔬Speaking of science, do you remember playing with Oobleck as a child? It's that magical goo that can be both a liquid and a solid depending on how you touch it. Now, here's the best part - Ditebogo Daycare isn't all about keeping the kids clean and tidy all the time. Nope, they believe in letting kids be kids and learn through experience! &#128

132
K
M
minae78New Participant
posted in Formulas
IF Formula.... Not sure how to fix it.

I work where following up with my clients are essential. Right now I have 3 columns in the table that. I'm trying to link together using IF Formula and it's not working so looking for some help here. First column is "Last Contact Date" which is in "Date" format and next one is "Schedule follow up" which is in "multiple selection" format with 1 day, 3 days, 1 week, 2 weeks, 1 month, 2 months, 3 months 6 months, 1 year. Last column is "Next contact day" where I'm trying to use IF formula to calculate the new date for me and they give me the new date for me. So below is what I have and right now, it's keep saying N/A.Not sure where I went wrong or how to fix it. Please advise how I could fix this. Thank you so much in advance. IF({Schedule follow up}="1 Day",{Last contact date}+1,IF({Schedule follow up}="3 Days",{Lat contact date}+3,IF({Schedule follow up}="1 Week",{Lat contact date}+7,IF({Schedule follow up}="2 Weeks",{Lat contact date}+14,IF({Schedule follow up}="1 M

41
S
M
MovementPlusNew Participant
asked in Formulas
How to create a custom URL using a formula field

Hello community, I would like to add a record ID to the end of a URL using a formula field. It would look like this:https://www.movementplus.app/course-overview?recordId=Record-Id-Goes-HereCan anyone please help me achieve this?Thanks in advance!

622
M
P
Pjero_KusijanovKnown Participant
asked in Formulas
If value is not find in multiple select field

Hi 🙂I have a formula field "TASK" that checks if there is value in fields or not.  It looks like this;IF({Fuel L}=BLANK(),CONCATENATE("• Add fuel tank", '\n')) Now, I want to have another multiple-select "CLEAR" field that clears the TASK value if the corresponding value is included in multiple select fields. For example {Fuel L} is blank, but in the CLEAR field "Fuel L" is selected. This setting should not show   "• Add fuel tank" in TASK field. I tried with FIND(), but I should use something as not FIND()... I would appreciate help to accomplish this. Thank you,Pjero

312
P

Badge winners

Show all badges
Terms & Conditions