Skip to main content

Formulas

Leverage this space to unlock the power of Airtable formulas.

  • 5,941 Topics
  • 18,238 Replies
5941 Topics
A
Ana_AmorimParticipating Frequently
asked in Formulas
Financial Formulas: is Airtable considering to launch financial formulas in the future?

Hi, guys! I couldn’t find a Roadmap for Airtable (I suppose it doesn’t exist) and I’d like to know if Airtable has plans to add financial formulas to the current list of formulas. I have a table where I collect some financial indicators and I’d like to do some basic calculations like standard deviation, variance, etc. I can’t do that in Airtable and I have to use Excel for that. I know Airtable isn’t a spreadsheet, but I hope developers could add more functionalities….

70
ScottWorld
W
Will_CarterNew Participant
posted in Formulas
Formula date add using "business hours"

Hello community, I have a question about trying to create a schedule. We have created jobs that has an x amount of hours to do those jobs. So I have a start date for example 7/1/2021 and the job is to take 20 hours which would be two 10 hour days. When I try DATEADD({Start},{Duration},‘hours’) or replace ‘hours’ for ‘days’ it literally adds 2 days for a completion date of 7/3/2021 but that’s wrong. It should be 7/2/2021. I tried to format the start date to include a time, however, Airtable looks at everyday as a 24 hour day. So then I come up with start time of 7/1/2021 7:00am and completion date/time of 7/2/2021 3:00am. Is it even possible to create business hours so when calculating start and completed by the dates are correct? Because ultimately I’m trying to set my start and duration as a Gantt view to show my forecasted schedule.

191
Justin_Barrett
W
Wayne_MerlinoInspiring
asked in Formulas
Help with an IF AND formula

Hi, I am in need of some guidance on a formula. I have two columns that I am comparing against each other - basically determining the dollar difference between the two and then the % difference that amounts to. Once that is figured out, I want to grade the resulting % as pass or fail, but I want to do it as a range. Here is an example: A) Final Estimate: $10,000.00 B) Preliminary Estimate: $9,000.00 C) Difference in $: ($1,000.00) D) Difference in %: (10%) Here is where I need a formula: IF the result of D is within a range of 10% under to 10% over the Final Estimate then the grade would be “Pass”. IF the result is either more than 10% below or over 10% above the Final Estimate, then the result is “Fail”. So I want to allow a spread of 10% each way off of matching the numbers as allowable. Any help would be appreciated. Thanks!

170
W
J
Josh_MartinNew Participant
asked in Formulas
Problem with IF + BLANK() + DATETIME_FORMAT

Hey all, I’m trying to build a formula right now that generates copy based on various fields, specifically a date field. Some of these values have a date and others do not. Whenever I try to build the formula using DATETIME_FORMAT({Presale Start}, "ddd, M/D") values that don’t have a date show as #ERROR!. That being said, I tried using an IF statement but it won’t even save the formula. Can’t tell what I’m going wrong but this is the latest version I’ve tried to use: IF({Presale Start}=BLANK(), “0“, IF{Presale Start}!=BLANK(), DATETIME_FORMAT({Presale Start}, "ddd, M/D") Any help would be much appreciated!

227
Kamille_Parks11
J
Josh_MCAParticipating Frequently
posted in Formulas
Automatically Populate Dates with Jobs

I am considering purchasing AirTable Pro. But before I do, I want to make sure that I am able to automatically populate a calendar with AirTable Pro. Certain jobs I have are weekly and routine, and I want to set dates (Mon, Tues, Wed, etc) and have all jobs show up in my calendar for the select days.

41
Justin_Barrett
T
TemiNew Participant
posted in Formulas
Help with automation - master calendar

Hi all! I am trying to create a master calendar with two categories: 1) reminders on when to contact someone and 2) program application deadlines, which are in two separate tables. My approach to consolidating these is to create a new table called Master Calendar where records are automatically created based on conditions about the other tables. I’ve run into an issue with the second category, app deadlines. Issue For the program app deadlines, I created an automation that adds creates a new record with the program and deadline in two columns in the master calendar table. The issue here is that the date that appears in deadline column of the master calendar table is the date the record was created, not the deadline. The automation I used is: when deadline column is not empty in Table A → create a record in master calendar with fields → Program Name (Table A) = Name (Master Calendar) and Deadline (Table A) = Deadline (Master Calendar). It may be important to note that I’m pulling automa

63
Kamille_Parks11
J
J_StanNew Participant
asked in Formulas
Adding together numeric values based on If Formula result

Hopefully one of you amazing pros can help me resolve an issue with a nested IF Formula that isn’t quite doing what I hoped it would. I have four individual products (Line Item #1 through to Line Item #4) and if they are ‘Espresso Blend’ I’d then like their total weight {Total Coffee Weight (G)} to be added together to give me the total amount of Espresso Blend across all of the products combined. For reasons I can’t quite understand the below formula isn’t very happy when the Line Item isn’t Espresso Blend. Rather than throwing the desired ‘0’ into the calculation it is cancelling out everything but the first item to include Espresso Blend. Any help would be greatly appreciated! IF({Line Item #1 Coffee}=“Espresso Blend”, {Total Coffee Weight (G): Line Item #1 Quantity},0 + IF({Line Item #2 Coffee}=“Espresso Blend”, {Total Coffee Weight (G): Line Item #2 Quantity},0 + IF({Line Item #3 Coffee}=“Espresso Blend”, {Total Coffee Weight (G): Line Item #3 Quantity},0 + IF({Line Item #4 Coffee

72
J
Sevanna_Shields
Sevanna_ShieldsInspiring
asked in Formulas
Current Status Field Formula

Hello! I have a table tracking agent info and testing statuses, and I need to create a Current Status and a Current Date & Time field. Imagine a table where (among other things) you have 6 fields, 3 single-selects and 3 date & time fields. Each pair of single-selects & date & time fields represents a testing attempt. So we have 1st Attempt Status, 1st Attempt Date & Time, 2nd Attempt Status, 2nd Attempt Date & Time, 3rd Attempt Status, & 3rd Attempt Date & Time. I need a formula/automation that always shows the current status & date & time of the current attempt. Preferably into 2 fields, 1 for the current status and 1 for the current date & time. I need it to keep up if it changes too, i.e. Scheduled changes to Pass/Fail I have an automation currently to watch each Status field and if they’re updated, it will return the most recently edited information BUT, if it’s updated out of order, (2nd attempt to Scheduled, THEN 1st attempt to Fail) yo

142
Sevanna_Shields
A
Ajay_DhesikanNew Participant
posted in Formulas
Find the closest value and lookup different field

I have a table like this: | level | exp | |1|0| |2|500| |3|1,500| |4|3,750| |5|7,875| |6|14,175| |7|22,680| |8|32,886| |9|44,396| |10|57,715| |11|72,144| |12|90,180| |13|112,725| I want a formula that outputs the right level when an exp number that is within a range is given. For example: With an input of 700, the formula should display 2 because the closest available exp value (rounded down) to 700 is 500 and the corresponding level is 2. With an input of 10,000, the formula should display 5 because the closest available exp value (rounded down) is 7,875 which corresponds to level 5. With an input of 33,886, it should output 8. The solution in sheets would be something like (with E2 being the input): =IF(ISNUMBER(E2),INDEX(SORT(A:A,B:B,0),MATCH(E2,SORT(B:B,B:B,0),-1)),) or =QUERY(B:A,"select A where B >= "&E2&" order by B limit 1",0) How is it possible to do this in Airtable? Thanks!

73
kuovonne
A
AutomagicalNew Participant
asked in Formulas
Using Nested IF and DateADD formula

I’m try to create a new field (Event End Date) based on the frequency of the Event (Event Day) and when the Event Day Started (Event Start Date). I started using the formula below but it doesn’t work. In addition, I need to code for Event Days where it’s a single day (i.e., “Thursdays”, “Fridays”, etc.) in which case the Event End Date = Event Start Date. IF( FIND ( "Tue - Fri", {Event Day}), DATEADD({Event Start Date},4, ‘days’)), IF( FIND ( "Tue-Fri", {Event Day}), DATEADD({Event Start Date},4, ‘days’)), IF( FIND ( "Wed - Sat", {Event Day}), DATEADD({Event Start Date},4, ‘days’)), IF( FIND "Tue - Thur”, {Event Day}), DATEADD({Event Start Date},3 , ‘days’)), IF( FIND "Tue - Thu”, {Event Day}), DATEADD({Event Start Date},3 , ‘days’)), IF( FIND "Wed-Fr”, {Event Day}), DATEADD({Event Start Date},3 , ‘days’), IF( {Event Day} = "Thur - Fri", DATEADD({Event Start Date},2, ‘days’)),

175
kuovonne
C
Christen_KrugerNew Participant
asked in Formulas
Regex formula needed for URL http:// & https:// check and add if not there

I am sending URL’s from Webflow which does not have URL validation easily integrated. I want to check and add the https:// to the beginning of the URL if it is not present when the field is created or updated. I have been looking through to forums but can’t seem to find an exact match. Would love some help with this please.

182
C
H
Heather_ParkerNew Participant
posted in Formulas
Issue with Nested If Formula - Value Returned Isn't a Date

Hi everyone - I want to create a formula that will return a date value only if the value in a separate field {campaign duration (days)} is greater than 10. I also want the field to stay “clean” even if the bounding dates (Live and End) aren’t filled in. Please find below the formula I’ve created: IF({Campaign Duration (Days)} < 10,"",IF(AND({Campaign Live Date},{Campaign Duration (Days)}),WORKDAY({Campaign Live Date},{Campaign Duration (Days)}))) Unfortunately the formula returns the following: 2021-08-19T00:00:000Z. Can anyone tell me what I’m missing? The date itself is correct but I’m not even sure what the rest of the value is. I thought at first that this was a formatting issue but according to Airtable the result isn’t a number or a date so it can’t be reformatted.

24
Justin_Barrett
N
N_BigglesworthParticipating Frequently
posted in Formulas
"Can’t save field because it exceeds the limit of formula dependencies"

Hi Airtable folks. I’m wondering if anyone has run across this message when writing Formulas in Airtable: “Can’t save field because it exceeds the limit of formula dependencies” This happened when I was splitting-out 100 image URL’s into individual fields. Everything worked fine until I tried to submit the 99th image Formula. Here are screengrabs of my Formula, in case anyone sees an inconsistency. I highlighted in RED where I made additions/changes. But again, images 1 through 98 worked just fine:

1915
N
M
Matt_JensenNew Participant
posted in Formulas
If / And / Find Formula Help

I need to generate a lead time result based on the contents of a product description {Item Name}. Any help here would be much appreciated. If find “Spider” in {Item Name} result = 30 If find “Raw” in {Item Name} result = 3 If do NOT find “Raw” or “Spider” in {Item Name} result = 7 I tried the formula below, using all the colors in the descriptions to try to isolate the result, but it sums up the results. ie. “730”. IF(FIND(“Raw”, {Item Name}), 3) & IF(FIND(“Black”, {Item Name}), 7) & IF(FIND(“White”, {Item Name}), 7) & IF(FIND(“Clear”, {Item Name}), 7) & IF(FIND(“Turquoise”, {Item Name}), 7) & IF(FIND(“Spider”, {Item Name}), 30)

31
C
A
Ahmed_ElagamiKnown Participant
posted in Formulas
Amortization Formula Row sequence by month help

Hello Awesome AIrtablers people. I am trying to transfer a formula from our Budget tracker Excel to an Airtable new base, It’s a bit tricky. We are a VOD online service and they are trying to see how the content is being consumed and amortized through the years, this has some rules 1- So they have set some rules when it comes to amortization percentage through a logic they already have, This might change according to some research, that’s why they have it in a separate tab inside excel and it’s synced inside the excel through a lookup formula, so if they change the percentage values will change in the main table: look at this image: 2- so according to these percentage: they follow and calculate the amount to be amortized each year according to each percentage so let’s say the total cost of Title (A): is 5000,000M, the release date is : 1/1/2021 1st Year amortization is: 100% so 5M will be amortized in 12 months and each month will have the amount 5M/12: so it’s : 416666.66 for each mo

110
A
M
Michiel_van_OosNew Participant
posted in Formulas
How can I configure a roll up column that refers to a column that is a link column that allows multiple values to only display unique values?

I am using a base to track orders. I have 3 tables: Orders, Orderlines, and Assets. The Orderlines table has an Assets column that links to the Assets table, with the option to allow linking to multiple records. The Orders table has a rollup column Assets that refers to the Assets column in the orderlines table, and applies the ARRAYUNIQUE(values) function. Given these rows in the Orderlines table, the corresponding row in the Orders table does NOT display unique assets: Asset A; iPhone 8; USD 400 Asset B; iPhone Case; USD 30 Asset A, Asset B; Shipping; USD 12 The rollup column Assets in the Orders table has this value: Asset A, Asset B, Asset A, Asset B Using ARRAYJOIN(ARRAYUNIQUE(values)) illustrates the problem: Asset A; Asset B; Asset A, Asset B Notice that there THREE values that Airtable considers unique: Asset A Asset B Asset A, Asset B I thought that applying ARRAYFLATTEN(value) first would solve the problem: ARRAYUNIQUE(ARRAYFLATTEN(value)) Unfortunately this does not

30
M
R
Ronni_EloffParticipating Frequently
posted in Formulas
How do I show time in a specific timezone for Schedule

Thank you @ScottWorld for your assistance with the first part of the formula While that fixed one issue - it introduced another issue I know that the results are showing in my time zone, but I don’t want my time zone to be used - I want the client’s timezone (Pacific for the particular request)

75
kuovonne
K
Kenny_PurnomoNew Participant
posted in Formulas
\\n in SUBSTITUTE is not working

I have followed this Video Structuring Line Item Detail - YouTube in 8:30 But i cannot get the PO details as he got (multiple rows) My formula SUBSTITUTE({Rollup to Text},“Item”,"\nItem") does not return the correct output. Please help

11
Justin_Barrett
D
Daniel_DowNew Participant
posted in Formulas
Using Python library to get records based on date formula

Hello Everyone, I’m using a library called “Airtable Python Wrapper” which can be found here: Airtable Python Wrapper Documentation — Airtable Python Wrapper documentation It has a method, “get_all” that will take an Airtable formula to filter records and return the ones that make it through the formula. filterByFormula = 'IS_AFTER(LAST_MODIFIED_TIME()+\\"2021-01-25T14:23:41.000Z\\")' def get_date_changes(): query = AT.get_all(formula=filterByFormula) However, that formula returns an empty list. I know that there are records in that table with modification dates later than that as new records are added almost every day. Can anyone tell me what might be wrong with that formula? Thanks! -Dan

371
Justin_Barrett
P
P_CNew Participant
posted in Formulas
Lookup for unselected records

Hi Everyone, I have a base for auditing which asks if certain documents are present by linking to document records in another table. I’d like to then be able to collate a list of the missing documents (those not selected) for part of the feedback. Is there any way to do this? I’ve been going mad trying to figure it out!

31
Justin_Barrett
A
Aimee_DerrahNew Participant
posted in Formulas
How do I get a URL for attachments so that it pulls up the attachment view IN Airtable?

What I’m looking for is to be able to send out a URL to collaborators that will bring them to the attachment view window inside Airtable so that they can view my attachment and leave comments. I have tried assigning a trigger & action in automations like this: (“Drafts” is what I named the attachment field") but all this does is open an image of the attachment in your browser. It does not open Airtable. PLEASE HELP!

211
Kamille_Parks11
S
Santiago_VeraNew Participant
posted in Formulas
Consolidated Count from Another Table by Date & 'Shift' Field

Hello! This is similar to another post: Consolidated Count from another table by date field, however I am trying to group by 2 fields. Table 1 has 3 fields (Date, Shift, Count) Table 2 has 3 Fields (Date, Shift, Total Count) Table 1 has 6 records: 1/1/2020, Morning, 2 1/1/2020, Morning, 1 1/1/2020, Midday, 1 1/5/2020, Midday, 3 1/5/2020, Afternoon, 4 1/5/2020, Afternoon, 3 I want table 2 to sum up all of the counts by date and shift. So I would want table 2 to show: 1/1/2020, Morning, 3 1/1/2020, Midday, 1 1/5/2020, Midday, 3 1/5/2020, Afternoon, 7 How can I do this? I have Table 1 grouped by Date&Shift, but I was hoping to show this in a cleaner way in Table 2. Similar to Adding it all up | Airscript but grouping by 2 objects instead of just 1.

66
Kamille_Parks11
J
Juraj_MarkoNew Participant
posted in Formulas
Work Time attendance system

Hi I want build attendance system tracking time to work and out of work I want to use FORM where you can pick Name and slide checkbox IN. After work pick Name again and slide checkbox OUT. The problem I have is how to get the OUT record in some line where the IN record is so I can make formula and calculated the time?

332
D
H
HNCNew Participant
posted in Formulas
CHALLENGE: If Statement based on Category Results

A base used to inventory products. Current relevant columns are: Product Category (single select with option of Painting, Banner, Description (Formula) Product Name (text) Substrate (single select) Size These products are categorized Categories in this Column include: Banners Frames The Description Column is a formula that creates a meaning full sentenced based product description on the columns. So currently I have: {Product Category} & " " & “of” & " " & {Product Name} & " " & “printed on a” & " " & {Size} & " " & {Substrate} In this example it will output: Picture of Glacier printed on a 16 x 20 hardboard gatorfoam What I need is an IF statement in which if the product category is “Painting” the output will have the words “Printed On” as shown above otherwise this word is not shown ALSO if Product Category is “Painting” then the output will have the words “of” as shown above… otherwise this word is not shown.

122
H
A
Anonymous
posted in Formulas
Hours till end of week formula

Hi everyone! I have a work week that operates from Monday-Friday (24 hours). I am trying to develop a formula that would show the total hours remaining from the current time until the week closes each Friday at 11:59PM. This would have to reset each week. So far I have FORMULA: DATETIME_DIFF(NOW(), {FORMULA NEEDED}, “hours”) This is a part of a much bigger formula. Thanks for your help!

46
E

Badge winners

Show all badges
Terms & Conditions