Skip to main content

Formulas

Leverage this space to unlock the power of Airtable formulas.

  • 5,940 Topics
  • 18,230 Replies
5940 Topics
Y
Y_KKnown Participant
posted in Formulas
Keeping Airtable field formula in Notion as a backup

I use Notion as a notebook to keep track of the formulae I’ve written in Airtable, it has a handy “code” block to highlight language syntax. Does anyone know which language I should select that’s most similar to Airtable formula syntax? It doesn’t have “Excel” as an option.

71
B
M
michael_frankhaParticipating Frequently
posted in Formulas
Form to return part of another field, but where () are used to make what is removed

hi, I really have no idea what Im doing here, I have a field that is a series of number and letters followed by a ( then more letters and numbers followed by ) example field 1 bob( Joe ). and I want Bob the amount of chariters inside and out of () is variable, the only thing I can go off of is the ()

52
M
F
Faculty_SchedulKnown Participant
asked in Formulas
Formula field efficiency

When creating a formula field with an IF statement where I want to check the value being returned before deciding to use that value or display something else I run into an issue where it seems terribly inefficient to have to basically have to call all the functions twice. IF({INSERT CONDITION HERE WITH A LOT OF FUNTIONS} > 0, {INSERT CONDITION HERE WITH A LOT OF FUNTIONS}, “NO”) Is there any way to avoid having to basically calculate a value for something twice when using it in an If statement? I’d hate to have to make a field just to hold calculated values that are only useful so that the IF statement doesn’t need to calculate the same thing twice. Our fields are already a bit bloated as it stands. Any creative solutions?

45
N
B
Brett_DrachenbeNew Participant
asked in Formulas
RIGHT(SEARCH()) odd results for last name first name reverse formula results

I am getting in inexplicable result from a RIGHT(SEARCH()) formula. My .csv import includes the field [Payroll Name} formatted as LastName, FirstName. I found a name reversal formula on this thread: RIGHT({Payroll Name}, SEARCH(‘,’, {Payroll Name})-2)&’ ‘&LEFT({Payroll Name}, SEARCH(’,', {Payroll Name})-1) The formula returns the last name correctly, but the first name returns a maddening variety of results. Some first names are correct, others include the comma and space from {Payroll Name}, some include the last letter of the last name, or remove the first, second, or third letter of the first name as shown below (The FirstName and LastName fields are the respective portions of the {Name} formula, shown for clarity). I have tried a variety of values for “-1” and “-2” (among other things), with unsatisfactory results. Of course I can correct the first name problem in Excel before importing, but I am super curious about how to fix it in Airtable. What is going on here? How d

225
B
A
Aline_Branco_WiParticipating Frequently
asked in Formulas
Completed within due or out of due date

Hi everyone! After checking several topics I still couldn’t find something to solv the problem I’m facing, so here I am. We have a table called Tasks and the following fields are important for this: Due Date (Date field) Completed (Checkbox) Completed within due date (formula) We added a Last modified time field to look to Completed field, so it returns the date that collaborator finished the task. To compare the date that Completed was checked with the due date, I tried the following: IF({Due Date} = {Last modified time},“1”, IF({Due Date} > {Last modified time},“1”, IF({Due Date} < {Last modified time},“0”))) The problem is even if Due Date and Last time modified has the same date, the formula field is returning 0 and my guess is beacause of the time. Due date was not created with time - because it doesn’t metter for us - but if we allow time, it shows “11/16/2022 00:00”. So Last time modified will always be after due date since we will always finish our task after 00:00. And

34
A
N
Nazar_LupakNew Participant
posted in Formulas
Help with calculating the value in two tables

Hi community! :ukraine: I need some help with some basic formula creation. I have a 2 table, first - my clients, second - affiliates. I want to count how many clients each affiliate bring to my services. Each affiliate has thair unique ID, witch are in bouth tables. I clearly understand what formula should do, but I’m new to any programing skills so do not know how to wrote it correctly using formulas syntax. My formula should find affiliat ID in Clients table, count them, and show result in Affiliat table alongside to each affiliate. Will be thankfull for any advise with this.

01
ScottWorld
J
Jim_DiGiovanniNew Participant
asked in Formulas
Concatinate Numbers field to 6 digits ( add 0s )

This is a newbie question. I have a sequence of numbers in my inventory and these are not auto generated, so I am either working with Number or Formula here. I am trying to add zeros to a sequence so that all numbers have 6 digits. Currently, my numbers start at 0 and go up to 4 digits, I think I am looking for an If / Then LEN formula, but I can’t figure out the syntax to make it work. For example, I need 5 to read as 000005, and I need 1401 to read 001401. etc. Thanks in advance.

463
Kamille_Parks11
Leah_Relish
Leah_RelishKnown Participant
posted in Formulas
Remove semi-colon when field is blank - CONCATENATE

I would like to omit the semi-colon when a field is blank, and I know it can be done using IF, however my brain cannot wrap around how to pull it together! Can anyone help? Here’s my example: The current Formula I am using: Much appreciated :slightly_smiling_face:

126
Leah_Relish
M
Moira_KrierNew Participant
posted in Formulas
"Weeks Until" Formula?

I need a formula column to show weeks until a Due Date. I have a Due Date column, but I can’t figure out how to set up the formula column to calculate the number of weeks from today until the Due Date. And I need it to update continually. Is this a possibility? Any advice on making this work? Thanks!

63
Kamille_Parks11
J
Jason_HullNew Participant
posted in Formulas
Alphabetize Rollup Result

I am doing a rollup on a field that has text (2-letter US State): ARRAYJOIN(ARRAYUNIQUE(values),"\n") I am wondering if there is any way to alphabetize the result output?

3912
M
D
DallasProducersNew Participant
posted in Formulas
Formula to hide data in a cell if it matches certain data

Is there a wide to hide data in certain cells using a formula? I am trying to work with date data coming from Memberstack that appears in UNIX form. I have a column converting the UNIX data to readable data. DATEADD('1/1/1970',{UNIX Renewal Date from Memberstack},'seconds') I have a column for Manual renewal dates. Some people are outside the system. I have a column that merges the two. IF there is a Manual Date, use that, if not, pull the Converted UNIX renewal date. It works mostly fine. It is setting the manual renewal date back a day, which is weird, but not the end of the world. IF({Manual Renewal Date}, {Manual Renewal Date}, {Converted Renewal Date From Memberstack}) Now the issue is that the UNIX baseline of December, 31, 1969 is showing up anywhere there is a blank cell. Is there a way to get rid of that? Essentially I am looking to either have a blank cell of a simple N/A instead of the 1969 date. Screenshot attached for reference. Thanks in advance!

185
K
R
Robin_GomezNew Participant
posted in Formulas
If statement in rollup

how can I do an if statement in a rollup? so I can check a value before the rollup is completed thanks

41
K
S
Steve_Hazeltin1New Participant
posted in Formulas
Streak Tracking

I’ve seen this asked a couple places on the forums, but I haven’t seen a response yet. Looking for a way to track streaks for a habit tracking base I’m building. As you can see from the screenshots, I’m using one table for habits, and one for days (called Log). I’m assuming the streak field (consecutive days done) would be on the habit table, but that it would likely have to be some kind of fancy rollup formula that references the Log table. Any ideas on how to accomplish this?

231
K
C
Charlie_PestiNew Participant
asked in Formulas
Roolup and list "Complete" tasks

I have a “Tasks” table with a checkbox (Open OR Complete). I want to list (Rollup) all “Open tasks” in one field and “Complete tasks” in another field on a different table (Clients) What should the formula be?

85
C
K
Kirsty_HoldenNew Participant
posted in Formulas
Formula to work out number of days before another

I’m looking for a formula that where I can have a field (1) with a start date in, a second field where I can add a number to represent days (3 days, 4, days, 10 days etc) and then another field where the formula is to calculate the specific days before field (1). Please tell me is exists! :winking_face:

44
K
Karlstens
KarlstensBrainy
posted in Formulas
Rollup Field - Include Integer blank values

Hi All, I have a Linked Field containing multiple records. One benefit of having a Rollup Field vs a Lookup, is when constructing JSON data the Rollup Field will provide a comma for empty/null values (where a Lookup will only return an array with less elements for any empty lookup returns). However, it seems my observation is only true for a Rollup Field referring to String data, but not a Numbers field. In my example above, how can I get the Rollup to return [7, 11, ]. Or for example, for even more linked records, a Rollup to return [1, ,2, , ,3,4,5] ? I am thinking that, for a workaround I need to create a Formula Field, that contains my Number field data as a string, so that the Rollup Field can then ArrayJoin(values) the data as a string and include the null fields, and then my JSON parser script re-convert that string data back to integer? :sob: Tell me that there’s some kind of Rollup Trickery that I can employ here so that null/blank() number fields return to a Rollup.

274
Karlstens
N
Nicole_AbascalNew Participant
asked in Formulas
Need help correcting the NaN from my formula

Hi there! I am looking to add to my formula (below and attached) that when the date is blank or 0 for the field to say “due today” CURRENT FORMULA DATETIME_DIFF({SHIP}, NOW(), ‘days’) & " " & IF(DATETIME_DIFF({SHIP}, NOW(), ‘days’) > 0, “days remaining :hourglass_flowing_sand: ”, “days overdue :boom: ”)

137
N
J
Jon_StephensonInspiring
posted in Formulas
Formulae to calculate usage

If I have a table as follows : Date/Time. Numeric value. Gain Since Last. Usage per day Record 2. 10/1/2022 100 50 10 Record 1. 1/1/2022. 50 Is there any formulae that would calculate the two fields in italics please? Thanks in advance!

52
J
Soly
SolyBrainy
asked in Formulas
Create a formula that uses RIGHT() and LEFT() simultaneously

Hi! I created a field that concatenates the last name and full name in a table. I am trying to write a function that takes the 2 letters first letter of the first name and 2 last letters of the last name. But it only returns the 2 first letters of the first name. Here is the formula CONCATENATE( UPPER( LEFT( {Full Name},2), RIGHT( {Full Name},2) )) How can we make it happen ? Thank you

172
Soly
M
Matteo_SaporettNew Participant
posted in Formulas
IF Contains Formula

Hi all, I need to create a formula column to calculate price of shipping equipments. I have multiple equipment types in a single select column e.g. (20DV, 20OT, 40DV, 40HC, 40OT) For all the “20” regardless if it’s DV or OT (same for the 40) the price is the same. Therefore I need an IF statement kind of like IF Equipment contains “20”, price is … thanks in advance Matt

2311
TheTimeSavingCo
A
Aj_AngoluanNew Participant
posted in Formulas
Formula not working for pass fail remarks

Hello everyone, I am trying to create a scoring system for our small team but one of the formulas I used is not working properly. Here’s the formula I used: and here’s the result: I would be grateful if someone could help me with this. Thanks in advance!

41
K
P
Praise_HallNew Participant
asked in Formulas
Automating deadlines for automated tasks

Hello! I am building out a new base for my department which runs a variety of Programs throughout the year. Thus far I have set up some dummy data (i.e the “steps” you see in the screenshot) to successfully link my tasks to projects. There is a flow to how we approach the different programs we offer in terms of our when open applications, when we contract instructors for a workshop and so on and so forth. I have it set up so that every time I create a new project - based on the project I select, the are a number of tasks that will be automatically generated. I am now hoping to make it so that when a “Program Start Date” is entered, the “Task Due Date” will auto populate a deadline for each task based on a formula that aligns with our overall event timelines. Based on some previous community posts and through general research I see that the SWITCH formula might be the right way to go. However, after trying to create and implement what I thought was a proper formula - there are no tasks

204
P
K
Kitty_MatzNew Participant
asked in Formulas
Formula works but returns #ERROR on blank cells

I have this formula WORKDAY({Release Date},-{weeks out in #days}) it returns the date that I need but if there is a cell with Release Date empty, it returns #ERROR. I have tried this formula IF( AND( {Release Date}, {weeks out in #days} ), DATETIME_DIFF({weeks out in #days},{Release Date},‘date’), BLANK()) it removes the #ERROR on the cells that it should, but it puts #ERROR in all of the date fields, I lose the dates that I need. I have tried this formula WORKDAY({Release Date},-{weeks out in #days}, BLANK()) this one puts #ERROR in all cells. what I need: the first formula that returns dates, but if the {Release Date} cell is empty, I want to see a blank rather than #ERROR.

121
K
C
Cat_CrawfordParticipating Frequently
asked in Formulas
Find Date Time difference only IF a certain single select field is selected

Surely this is an easy formula, but I just can’t get my brain to bend this way: I am using Airtable for my timesheet for my employees. I would like to improve upon my existing “Total Hours” formula by showing “Total Hours by task” , and each task is a single select item in another field. Here’s my “Total Hours” formula currently: DATETIME_DIFF({Time Out},{Time In},‘minutes’) / 60 Field for the different tasks is aptly named: “Task” Specific Single Select is “Teaching” Please help! Thanks!

123
C
J
Jeremy_BloomNew Participant
posted in Formulas
Count Formula for list separated by comma

Hi All, I am looking for a Count formula to count up the different “Product SKU’s” in the a column. The SKU’s are separated by a comma

51
ScottWorld

Badge winners

Show all badges
Terms & Conditions