Skip to main content

Formulas

Leverage this space to unlock the power of Airtable formulas.

  • 5,939 Topics
  • 18,228 Replies
5939 Topics
B
buseNew Participant
posted in Formulas
About formula column [HELP]

Hi, I am new to AirTable, there are still some points I can’t figure out. Now I will explain my question in detail. I’m sure it’s very easy for you guys, but I certainly didn’t succeed. I can never find the solution. I have two questions. The first is this: The table you see in this picture is the class list. Write the names of the students. I linked this table with the “Dersler” table. And this is the “Dersler” table. Classes and dates. I add the students who entered the course next to the course. And I created a formula row in the “Class List” table. Here I wrote this formula. The form is working. But I want the “/2” part of this formula to change after the third lesson is made in the “Dersler” table and after I start to enter students there. Since the number of lessons changes after each lesson, the success score needs to change. For the success score, I need to calculate from “the number of students attended the course/all courses”. Can I have the formula change as I complete th

31
Databaser
C
Chafen_SuttleParticipating Frequently
posted in Formulas
If this formula help

I need help with a conditional statement working with dates. So in the table there is a time slot however the conditions lie on the right where the start and end times actually are. So if a slot is taken at 10:30 for instance how would I formulate a statement saying if appointment time is between then “booked”

63
Dimitris_Goudis
B
Barry11New Participant
posted in Formulas
Is it possible to write a formula for: IF within 1 week of this date, highlight this date?

Hi all! I think this would be super helpful to have programed as a default. "within (number of days) of date chosen, date will highlight as (choose color) Short of that, is there a way to script this? Thank you, B

141
Dimitris_Goudis
M
Manuel_SoberaniNew Participant
posted in Formulas
Chossing status

Hello all I have these two fields the is the “amount used” and “Status” what I want to do is when the “amount used” is empty I want “pending” when the “amount used” is “0” I want “Void” and when the “amount used” is greater than “0” I want “Used” On status is a single select! Screen Shot 2021-10-22 at 9.13.54 PM|173x500

62
M
K
Katy_RichardsParticipating Frequently
asked in Formulas
IF FIND- help with formula

Hello. I have a lookup field which lists the age groups that are related to all the linked records. I have a formula field which I’d like to show ‘Senior’ if the lookup field contains Senior, ‘Intermediate’ if it contains Intermediate (but not Senior) and ‘Junior’ if it only contains Junior. The formula I’m using is this: IF( FIND( ‘Senior’, {Lookup Correct Section} ), ‘Senior’, IF( FIND( ‘Intermediate’, {Lookup Correct Section} ), ‘Intermediate’, IF( FIND( ‘Junior’, {Lookup Correct Section} ), ‘Junior’ ) ) ) If it is listed “Intermediate, Senior” then the formula works perfectly however if it is “Senior, Intermediate” then it returns Intermediate (even though Senior is in there). I’m sure it’s something really simple that

119
S
S
Stephen_CurtisInspiring
asked in Formulas
Help using OR in an IF statement

Hi, I’m trying to build an IF statement with an OR in it but I’m not getting the expected result. I have created a simple table for tracking hours worked and calculates overtime. Columns are as follows… Date - is just the date in question. Calculate Weekday - WEEKDAY(Date) So far so good. Because the standard work hours vary from day to day (7.5 for Mon, Tue, Wed, Thu / 7.25 for Fri / 0 for Sat, Sun), I wanted to create an IF query to return the correct hours for that day number. Rather than going overboard with nested IF, I tried to simplify to the following, but the OR in this doesn’t seem to be doing what I expected… IF(WEEKDAY(Date)=5,7.25,IF(WEEKDAY(Date)=OR(1,2,3,4),7.50,0)) From what I can see, the day 5, 0 & 6 work okay. But my problem comes with the OR as only day 1 works; the rest come back as 0 hours. Am I misusing this function? I was expecting the OR to then return 7.50 fo all of 1,2,3, and 4.

162
S
A
AICInspiring
asked in Formulas
Display a message if today's date is in a linked field

Hi all, Please see the screenshot below - I’d like to display a message in the Is Absent Today column if today’s date is among the dates in Absences. Currently I’m using the following formula but it only works if there’s a single date: IF(DATETIME_DIFF({Absences}, TODAY(), 'days') < 1, "Yes", BLANK()) Any help is much appreciated!

148
A
Burner
BurnerKnown Participant
posted in Formulas
Convert number with decimal to string

Hi, I’m trying to convert a number to a string. I have a field called ‘Version number’, which is formatted to take two decimals. I want to use this version number in another field as a string because I’m concatenating to generate a document filename. So for e.g. if the version number field is 1.00, I’m trying to write a formula that generates a string “myfile_v1.00”. The problem I’m having is that when I use the approach suggested in the documentation, 1.00 is being converted to “1” and so I’m getting “myfile_v1”. I want to retain the decimal value in the type conversion. I suspect had the decimal part been something other than .00 it would have also been converted to string, but I want this to be the case even if the decimal part is .00 I could use some help with this. The formula I’m using is: {Document name}&" v"&{Version number}&"" Document name = mydocument Version number = 1.00 Formula result = “mydocument v1” Desired formula result = “mydocument v1.00” Thanks.

281
kuovonne
A
Amber11New Participant
posted in Formulas
Concatenate With Conditional IF Formula

I’m working on creating a Table that lists Expenses. I’m currently using the following formula to populate a Title for the first column in the table based on columns: Vendor, Frequency, Price, Per Member Price?, Users and Total Due. The “Per Member Price?” is a checkbox field and the Total Due column is calculated by multiplying Price x Users. CONCATENATE({Vendor}, " ($", {Total Due}, Frequency, IF({Per Member Price?}, " per member", ""), ")") This results in data displaying like this: App 1 ($52/mo.) if the “Per Member Price?” is not checked OR App 1 ($52/mo. per member) if the “Per Member Price?” is checked. The problem is, is that regardless of if it’s supposed to be per user or not, the Total Due is always present vs conditionally showing the per member price as defined in the Price column. I THINK I might need a nested condition here within the CONCATENATE… something like: CONCATENATE({Vendor}, IF( {Per Member Price?} = 0, "$", {Total Due}, Frequency, IF({Per Member Price?}, "$",

826
kuovonne
J
Jack_InscoeNew Participant
posted in Formulas
Conditional Invoicing Question

Hello - I’m quite new to Airtable and trying to prototype out an invoicing application, but I’m running into an snag and could use some guidance. My base: Clients Table (name, id, project lead, etc) Items table - this is a granular list of the items for an entire project - name, descirption, costs, % complete, status, last updated datetime, and a link to the clients table Invoices - In this table my goal is to be able to create a monthly invoice for a client based on the items table with the following conditions. Where the CLIENTassociated with the ITEM = CLIENT associated with the ITEM AND ITEM’S Last Updated date is BETWEEN {invoice start date} & {invoice end date} In SQL I would join on the client ID for both tables, but it’s not yet clear to me whether this kind of join can be done in Airtable. I’ve looked at other invoicing examples and it looks like many have the ITEMS associated at the invoice, but in my use case that may be too laborious with the number of line items a

73
J
D
Darren_SmithNew Participant
posted in Formulas
Date and Time Formula Help Needed

I have a field that includes a date and time. I want to create a new field that give a new date and time that is 24 hours later Please can anyone help?

41
Dimitris_Goudis
Y
YourFriendKnown Participant
posted in Formulas
View Condition in Formula

I have a view with the condition group: When Primary Introducer Contains John Stamos or Secondary Introducer Contains John Stamos or Tertiary Introducer Contains John Stamos Then I have an IF statement determining whether John Stamos is the Primary, Secondary or Tertiary introducer on an order and doing the calculation depending on which tier of introducer John Stamos is. If Statement: IF({Fees for Primary Consultant}="John Stamos", {Primary Introducer Cash Fee ⚙}, IF({Fees For Secondary Consultant}="John Stamost", {Secondary Introducer Cash Fee ⚙}, IF({Fees for Tertiary Consultants}="John Stamos",{Tertiary Introducer Cash Fee ⚙},0))) Is there a way to make where the if statement says “John Stamos” instead refer to the view condition so I don’t need to manually edit the if statement each time I change the consultant? Thank you Best Wishes Your Friend

72
Y
K
kennypurnomoKnown Participant
posted in Formulas
Many-to-many table relationship to set unique pricing per customer

I have tables named “Inventory” and “Customer”. What is the best way to create unique price list per inventory record for each customer? I want to have => inventory A is $10 for customer A and $5 for customer B etc

105
K
T
TiffanyZavalaNew Participant
asked in Formulas
IF, contains formula help!

Hello! I am trying to troubleshoot a formula field. Currently I have this in the formula field: CONCATENATE({Part}," | “,{Objective},” ",{#}) to create the following: Looking at the first record, the result reads: Part 1: Competent Leader | Heart 1 However, I would like create a search that allows the final formula to read as such: Part 1 | Heart 1 I want it to be able to search the {Part} field, but not list the entire title - if that makes sense. Seems simple enough, but I had problems using the Find or Search field.

552
T
B
buseNew Participant
posted in Formulas
Automatically refresh formula [PLEASE HELP, ITS VERY IMPORTANT TO ME]

Hello, I’m new to Airtable. That’s why I’m asking for help with something I can’t solve. I have two tables. One is Class Attendance and one is Lesson List. The Class Attendance table includes student names, student ID rows and course dates columns. If I write the value of 1 in the course date column, the student is enrolled in that course. If I leave it blank, it is not entered. And I also created a formula column and wrote this formula. ( COUNT({2021-10-05},{2021-10-06},{2021-10-13},{2021-10-23}) / COUNTALL({2021-10-05},{2021-10-06},{2021-10-13},{2021-10-23}) ) *100 This formula is the number of courses taken by the student/total number of courses. The result is the student’s success score. In the Course List table, I wrote the course dates, assuming that the course dates were determined in advance. I opened a new column and put a checkbox. I will tick the checkbox after that lesson is complete. What I want to do is to automatically create a new column in the Class Attendance table af

52
R
J
Jyo_DNew Participant
asked in Formulas
Multiple conditional formula with IF, AND

Hi Everyone, I have been trying to accomplish an error check and I have syntax issue on the below formula. May I please know what is the issue with my formula? Thank you so much in advance. IF(AND({est_filing_date_range_start},{est_filing_date_range_end},{actual_or_estimated}!=BLANK())AND({review_type}=BLANK()),“ :stop_sign: ERROR…review_type CANNOT BE BLANK when est_filing_date_range_start, est_filing_date_range_end,actual_or_estimated fields are populated”, ",“ :white_check_mark: GOOD”)

644
J
A
automadienInspiring
posted in Formulas
Collaborator ID as formula

Hi Airtable community! I’m looking for a formula which fetches collaborator IDs The reason is, I want to assign through another formulas a set of teams. So, if user 1 is true in the ID column, return text value “team 1” If that makes s3ense!

21
Rupert_Hoffsch1
K
Kayla_Marquez_VNew Participant
posted in Formulas
I'm stumped when trying to aggregate data from three columns

Hi! I’m trying to aggregate multilingual survey answers using a formula. I will eventually need this format of formula for more complex data, but I am stumped at the beginning part of getting at least three statements to all say ‘YES’ if there is a ‘yes’ in one column or a ‘Si’ in another or a ‘容易 - Yes’ in another. This formula works :IF({Was it easy for you to order a meal through the current texting system? CHINESE}=‘容易 - Yes’, ‘YES’) But then I want to build upon it by adding the conditions IF or even OR it doesn’t work. For example, this does not work: IF({Was it easy for you to order a meal through the current texting system? CHINESE}=‘容易 - Yes’, ‘YES’) OR(IF( {Was it easy for you to order a meal through the current texting system?}=‘Yes’,‘YES’)) Does anyone have any ideas of how to make what I want to happen/guidance on what I’m doing incorrectly? Thanks so much in advance.

73
Kamille_Parks11
M
Matthieu_LABERIInspiring
posted in Formulas
Formula date if

Hi, I need help from your brain because mine is out for this problem ! Actually i have this formula : IF(IS_SAME({Date d’envoi}, TODAY(), ‘day’), ‘YES’) To this formule i want to add 2 things : if the date is after today add “AFTER” if the date is before today add “BEFORE” Finally, i need to have 3 conditions : YES if today AFTER if the date is after today BEFORE if the date is before today Thank’s for your help ! Matthieu

11
Kamille_Parks11
A
Ahmed_ElagamiKnown Participant
posted in Formulas
Calculated time based on Processing type

Hello beautiful people. I hope you guys are doing great, I am working on a formula that determines Time (no of dates to be added to ) A start point ( Material receive date) So We are working on localization the titles that we have:: Some of them need En Dubbing others need Ar subtitling only, other need nothing. So This image above, shows how I can indicate which titles need what to be processed. There are some rules estimation for each process, to be taken in consideration this calculation is on 45 Min for each asset So Depending on those Hours given for each episode (45 Min ) Ave I would like to get the Delivery Date for each title depending on the processing time for each title. to add up on the delivery date. Please put in mind: Media transcoding and Ingesting are A Must On: Is upon selection After: Compliance is a must I have this base shared it has all details Airtable Airtable | Everyone's app platform Airtable is a low-code platform for buildi

61
Dimitris_Goudis
O
Olivia_HungNew Participant
posted in Formulas
Calculate discount based on single field

Hello. I need some help with my Airtable. I would like to do some discount calculation. Column 1: Total price of purchased products Column 2: single select, select Yes if customer is VIP, then will give 5% off Column 3: optional further discount, also a single select Column 4: Discounted price after column 3 and 4 I would like to know if I could create a formula that after I selected “yes” on column 3, then Discounted price would automatically do the calculation for me. Thank you.

81
Kamille_Parks11
A
AnxiousInspiring
posted in Formulas
Multiply a field with a percentage

I am basically trying to calculate how much a PayPal fee is by multiplying the item price by their fee (3.49% + .49 cents per transaction) I tried using the Airtable guide but it kept saying it was invalid and whatnot. Buuut, of course, the ol’ Google formula does not work because this isn’t Google Sheets. :slightly_smiling_face:

41
A
R
Roman_VaismanNew Participant
asked in Formulas
Substitute Spaces with Hyphens on Multiple Select Field

I have a a Multiple Select Field which I am trying to substitute spaces with hyphens. The issue that I am running against is that the is space between items and the formula adds an extra hyphen in the final result. I am using LOWER(SUBSTITUTE(Series, " ", “-”)) See screenshot for field details and results. Thanks for the help!! substitute issue|700x237

382
R
D
Diletta_D_ArrigNew Participant
posted in Formulas
Start time & end time for event

Hi everyone :slightly_smiling_face: I’m very new here and I’m still trying to learn this immense platform. I just attached the image of what I am working on. I would love Airtable to pick up the duration of each topic and update the start and end For example “hosting introduction” is 3 mins, starts at 12:00pm and finish at 12:03pm The next topic is “industry updates” and it will last 7 mins starting from 12:03 til 12:10. Does anyone know a formula that will calculate the time? Thank you so much to everyone who will read this :grinning_face_with_smiling_eyes:

19315
Justin_Barrett
Y
yann_blondelNew Participant
posted in Formulas
Only display Last Version

Hello All! Not sure I should ask this question in Formulas. On a list of multiple versions of a same item I’d like to only display the last one. Here is what I have: Here what I wish to obtain: Thanks in advance!

21
Kamille_Parks11

Badge winners

Show all badges
Terms & Conditions