Skip to main content

Formulas

Leverage this space to unlock the power of Airtable formulas.

  • 5,938 Topics
  • 18,225 Replies
5938 Topics
L
Laura_ConnollyNew Participant
asked in Formulas
DATETIME_DIFF within IF/AND/OR formula

I am trying to create a field that will show a flag when one of two conditions are met around a Platform Due Date: Delivery Date is after the Platform Due Date Submitted Date is within 56 days (or 8 weeks) of Platform Due Date I also don’t want it to show anything if there’s no Platform Due Date (field is blank), as this field/column doesn’t apply for all records. I’m not getting any errors, but it’s also not showing the flag for a record that was submitted 3/29/22 but was due 3/7/22. In this case, the submitted date is after the due date, but there will be future cases where the submitted date is before the due date, but still less than 8 weeks or 56 days before. Here’s what I’ve got: IF(AND({Delivery Date} > {Platform Due Date}, OR(DATETIME_DIFF({Submitted Date}, {Platform Due Date}, ‘days’ < 56)), {Platform Due Date}, “Field Is Not Empty”), “ :x: PAST DEADLINE”, “”)

234
L
C
ChalinsaNew Participant
posted in Formulas
Creating Bill of Material for each Purchase Order

Dear Airtable Community, First of all, thank you all for your knowledge and post shares, all of the comments have been incredibly useful to newbies like me and it has helped with my work greatly! I have a series of questions please: Right now, I have the following: ‘Product’ Table: containing details of product eg. Price, cost (linked to Sub-part and Calendar table). Example: Product A, Product B, Product C ‘Sub-part’ Table: containing various parts of each product with its specification eg. Picture, size, vendor details, quantity of each sub-part used in the Product (linked to Product and Calendar Tables). Example: A-1, A-2, A-3, B-1, B-2, C-1, C-2, C-3, C-4, etc. ‘Calendar’ Table: containing purchase order details eg. Quantity of ‘Product’ ordered, color, start date, end date, etc. (linked to Product and Sub-part Tables). Example: Order2022-01, Order2022-02 Currently, I have the lookup field of ‘quantity of each sub-part used in the Product’ in the ‘Product’ table, basically to let

210
C
I
Ian_KuhnNew Participant
asked in Formulas
Formula To Identify Relationship Between Multiple Rows

Hi all, I am looking to identify if a team is onboarded. We define onboarded if at least one of the designated people on the team participated in a session. Take the example below (columns hidden to simplify the example). The first column (physician team name) is the name of the team. The team consists of three people including John Doe, William Smith, and Sarah Johnson. We can see that Dr. Doe onboarded and he is considered a “live” user. His two colleagues are not, but because he is live that means the entire team is live. The ultimate goal is to group by Live vs Not Live and then Team Name like the screenshot below. This doesn’t work though because Neuroscience Team shows in the live section because Dr. Doe is live, but then also shows in the not live section because the other two did not participate in training. How do I create a formula so the Neuroscience team only shows as live? We cannot filter out the other two individuals from the grouping. Does anyone have any clever ideas

173
I
B
Bryan_SanchinelNew Participant
posted in Formulas
How I create formula that pulls data from two cells and eliminates the space by creating a dash between them

Hello everyone, thanks in advance for any support. I would like to create a formula that pulls organization names and what state they are in and eliminate the space and have a dash between them. I.E. Akoutis CA would be Akoutis-CA. See attachment

143
B
E
Erica_HolleranNew Participant
posted in Formulas
What am I doing wrong?

Here is my formula IF({Dept} =Bath, “8.5”, IF({Dept} =Win, “10”, IF({Dept} =ODL, “0” ) ) ) My objective - if field “Dept” is Bath then 8.5, if it is Win then 10, if it is ODL then 0

33
kuovonne
B
bederNew Participant
asked in Formulas
Formula Help: IF statement to override calculated date field with a manual date

Hi Airtable community! Hoping for some assistance with an If statement. We have a workback schedule for deliverables based on an event date Workback:Materials Due: WORKDAY(Date,-45)) Occasionally, we will need to adjust the deliverable dates if the date of the event is determined later than the typical schedule allows for. I created a “Manual Deadline” date field. Manual: Materials Due: DATE I would like to create an If statement Formula field for Materials Due that will populate it with the Workback: Materials Due date UNLESS the Manual: Materials Due field has a date in it, in which case it will display the manual date. Can someone please assist? My brain has trouble with if statements :winking_face:

204
B
J
Jonas_TidstromNew Participant
posted in Formulas
Select first and last (or second last) elements in array

I want to select the first and last values from an array. The array is the results of a “rollup” of certain values in another table. Depending on the value of the last element, I might need to select the second last element. How can I conveniently select values from an array? In Python and other languages I would be something like x[0], x[-1] (or x[-2])… Do I need to do “the painful job of FIND LEFT” etc. Or can it be done in a nicer way? Thanks!

2577
C
M
Meat_Goat_SocieNew Participant
posted in Formulas
Create: A Prompt to ask for information when it does not exist in the base

Hello, I have created a form. The form is about a “person” and their parentage. On the form, it asks for a mother and father to be identified. Because it is a family tree type situation, it is for related people. I would like the selection from the base to already be in there or added with a form, like the one I have created. If a child’s name is put into the form, and the parents do not exist, I need a prompt to input the mother or father and their information will be entered into the database also. Kind of like, 'it doesn’t exist, so you’re going to create it at that time, to move forward on the form. Can someone help me with that formula? Thanks in advance.

30
M
S
Scott_CalleryKnown Participant
asked in Formulas
Find initial letters of UK Post Code

Hi, I am looking to find the initial letters of a UK post code before the first number. UK Post codes start with 1 or 2 letters and are always numbers and then letters. E.g. ‘WA1 1AB’ or ‘SK2 3LP’ or ‘L1 1AA’ or ‘M2 3NK’ I am looking for a formula to return the initial letters before the first number. So for the above examples, I would expect ‘WA’, ‘SK’, ‘L’ and ‘M’ Any ideas?

160
Databaser
I
InfoWranglerInspiring
posted in Formulas
Nested IFs from multiple columns

Hi folks, first-time poster here; I’ve looked around and tried some things but none of the solutions I’ve found worked, so here we go: I currently have an IF formula that displays 3 different results based on the content of one column, and I am trying to display a 4th result based on the content of another column. Here’s what I have so far: IF({EXP}="",“UNLISTED”, IF({EXP}<TODAY(),“EXP”,“ACTIVE”, ) ) I’ve tried: IF({EXP}="",“UNLISTED”, IF({EXP}<TODAY(),“EXP”,“ACTIVE”, IF({MARKET}=“AUCTION”,“AUCTION” ) ) ) …but Airtable just deletes the third string. Other variations (IF/AND, IF/OR, and ELSE) generate an error. I’m sure what I’m trying to do is simple enough, but it’s giving me a headache. Please help! Thank you, Shawn

145
I
A
Alba_Machado2Known Participant
asked in Formulas
Time in an IF formula

Hi everyone, For the freelancing part of my making-ends-meet endeavors, I have started tracking tasks. Usually, that’s no problem with Airtable. But once in a while, I’ll forget to enter a task when I start it, and, instead, enter it when I remember to do so. That means CREATED_TIME() in the primary field gives me that later time, and that’s not helpful. I tried to get around this by using this formula: IF({Manual Date & Time},{Manual Date & Time},CREATED_TIME()) But if I toggle on “Use the same time zone…,” the CREATED_TIME() goes wonky. And if I don’t toggle on “Use the same time zone…,” the {Manual Date & Time} goes wonky. How can I get the time zone to be consistent in this formula field whether it’s autogenerated or manually entered? If someone could help me figure this out, I would be really grateful!

92
A
B
bederNew Participant
posted in Formulas
Formula Help: If/Or Benefits Calculation

Hi! I’m trying to calculate a formula field that looks at 2 different fields (# Benefits Remaining) and (End Date) and returns either “Active” or “Inactive”. If the # Benefits Remaining is >=1 OR the end date is after today, it is active. Some of our benefits have a specific number available and others last for a specific duration of time. Here’s what I have so far - what am I doing wrong? IF( OR( TODAY()<{End Date}), ({# Remaining}>=1), ), “Active”, “Expired” )

41
C
A
Adi_MenayangKnown Participant
posted in Formulas
Concatenating two rollup fields with array(unique), but comma is missing

Hello, I am trying to create a field that concatenates two rollup fields that have the array(unique) formula in each of them. But the concatenated result eliminates the comma. You can see in the 10th row, the comma between “Refugee Support” and “Water access, sanitation, and hygiene” is missing. Here’s what I used: IF(OR({BACKEND_Subject Areas of Proposals as Lead}=BLANK(),{BACKEND_Subject Areas of Proposals as Partner}=BLANK()), CONCATENATE(ARRAYUNIQUE({BACKEND_Subject Areas of Proposals as Lead},{BACKEND_Subject Areas of Proposals as Partner})), CONCATENATE(ARRAYUNIQUE({BACKEND_Subject Areas of Proposals as Lead}& ", " &{BACKEND_Subject Areas of Proposals as Partner}))) For context: Each row is an organization that have submitted a proposal to us. I would like the field “Subject Areas of Proposal(s)” to be a tidy array of subject areas tied to that organization’s proposals, which they selected from a standard taxonomy list (Linked Record in the forms). Sometimes the organiza

603
Kamille_Parks11
D
Dan_EmmerieParticipating Frequently
posted in Formulas
Datetime_parse error

This is probably easy, but I’m using DATETIME_PARSE so that a field being called up can be converted to date, but sometimes those fields that are called up don’t have dates, so it returns an error where there is no date to convert, how do I make it return a blank?

71
D
M
Murat_OzkokNew Participant
posted in Formulas
How to consolidate 2 tables in to 1 table

Hi Team, We are trying to link a few records between 2 different project bases and we hope that you can show us the right direction. Problem Statement: We have 2 Project Bases, External and Internal, and we would like to add the records in External Base to another table in Internal Base. Progress so far: We can sync all records from Table 2 in one base to another base with no problem. Help Needed: Can we do a formula or rule set to add records from synced table to the another table in the same base ( Vlookup)? We would really appreciate your thoughts and guidance on this one. Thanks in advance.

91
Kamille_Parks11
J
Julia_Moreno_PeNew Participant
posted in Formulas
Having trouble with writing the right formula for IF Conditional drawing from two columns to have results in a third column

Hello! I’m having trouble with a formula. I hope someone can help:) Issue: Certificate program provides two chances for someone to pass the course. If a person “passes” Quiz 1, they automatically pass the course. If they don’t pass, they can try it again with Quiz 2. If they pass Quiz 2, they pass the course. I need to create a column that states that if there is a “pass” in either Quiz 1 or Quiz 2, they pass the course. I’ve been writing out formulas and I’m getting some things wrong. Can someone help me figure this out? Options: Pass, Did not pass, Not Started These are some of the examples I’ve been writing the formula in a third column called “Final Results”. I want this column to read “pass” or “did not pass”. This is the column that will be exported to a certificate platform that specifies only one field for people who pass. IF((Q1 IS_SAME(pass) OR(Q2 IS_SAME(pass))) IF({Q1}IS_SAME(Pass),IF({Q2}IS_SAME(Pass),“Pass”) IF(){Q1}IS_SAME(Pass), IF(){Q2}IS_SAME(Pass),“Pass”) IF( {Quiz 1

00
J
E
Ed_CooperKnown Participant
posted in Formulas
Show blank in cell if

I have a formula field that calculates the difference between {End date} and {Start date}. Works fine If there is no Start Date or no End date I want the cell to be blank instead of NaN Been trying everything. HELP!!

73
E
L
Lisa_HinesNew Participant
posted in Formulas
SKU number formula

I have scoured the community and keep bumping up against an error when trying to create my formula for a SKU number. I have multiple product categories: Mugs, Berry Bowls, Plates, etc. I would like to have a SKU number that displays the current year, current month, a truncated category, and the autonumber field. The categories will change to a two or three letter combo, like Mugs becomes MUG, Berry Bowls becomes BB and so forth. The SKU should end up like this: 2022-03-BB-00001 I’ve tried the SWITCH command, but kept getting an error. I don’t currently have a date column, but do I have to have one to include the year and the month? Also, the autonumber column doesn’t add the zeros. How can I get the autonumber field to display a 5 digit number? Thanks in advance. Lisa

182
Kamille_Parks11
T
Tom_BlanchardNew Participant
asked in Formulas
Formula for Including "st" "nd" "rd" "th" in Dates

Hi, Does anyone have a formula to format a date to include the “st” “nd” “rd” “th” ? For example, I would like 3/21/2022 to read as Monday, March 22nd I can only figure out how to do Monday, March 22 Thanks! Tom

291
Kamille_Parks11
E
ES11New Participant
posted in Formulas
How to use functions used in Excel in airtable?

How do I convert the function “=CONCATENATE(REPT(0, 3 - LEN(A1)), A1)” used in Excel into an airtable formulas?

20
E
P
Peter_MartensNew Participant
posted in Formulas
Lot Code Generator

We have a farm and grain processing facility. We installed an on farm truck scale a year ago and have been manually filling out scale tickets, bills of lading, and a scale log. I want to set up a system in airtable as a scale log with the following data: Unique ID Date Origin (Name, Address, City/State/Zip, Phone, Email) Destination (Name, Address, City/State/Zip, Phone, Email) Product Status (Organic/Transitional/Conventional) Lot Number ******* (more later) Gross Weight Tare Weight Net Weight Incoming Moisture Product Target Moisture Moisture adjusted weight (this is a formula we are already using) Scale Operator Notes From that I would need to be able to generate: Scale Ticket Bill of Lading these 2 items I’m guessing will something like “Page Designer” Most of this is straightforward, what I’m having trouble with is trying to set up a formula to automatically generate a lot code based on other data already in an entry. Our current lot code format is: Origin Initials-Status,Produc

81
kuovonne
J
Jake_RussellNew Participant
posted in Formulas
Concatenate merging 3 fields only if not empty

I have up to 3 fields that I want to merge together and separate each field with a comma, but only if the field is not empty. If any of the 3 fields are empty, I don’t want them included so that I don’t have extra commas in the resulting formula field. Any ideas how I achieve this?

150
J
K
Kamil11New Participant
posted in Formulas
Sale sumup by month

Hi, I am trying to create a sum up view form my services sales. I provide only 7 services but I sell them always with different price and expense. This is what I have: Table 1: Service - Service (Service name + reference number of each sale), reference number of each sale, Sale date, Sale month (from Sale date), Price (different for each sale!) Table 2: Expenses - Name of expense (Service Name+date of expense), date of expense, amount paid Table 3: - Service name - linked to Expenses and Service table, where I rollup all sales and expenses for each month (using conditions) then I can get net profit. The problem is that every new month I have to create new conditional roll up to get monthly sale, expense and net profit. Is there any way I can create this reports automatic? Creating views in ech table won’t be enough for me as I can’t get the net profit values using only views :confused: Thank you all in advance! Kamil

20
K
S
Suraj_KapoorNew Participant
posted in Formulas
Conditional in a RollUp

Hi! I am trying to write a conditional formula on a rollup… In table A, I have a dropdown column (“sponsored”) with multiple options, not all rows have a dropdown against them. I have table B that has a rollup column, that I would like to sum the values IF the row in table has a dropdown against it. I am using… IF({sponsored}, SUM(values)) …but it’s not working!

12
S
D
Dan_EmmerieParticipating Frequently
posted in Formulas
Blank when #ERROR! while Date Formatting

Thank you ahead. This is probably an easy one, but I could use the help since I’m not good at formulas or programming. Basically I am getting data from an API that is giving me IDs that need to be matched with Values. I got that happening but where the Values are Blank, I get an error, possibly because it’s trying to format it into a date. The errors are keeping it not working with some reports, so need them to result in blank() when erroring. Here’s my formula: DATETIME_PARSE((IF({Custom field items 1 id custom field}=“5a9866ffd6afbd6de1c0d07b”,{Custom field items 1 value date})&IF({Custom field items 2 id custom field}=“5a9866ffd6afbd6de1c0d07b”,{Custom field items 2 value date})&IF({Custom field items 3 id custom field}=“5a9866ffd6afbd6de1c0d07b”,{Custom field items 3 value date})&IF({Custom field items 4 id custom field}=“5a9866ffd6afbd6de1c0d07b”,{Custom field items 4 value date})&IF({Custom field items 5 id custom field}=“5a9866ffd6afbd6de1c0d07b”,{Custom field it

123
D

Badge winners

Show all badges
Terms & Conditions