Skip to main content

Formulas

Leverage this space to unlock the power of Airtable formulas.

  • 5,938 Topics
  • 18,225 Replies
5938 Topics
S
S_Pacheco-WilliNew Participant
posted in Formulas
How can I create nested IF statement which returns dates based on program type?

Hi there! Looking for assistance on a project management table. Each project will have a Project Type, Review Date and Delivery Date. There are three potential Project Types (Lead/Partner/Advise), the Delivery Date is a manual entry, and the Review Date needs to change based on the Project Type. Problem: 1) I keep getting an invalid formula notification, 2) I’d really like the review date to only land on weekdays/workdays but the formula needs to account for weeks out, not days out. Additionally, I’ll be duplicating this formula for various types of Review Dates, and sometimes based on Project Type, I’ll need the answer returned to simply be “Blank”. Here is the nested IF statement I am attempting. IF({PROJECT APPROACH} = “LEAD”, DATEADD({DELIVERY DATE},-15,‘WEEK’), IF({PROJECT APPROACH} = “PARTNER”, DATEADD({DELIVERY DATE},-14,‘WEEK’), IF({PROJECT APPROACH} = “ADVISE”, DATEADD({DELIVERY DATE},-13,‘WEEK’),’ ))) Would really appreciate help on getting this sorted out. Thank you!!

71
kuovonne
A
AUTORRARI_MAINInspiring
asked in Formulas
How can I perform a calculation from the values that were returned in a lookup field

So I’m not sure how to ask it differently, but if I were to try, here it goes. NEW to Airtable (so TY for being patient & detailed) So I have a base with three tables in it, Customers, Vehicles & Sales. I’m trying to collect all of the sales for each customer. All of the tables are linked, and I have been able to bring each of the sales into one cell (example: $0.00, $170.00, $125.00). But now I need to total those numbers! Any help would be greatly appreciated! DG

91
kuovonne
S
Samuel_MarklewNew Participant
asked in Formulas
Checking if a date range includes a day of the week

Hi all, Trying to make an interface with a “maximum possible spend” for each project launch day. Projects launch twice a week and run from 2 days to 15 days. I need to find a way to ask the question “Is this project still running next Tuesday” and “is the project still running next Friday”. Does anyone know where to start? I’ve tried a few things, I’ve tried lots of nested if statements, but I’m truly stumped. EDIT: A better way of formatting the question is “Will this product still be available for purchase next Tuesday/Friday” as two separate columns with a true/false value.

71
S
S
Shonet_HockleyParticipating Frequently
asked in Formulas
What is wrong with this formula?

Please could you help me to understand how to make this work? Thank you! IF(NOW(IS_AFTER({Announcement Start Date}&IS_BEFORE({Announcement End Date}, ‘ACTIVE’, ‘INACTIVE’))))

138
B
S
Shonet_HockleyParticipating Frequently
asked in Formulas
Suggested Formula solution

Hi there, I want to create a field that gives a different message to the user, depending on whether they have filled in a particular field ‘on time’ or not. I’ve attempted to explain it in text. If anyone could suggest the functions I need to use, I would really appreciate it. The problem for me is a. how to say ‘IF time is less than 5 minutes towards the time in the field OR IF time is more than 3 hours after time in field’ and to link that to b. ‘… AND field x has not been filled in THEN ‘show message a’’ and c. linking several options together (x3 optional messages). IF time is less than 5 minutes before end of lesson AND there is nothing in lesson reports field THEN “Please enter your lesson report, concrete targets and homework now” OR (IF time more than 3 hours after the lesson end time AND there is nothing in the lesson report field THEN “Your lesson report is late. Please enter your lesson report, concrete targets and homework now”) OR ((IF the report field is filled in BEFO

102
S
K
ktsParticipating Frequently
posted in Formulas
Tricky nested IF using multiple fields

hey community, I’m working on this formula to return a simple “Y” or “N” based on whether or not a date is past. The items in the grid could either be on a single date, or could have both a start date and an end date. I’ve tried a couple different ways of writing this formula but getting a lot of errors. What I’d like to do is: “if the end date field is empty, determine whether or not the event is in the past based on the start date field; otherwise [if the end date field has a date], determine whether or not the event is in the past based on the end date field.” Here’s what I’ve got: IF({End Date} = "", IF(IS_AFTER(TODAY(), {Start Date}), "Y", "N")), IF(IS_AFTER(TODAY(), {End Date}, "Y", "N"))) Could somebody help me figure out what I’m doing wrong? Thank you!

21
Ben_Young1
A
April_KastnerKnown Participant
posted in Formulas
Produce string of results if multiple nested if statements are true

Hi All! I have a nested IF statement that is set up to find if the value from Column A, B, C, D, or E matches Column F. If one matches, it produces the name of the column. What I’m looking for is a way to produce more than one name if more than one column matches. Currently it looks like this: IF( OR( ColumnA, ColumnB, ColumnC, ColumnD, ColumnE ), IF( ColumnA=ColumnF,“ColumnA”, IF( ColumnB=ColumnF,“ColumnB”, IF( ColumnC=ColumnF,“ColumnC”, IF( ColumnD=ColumnF,“ColumnD”, IF( ColumnE=ColumnF,“ColumnE”, ) ) ) ) ) ) This only gives me one result. If more than one column matches, I’d like it to somehow give me both or all results Thanks!

31
Ben_Young1
P
Paulo_GallettiNew Participant
posted in Formulas
Fx field showing ""

I have a Fx field and for some reason, it’s showing " at the beginning and end of the words. Is there a way to remove it? Formula: {Contact} & “—” & {Type} How it’s showing: “John Smith, Acme Inc”—Email How I’d like it to show: John Smith, Acme Inc — Email

21
kuovonne
Z
Zhenya_RozinskiNew Participant
posted in Formulas
Linking Value through Formula

I am building a simple recruiting database in AirTable. Here are the tables that I have Candidates - (names, all information, name of recruiter linked to Recruiter table) Recruiters - Name, Email Position - Position, company, salary, etc. Submission - This is where a candidate is linked to a position. Link to Candidate Name, Link to Position. I also have the Recruiter Name here though Lookup. How can I display recruiter’s email so it can be included in email automation?

74
Z
C
Ciaran_DuffyNew Participant
asked in Formulas
Need help writing a complex IF(FIND) formula for a Mood Tracker

Hi all, I’m using Airtable to create a mood tracker. I have a form question which asks which of the following 20 emotions I’m feeling, multiple answers = ok. :green_square: Balanced :green_square: Calm :green_square: Cozy :green_square: Loving :green_square: Fulfilled :yellow_square: Energised :yellow_square: Focused :yellow_square: Happy :yellow_square: Inspired :yellow_square: Motivated :blue_square: Bored :blue_square: Drained :blue_square: Lonely :blue_square: Sad :blue_square: Tired :red_square: Angry :red_square: Anxious :red_square: Hungover :red_square: Restless :red_square: Stressed :red_square: Tense What’d I’d like to achieve then is to have a pie chart look at these emotions and display an ‘average’ with each of the 4 colours. My plan was to use IF(FIND) to create a kind of mood average column in the database to direct the pie chart to, but I’m having a bit of difficulty writing the script. I was going down the track of: IF (

136
ScottWorld
D
Diane_MartinezParticipating Frequently
asked in Formulas
Percentage Difference between two numbers

I’m trying to figure out the difference (in percentage) between two numbers. I’d like to set up a formula that gives the percentage (positive or negative) difference between two numbers. So in the screenshot above Row 1: Percentage decrease from 100 to 50 Row 2: Percentage increase from 25 to 50 Row 3: No change between 50 to 50 My formula so far: -(({Starting #} - {End #})/{Starting #})

852
D
T
Terilyn_Floyd-CNew Participant
asked in Formulas
Is it possible to write an AirTable version of this power query?

The piece I am really struggling with is the comma delimited split. This is the order as done in power query. Select entire tab/Table Source = Excel.CurrentWorkbook(){[Name=“Table1”]}[Content], #“Changed Type” = Table.TransformColumnTypes(Source,{{Many Columns/Fields}}), Select only the Columns/Fields #“Removed Other Columns” = Table.SelectColumns(#“Changed Type”,{“Key”, “ManyLinked”}), Remove empty cells/rows #“Filtered Rows” = Table.SelectRows(#“Removed Other Columns”, each ([ManyLinked] <> null)), Split the ManyLinked into one row for each comma delimitate value - each row gets the value from Key and a single value from ManyLinked #“Split Column by Delimiter” = Table.ExpandListColumn(Table.TransformColumns(Table.TransformColumnTypes(#“Filtered Rows”, {{“ManyLinked”, type text}}, “en-US”), {{“ManyLinked”, Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), “ManyLinked”), Thanks, Teri

51
T
T
Tere_HinojosaNew Participant
posted in Formulas
Help: How to use DATEADD with business hours and working days

Hi everyone! I’m adding a date field {Fecha de inicio} and a duration field {Tiempo de impresión} to get an end date with hour. But I need to know if the result can skip/exclude non working hours and days so the results are not for example, Sunday at 4 am but Monday at 8am This is what I have now: DATEADD({Fecha de inicio},{Tiempo de impresión}, ‘seconds’) I’d appreciate if someone can help! :slightly_smiling_face:

531
W
M
Mike_BassNew Participant
posted in Formulas
Sum from previous columns

Hi! I’m tracking loan payments (for a 0% loan support-community). One column should show the monthly payment made. The other column should aggregate total payments made thus-far. Suggestions? In the screenshot I manually entered what I’m aspiring to have auto-calculated. Thank you!!

21
M
J
Jose_Alberto_FuKnown Participant
asked in Formulas
Help with an if then concatenated formula

For a casting agency db I need to set a personalized filter column I have a field with the age, now I need a formula column that gives me the segment :point_down:t2: AGE CALCULATED COLUMN < 15 kids => 15 & < 18. teenagers => 18 & < 31 young => 31 & < 45 mature => 45 older I’ve been trying to make an if then concatenated formula but I’m always getting errors. Any help? :pray:

124
J
J
Johanna_NikoletNew Participant
posted in Formulas
Using formula to flag if date is older than 2 weeks, 4 weeks, 6 weeks

I’m trying to add a column to a view that calculates - if X date (date a specific record was updated) is older than 2 weeks, show text “2 week” in the corresponding cell. And so on… for all the weeks - 2, 4, 6, and 8. Is this something that can be done? I’ve tried playing around with IF statements, but I’m not sure how to have it detect today’s date minus 14 for 2 weeks, today’s date minus 28 for 4 weeks, etc.

1026
Alexey_Gusev
N
Nadege_BOINNARDKnown Participant
posted in Formulas
Calculate quarterDeadline from paymentDate

I have a little brain exercise and I’m running in a hamster wheel and cannot see to find the logic to use or if there’s a function out there that would solve my problem. I’ll take any help I can get :))) How can I create a formula doing the following behavior ? Variable : paymentDate - formatted in YYYY-MM-DD (ISO) I want to make each line appear if it fits the criteria. A paymentDate can belong to Quarter 1, 2, 3 or 4. Q1 is from Jan 1st to March 30th Q2 is from Avril 1st to June 30th etc… We have one month to prepare our reports after a quarter ends. What I want is to hide all the records from a current quarter until the deadline, i.e. all the payments made during Q1 2022 for instance will not be visible by my partners before April 30th (March 30th + 1 month). What I need is to be able to calculate a date (quarterDeadline, for instance) that will be April 30th, July 31st, October 31st, and then January 31st (of the next year). But this date of course needs to carry the year… because

171
Kamille_Parks11
I
IT_DepartmenrtNew Participant
posted in Formulas
Linked Filed Issue

Hi, just wondering if someone can help me, I have an issue with a linked field. The problem I am working on a skills matrix and adding skill for a user is fine, however I need to create an additional field to show all the unselected items, ie. what goals after left to achieve. So what I need is the linked files that shows all the selected items and another that show the unselected items. Hope some one can help and thanks in advance.

63
Justin_Barrett
N
Nadege_BOINNARDKnown Participant
asked in Formulas
RIGHT(String,HowMany) > Error when calling a function field as the string

Hi ! I have a calculated field that returns for me a value that looks like 2021 | Q3 or 2022 | Q1, determining the year and the quarter from a payment date. Now, in a linked table where this field (“Quarter”) is a lookup, I’m calling the RIGHT() function as follows : RIGHT(Quarter,2) in order to have as a result Q3 or Q1 as in the examples above. However, I get an ERROR! I believe it has to do with the fact that my “String” is a lookup but even if I create a field in the table, a function, that is basically “Quarter” (i.e. take the value that’s in the lookup) and call RIGHT() on that new field, I get an ERROR! too. I am deducting that RIGHT() cannot be called on a cell that’s a function ? True ? Any workaround ? Thank you !

42
N
A
Anton1New Participant
posted in Formulas
Help with REGEX_EXTRACT

Hi everyone, Can someone help me with formula Regex_extract. In Airtable I have a field with text in quotes: " “msg”: “2. Have a second version of that same photo with no background”" . I can’t get rid of the quotes with formula REGEX_EXTRACT.

112
Justin_Barrett
G
Gustavo100New Participant
asked in Formulas
Screen a multiple select field for a certain value to fill another column with data

Dear all, i am looking for a solution for the following problem: “Column A” is a multiple select field where I have data on company presence in a variety of countries (e.g. “Poland”, “China”, “Germany”) “Column B” should have a formula that checks whether “Column A” contains an entry (among others) that belongs to a specific list (e.g. “Poland” or "Germany). In this case the formula should return “Europe” I am not sure, whether this problem should be solved with IF, nested IF, SWITCH formulas or whether I need a completely different approach Thanks for any input, Cheers

623
G
M
Matt_EngageNew Participant
asked in Formulas
Nested IF Statements Error

I’m trying to add a column into my financial forecast that shows whether Column A + Column B = Column C. If the value in Column C doesn’t match I’d like it to show me that it’s incorrect. The formula I have so far is below but I can’t get around the error. Can anyone tell me what I’m doing wrong? IF({Revenue Recognised in FY22}+{Year Recognised To Date}={Contract Value},“MATCH”,IF({Revenue Recognised in FY22}+{Year Recognised To Date}>{Contract Value},“INCORRECT”,IF({Revenue Recognised in FY22}+{Year Recognised To Date}<{Contract Value},"INCORRECT”))) Any advice is appreciated! Thanks

82
M
G
gwitudNew Participant
asked in Formulas
How to calculate if two dates fall within a specific day

I’m really struggling on working out how to create a formula for the above. For context, I have an AirBNB type operation whereby there is a check in + check out date for guests who stay which can sometimes be as long as a few weeks. I’m trying to figure out the best way to work out if a specific day falls between 2 dates so I can group data based on events & also calculate occupancy in a given month or event based on a property (e.g. if there’s a major event in a city on the 10th of February & there’s a checkin on 8th February & checkout on the 11th February, how to have that filter in as an “event booking”). Alternatively, with the same logic, if I want to separate “weekend bookings” from “midweek booking”, I’m happy to have a separate table where I manually input every weekend/event. Would greatly appreciate any ideas anyone might have on how to solve it

121
A
J
JayArmstrongNew Participant
asked in Formulas
Perform a basic calculation on integers inside a string

I would like to achieve the following: Multiply all the quantities inside Part List Output (2x, 8x, etc) by the Quantity value. So in the first row in the example provided I want to multiply all of the integers in the comma-separated strings by 6. I’ve mocked up a demo output in Calculated Part List. One complication to note - the strings can also contain integers that I do not want to target for calculations, eg. 1x Shelf 812mm. I can easily manipulate the Part List Output if necessary. For example, wrap in special characters: _2_ Large T Side with Holes, _2_ Large A Side, _1_ Large Base, _8_ Spring Clips Perhaps @ScottWorld has managed this?

82
J
U
ucmavnerdKnown Participant
posted in Formulas
Convert 24hr time text field to AM/PM

I am getting an export that expresses a start and stop time as text in 24 hour format. I am trying to make this into AM/PM. Thoughts? Input: 730 1130 1330 1700 Desired Output: 7:30 AM 11:30 AM 1:30 PM 5:00 PM

1017
U

Badge winners

Show all badges
Terms & Conditions