Skip to main content

Formulas

Leverage this space to unlock the power of Airtable formulas.

  • 5,939 Topics
  • 18,228 Replies
5939 Topics
J
Joannis_TomNew Participant
asked in Formulas
Delete line break

Hello community, I have a problem that is when I scan a barcode with my tablet the following characters are displayed like this: 0096 UNIT0855 121F21 I would like it to be displayed like this: 0096UNIT0855121F21 So I would like a formula that allows me to do this task automatically. Could someone please help me?

120
J
L
Luke_AnnisonNew Participant
asked in Formulas
Dates and Times an Hour Off

Hi Everyone, I’ve come across an issue where I can’t seem to get the date to print the correct time within a formula. Please see example, I’ve stripped it down ot its bare bones. On the left is a date (and the time is correct). On the right is that date reprinted as a function. It appears to be an hour off. This is the formula for the function (simply reprinting the date): It also gives the same result (incorrect) if I set the time to my time zone, which is GMT. Any help appreciated

628
L
A
ADKNew Participant
posted in Formulas
Populate Single Select based on another field

Hello, Trying to populate a single select based on text in another field. Sometimes I want it automatic, sometimes it needs to be manual, so a formula field alone isn’t appropriate. Currently running a nested IF formula, but I just need help with the syntax to make it choose X, Y, or Z from a single select field instead of printing it in the fomula field. Any help greatly appreciated. Thanks! The formula (which is working great for printing XYZ in the formula field): IF( SEARCH("x", {Column A}), "X", IF( SEARCH("y", {Column A}), "Y", IF( SEARCH("z", {Column A}), "Z" ) ) )

941
Kamille_Parks11
R
Research_Team_SNew Participant
posted in Formulas
If statement with dates

I am looking to create a formula where if my date field is between certain dates then my quarter field would tag appropriately. So if the date is between 10/01/21 - 12/31/21 then the field “Quarter” would say Quarter 4. If the date is between 1/1/22 - 4/1/22 then the field “Quarter” would say Quarter 1. I don’t if this can be done with formulas or if there is a better way to do this but would love any help/suggestions to automate the process of manually updating my quarter field.

21
Kamille_Parks11
M
Mustafa_BNew Participant
asked in Formulas
Is between Monday and Sunday of previous week

Hi! I’m trying to display ‘Yes’ on records that fall between Monday and Sunday of the Previous week, no matter what day of the week it currently is. My code works, but only if it you’re looking at it on the Monday of this week, otherwise it’s off. IF( AND( IS_AFTER( {Completed Date}, DATEADD(TODAY(),-8,'days')), IS_BEFORE( {Completed Date}, DATEADD(TODAY(),-0,'days') ) ), 'Yes','No' ) I’ve also tried this, but again it has to be run on the Monday of the current week to be accurate… IF( AND( IS_AFTER( {Completed Date}, DATEADD(TODAY(), -8*(WEEKDAY(TODAY(), 'Monday')+1),'day') ), IS_BEFORE( {Completed Date}, DATEADD(TODAY(), 0-WEEKDAY(TODAY(), 'Monday'),'day') ) ), 'Yes','No' ) I Hope someone can help me out :slightly_smiling_face:

183
M
Nick_Robeson
Nick_RobesonInspiring
posted in Formulas
"Next Due Date" formula using 3 Date Fields

Hi all, The review process for our Creative team typically consists of two reviews prior to the final asset being uploaded to Airtable. Because of our review process, the Project Manager is responsible for assigning three due dates to each line item (asset) within the Creative table: V1 Due Date, V2 Due Date, Final Due Date. I would like to have the option to hide all three of the fields listed above and have a formula field that will display only the Next Due Date in a user friendly format. Please see below for the set up of the date columns mentioned with examples. The “Next Due Date” column is currently blank as I try to come up with this formula. A nested IF statement should do the trick, just haven’t yet figured it out! Any help is greatly appreciated!

21
Kamille_Parks11
Z
zodeciNew Participant
posted in Formulas
Formula formatting? conditional IF

if (Check if Not Converted)=1, {Exchange}*{Total} cant seem to get this to work any help appreciated ty

31
Kamille_Parks11
J
Jean-Baptiste_1New Participant
posted in Formulas
Create attachement from another url field

Hello, I have a url type field with the url of an image, and I would like to know if it is possible to create an attachment automatically with a formula to upload the image in Airtable from its url. example : field_image_url: “https: //coolimage.png” field_airtable_image: UPLOAD HERE

11
kuovonne
Theodora_Hart
Theodora_HartInspiring
asked in Formulas
WORKDAY with IF and DATEADD, pulling from multiple fields?

Hello! I’m able to make this formula work but I keep getting errors when I try to insert ‘WORKDAY’ into it: IF({Original Release Date}, DATEADD({Original Release Date}, VALUE(’’ & {Days Needed Before Release}), ‘days’, ‘’)) I am trying to return the closest workday after subtracting a formulated number of days from my original release date. Thanks!

219
Theodora_Hart
M
mustOfGotLostNew Participant
posted in Formulas
Formula Pop Up No Longer Shows

Odd thing when creating a formula the help pop to the left no longer appears. Is there some setting I need to enable.

114
A
A
Alex_NesbittNew Participant
posted in Formulas
Replicate Excel index and match functionality

Hello, I am looking for a formula that could help me achieve the same outcome that I could do in Excel using an index & match array formula. I am trying to group classification data based on the hierarchy. You can see from my excel screenshot below, Column1 is what I am trying to achieve. =INDEX([Code tite],MATCH([@[Grouping code]],[Code],0),1) Here is a link to my airtable view: Airtable - Share Here is a link to the excel: AirtableIndexMatchFormulaArray.xlsx Thank you very much!! Alex

61
Bill_Felix
B
bima_saktiNew Participant
posted in Formulas
Supported locale modifiers for SET_LOCALE

my locale format was DD/MM/YYYY i try make it work but it looks like this! Screenshot (3)|652x222 i really wanna know why keep telling March NOT August

82
kuovonne
C
Clyteroll_CabuaNew Participant
posted in Formulas
Multiple Subtitue

Multiple Substitute in Airtable, I have one field that contains jebirish information but it contains one of the information. like ’ Field updated UNDO Your Account Name: Reillence Lester Diocson Your Account Number: 9189160337 Your Pin Number: 860503 I want to make a formula get the Account Number only not the Jiberish character. How should I do this?

52
Justin_Barrett
H
Holly_MattersonNew Participant
posted in Formulas
'If' Formula - equaling Date

Hello, I am trying to use an IF formula to equal a date but can’t find how to do it. I want to create IF({WEEK} = “Wk 1”, “15/01/22” example. wk 1 = 15/01/22 wk 2 = 23/02/22 Wk 3 = 30/02/22 Does anyone know how to do this? Thanks

438
Justin_Barrett
A
Albert_belingarNew Participant
posted in Formulas
Formulas to verify IBAN

Hi, i’m using Airtable to help my customers for refund. so the form on my website is working fine but i need to improve it. Customers enter their bank details. I would like to make sure that the key is correct. i have a huge formula in Excel and i want to know if it’s possible in airtable. thanks

601
A
C
Chattanooga_FunParticipating Frequently
asked in Formulas
Using Switch formula

I am using the switch formula to have Airtable populate a numerical value into a cell based upon the input chosen from a multiple select option. Prior to this I was typing the amounts in and it was working properly. Now I have set up the switch to populate the cell and it works and puts the correct number into the cell that the formula was pulling data from, But now I receive an error message in the end cells. Why is this? The cell the formula was pulling from previous has the same numerical values showing in the cells, it is just being generated by the switch formula instead of me inputting it into the cell. Thank you for any guidance.

152
C
J
Jinghan_HeNew Participant
posted in Formulas
IF formula usage help

Now I have got 2 tabs, the first one has a list of events, including the company name, event date and event notes (Each company could correspond to several events). In the second tab, I created a field called the most recent event, which filters all events for each company in tab 1 , and only show the most recent event of each company in tab 2(the name of this tab is Deal). Now I want to link the most recent event field back to tab 1, and only show the event note of the most recent event. My current formula is: IF({Date}={Most Recent Event (from Deal)}, Event Note) this formula seems right but doesn’t work, and the notification says that “most recent event (from deal)” cannot be found. How can I fix my formula to make it work?

31
Alexey_Gusev
J
jy22New Participant
asked in Formulas
If...else and DATETIME_DIFF

Hey folks, I’m trying to calculate the number of days someone has worked in a job. I’m familiar with the DATETIME_DIFF() function, which works great when you have a Start Date and an End Date, but what if you don’t have an end date? In other words, imagine that someone is currently in their position, therefore the field registering their end date is empty. The theoretical solution I came up with is creating another field with the formula TODAY(), and then, if the field for end date is empty, use the field for today to calculate the difference between today and the start date, and calculate the time that the person has worked. I’m having trouble with the syntax! Any thoughts be greatly appreciated.

306
kuovonne
I
Ian_TaylorNew Participant
posted in Formulas
IF Statement to TRUE/FALSE if one field data is equal to another MULTISELECT field

Hi all - need help! I have a Multiselection of “profiles” linked to a store. I then have items which may relate to one of those profiles. I need a formula to look at the “item profile” and see if it exists within the “store profile” multiselection - returning a simple true or false will work. I am hoping the below helps explain further

152
Justin_Barrett
M
Meri_WilsonNew Participant
asked in Formulas
AND OR + IS BEFORE IF statement help

Hi there! I’m trying to do a fairly simple formula but it has quite a lot of conditions. I’m a beginner and not sure where I’m going wrong, could someone help? The board is a basic project overview. I’m trying to set a column to display “on track” or “overdue” depending on what the due date and status of the item is. My current formula is written like so: IF( AND(IS_BEFORE(Due,TODAY(), IF( OR( Status = “In Progress”, Status = “With Client”, Status = “Not yet started”, ), “Overdue!”, “On track” ))) Any help would be greatly appreciated! Meri

384
M
M
Margaret_WongNew Participant
posted in Formulas
Combining AND and OR Filters

So I’ve gathered that I can’t use both AND and OR filters, but the workaround is to use formulas. Problem is, I can’t find a formula to solve my problem. I currently have two columns that I want to be able to filter, a “status” column and a “release date” column. This is the filter I want to set up: WHERE “release date” is on or after 14 days ago OR “release date” is empty AND “status” is not archived. Is this possible using formulas? Or are there too many layers? Thanks in advance for everyone’s help!

733
J
A
automadienInspiring
posted in Formulas
Date formula to display status

Hi there! There are a few formulas out here for similar-ish issues but I can’t find on that matches exactly what I need. I’m trying to create a formula that shows “Done” if the date is in the past, “Upcoming” if in the future or “In progress” if it’s on the day. The first two are working fine, but I can’t get it to show “In Progress”. It’s driving me insane as I can’t figure out why :frowning: IF( IS_AFTER({Live Date}, TODAY()), "✅Done✅", IF( {Live Date} = BLANK(), "🛑DATE NEEDED🛑", IF( IS_BEFORE({Live Date}, TODAY() ), "⏰Upcoming⏰", "🏃‍♂️In progress🏃‍♂️" ) ) )

283
Justin_Barrett
B
bima_saktiNew Participant
posted in Formulas
How to express Switch()

Sorry I dont understand formula. Iam using 2 row for my stock, first row is (Stock-DO), Second is IF({Stock}<0,‘ :x: ’,IF({Stok}=0,‘ :heavy_minus_sign: ’,{Stock})) I DO belive there is a better solution. So how to express Switch((Stock-DO),Stock<0,‘ :x: ’,Stock=0,‘ :heavy_minus_sign: ’,Stock>0,‘Stock’)

33
B
K
Kimberly_MilruyNew Participant
posted in Formulas
Formulas for Due Dates to Set a Status Field

Hi everyone! I am a beginner and was trying to modify other formulas I found on here but couldn’t get them to work. Would someone be able to help me nail down a formula that would check my due date field and then set a single select Urgency field depending on the due date for the following: -Not Urgent (due dates that are farther than 1 month away from today’s date) -Upcoming Due Date (for due dates that are 1 month out or less) -Past Due (for due dates that are before today’s date) -TBD (for any due date fields left blank) Thanks!!!

201
Justin_Barrett
A
AJTechInspiring
asked in Formulas
Formula to remove a space after a comma in a string

This is probably really simple - can someone please help with a formula… What I am trying to achieve: remove space after comma in a string Example of existing string: Kim Jones, Ted Jones, Fred Jones, Sam Jones What I am after is a formula to convert the above to: Kim Jones,Ted Jones,Fred Jones,Sam Jones I have up to 11 names in my string - any advice is greatly appreciated.

232
A

Badge winners

Show all badges
Terms & Conditions