Skip to main content

Formulas

Leverage this space to unlock the power of Airtable formulas.

  • 5,946 Topics
  • 18,252 Replies
5946 Topics
H
Holli_YoungerInspiring
posted in Formulas
IF & Blank Formula not pulling in data

Can someone assist and ‘teach’ me where I’m going wrong, please? Trying to pull data from Field B if Field A is blank. Here is the formula I’m currently using, which doesn’t return a value. IF({Field A} < 5000, {Field A},IF({Field A}=BLANK(),{Field B})) This formula works to pull data from Field A but Field C is blank, not fulling data from Field B. I don’t get a formula error, the field is only empty.

52
H
A
alex_todorNew Participant
posted in Formulas
Nested Logic + DATETIME_DIFF

Ok, to get it out of the way, here is the formula: IF( {Which Iteration?} = ‘This Iteration’, ‘New’, IF( AND( DATETIME_DIFF({Created Date in Airtable}, {Custom field (Done Date)}, ‘days’) <= 19, (IF( {Custom field (Released Date)}, DATETIME_DIFF({Created Date in Airtable}, {Custom field (Released Date)}, ‘days’), DATETIME_DIFF({Created Date in Airtable}, Resolved, ‘days’)) <= 14) ‘New’, ‘Old’)) Hopefully it comes through semi-formatted, never posted before. Essentially, logic aside, I’m trying to figure out why it’s an invalid formula. That being said, the logic I’d like it to have is as follows: If the Bug is marked as belonging to ‘This Iteration’ (a formula column I have), mark it as new. Else, if the Bug was created in the past 19 days AND it was either Released or Resolved in the past 14 days (this iteration), mark it as new. ^ (What I would also like to know is whether or not DATETIME_DIFF returns a value, maybe 0, for comparisons between empty and non-empty columns, and wh

30
A
R
Rachel_DavisNew Participant
posted in Formulas
Autocorrect not working correctly

Is there a way to turn off the autocorrect feature? It’s autocorrecting words that I don’t want changed and I can’t seem to find a way to get it to stop. Mars is being autocorrected to March and Pisces is being autocorrected to fish.

182
Melissa_Frank1
J
Jonas_GuentherNew Participant
posted in Formulas
How to VLOOKUP across tables

Hi there, I am struggling to integrate make multiple lookups in one table to add shipping cost to my overview: I have on table containing all my Order Data (order number, customer, product, shipping address, price) and I would like to add a column with my shipping cost to easily calculate the profit that I make (Price - Shipping Cost - COGS). This table contains a column with product variant and shipping province. I have a second table with my associated shipping cost: State, Type of Shipping, Cost of Shipping for each variant - there are 7. (Shipping costs are dependent on State and Variant) I have a third table with all my variants and associated COGS. I don’t have an issue integrating my COGS to the Order Data, since those tables are linked. How do I get my shipping cost? I was able to link Order Data and Shipping Costs via the State column, but how do I avoid integrating all 7 columns as lookups and then write a long IF formula? That kind of makes the additional table useless… Orde

40
J
A
Alta_TechnologiNew Participant
posted in Formulas
Look up alphabetic shelf height in second table

My company has a warehouse full of shelves with differing heights. Rows are lettered, individual racks are numbered (rolling over into letters), horizontal locations on the shelves are numbered 1-4, and shelf height is lettered there is also sometimes a bin number at the end between 1 and 20). Examples: “M74M 15”, “Q11B”, “ZQ3A”, etc. The shelves have different vertical spacing depending on the parts, so it can be hard to tell at a glance whether the part you’re picking can be grabbed without getting a ladder or lift vehicle. Rack H4 has small spacing, so you can easily pick anything up to shelf G, but H5 has larger parts, so anything above C needs a ladder or vehicle. My current solution: searching for the rack number of each part in a spreadsheet that indexes it with the max shelf you can reach. I have a field for the location of each part in our Airtable Database. I’m using formulas to extract the rack number… LEFT({WH Location} , 2) …and the shelf height… RIGHT(LEFT({WH Location} ,

11
A
S
Student_ServiceParticipating Frequently
posted in Formulas
Calculate Employee Sick/Vacation time

Hi All, I have a great working time sheet base that calculates day totals and accrued vacation and sick time, but now I want to be able to subtract USED vacation and sick time from those accruals. I’m wondering if I can use check boxes to identify entries that are sick or vacation and the work those checked boxes into a formula that subtracts the identified records from the total accrued. Here’s a screenshot: Thanks for any help you’ve got! Heather

111
G
C
Cameron_GoldberKnown Participant
posted in Formulas
Method for expanding a link shortener in a cell

Hey! I’m using a Zapier and connecting to dropbox. In my Zap, it drops the a URL of the file uploaded into dropbox back into my Airtable table. Problem is, I don’t want my users to have to use dropbox. Luckily there is a feature through Dropbox that allows you to turn any share link into a direct download link by adding a “1” at the end of the URL instead of the 0. Problem is, the Zapier API is giving me a shortened dropbox link… I can’t change this… is there anyway that I could expand the short link in another cell? That would allow me to create a formula that’d change the last digit of the URL into a direct download link. Thanks in advance!

62
ScottWorld
S
Stephanie_BullwNew Participant
posted in Formulas
Combining lists

I have a Zap that brings invoice information from Quickboks Online into AirTable. Many of these invoices have multiple lines for different charges (material, labor, etc). My Zap brings over the descriptions of the charges in one AirTable column and the charges are put into another column. I have then created columns that take this information and put it into lists. I want to combine the two lists such that the charge ends up with its description. For example: Faucet: $500.00 Installation: $100.00 Currenly when I try to combine the list the result is: Faucet: Installation: $500.00 $100.00 BTW - I have tried to combine the two within the ZAP itself and I get the same result. Thank you - Stephanie

00
S
jord8on
jord8onInspiring
asked in Formulas
Formula to remove last letter of the Value in a field

I have a field called “Category” where each of the categories are plural (i.e., “Widgets”, “Cogs”, etc.) In the first field (record) i have a formula that grabs a product name, concatenated with the “Category” at the end. I would like to have the “s” removed from the category name. Here’s my current formula: MFR & " " & {Model} & " " & {Category} So essentially, I would like to remove the last letter, from the {Categroy} name. Thanks in advance for any help on this!!

1574
jord8on
M
Max_HaInspiring
asked in Formulas
Formula to sum up fields that are not empty

Hi everybody, I got 7 fields that are either empty OR contains a name… Is there a formula which sums up how many of these 7 fields contains a name (and aren’t empyt)? Thanks in advance!

191
M
J
Jessi_BurnsParticipating Frequently
asked in Formulas
Formula help dates

How would I calculate days in foster care from the intake date to today but have it stop counting once the animal’s status is adopted? Ive got the intake date and adopted date but wasnt sure how the formula should be worded. an example of the info

213
J
A
Alba_Machado2Known Participant
posted in Formulas
Airtable Combining and Formatting Multiple Select Columns

Grateful to those of you who’ve helped me get started on Airtable. I recently realized I’ve been leaning too hard on you folks, so I’m making sure to research my Airtable problems as much as I can before heading here. I think I’m getting a handle on the IF formula, but not on the formatting within it. (In case anyone’s curious, aside from taking over my dad’s bookselling business, I’m also helping to get a nonprofit publication off the ground. That’s what this one is for.) Here’s the formula that’s not working right: IF( {Community Causes},{Community Causes} & ", ", " " & IF( {Democracy Causes},{Democracy Causes} & ", ", " " & IF( {Economy Causes},{Economy Causes} & ", ", " " & IF( {Health Causes},{Health Causes} & ", ", " " & IF( {Identity Causes},{Identity Causes} & ", ", " " & IF( {Justice Causes},{Justice Causes} & ", ", " " & IF( {Nature Causes},{Nature Causes} & ", ", " " ))))))) And here’s what that renders (missing values): (

10
A
D
Don_BoscoNew Participant
posted in Formulas
Lookup if matching word found in category list?

I have a list of titles that I want to find the ranking for. For instance, “CEO” is “C Suite”, “Director of Sales” is “Director”, etc… I have a list of potential matches for these categories. For instance, 30 titles that fall under “Manager”, 21 titles that fall under “C Suite”, and so on. How can I make a column to find the appropriate category for a title in Airtable? In Google Sheets this can be achieved with a query function, but I’m not too much of an excel wizard - so any and all help greatly appreciated!

10
D
I
Iain_Rogers1New Participant
posted in Formulas
Cumulative formula/line graph

Hi Has anyone worked out how to make a line chart that shows cumulative sales? Each record in my table has an amount of sales assigned to it and a date but on a line graph the graph shows the number of sales but not the acummulating number of sales. Any Suggestions? Thanks in advance!

60
I
T
TheMinimalistNew Participant
posted in Formulas
Count All Values Excluding Most Recent on Roll Up Field

Hi guys, Got a base where I track my products inventory and need to use a Roll Up field on a table to know the actual amount of product left in the inventory. I also need to know the amount of product before the last inventory movement to be able to know if the inventory came from" in stock" to “out of stock” or the opposite. This part of an automation process were I will send emails to people that were awaiting a product to come back in stock. The idea is to compare both inventory values and trigger the scenario only is the stock went from 0 to whatever value that is not zero. So my need is to be able to sum all records for a specified product except from the last one. I would likely use the “created date” field but not operator allows us to target “the most recent” or “the last”. Any idea on how to archive that goal ? Thanks

00
T
L
Leslie_McKenzieNew Participant
posted in Formulas
WEEKDAY Formula Odd Behavior

Maybe I have got my formula wrong, but I checked and checked it. What I am trying to get down to is a count of how many specific weekdays in a month. So for a schedule date of 11/05/2020, the formula would read “1st Thursday of the month” (11/12/2020 would read “2nd Thursday of the month” and so on) I got this far, and it gives me the first day of the month (so I can build a calculation), which returns 11/01/2020: IF({Scheduled Start (CST)}="","",MONTH({Scheduled Start (CST)})&"/01/"&YEAR({Scheduled Start (CST)})) However, when I add the WEEKDAY wrapper, it just returns a flat blank cell. IF({Scheduled Start (CST)}="","",WEEKDAY(MONTH({Scheduled Start (CST)})&"/01/"&YEAR({Scheduled Start (CST)}))) For the record, I did already try wrapping the date in a DATETIME_FORMAT which also gave a flat blank cell. Some issue with the way I am using WEEKDAY. Any ideas???

152
L
B
Brendon_Gan-LeNew Participant
posted in Formulas
Custom formula to display first "last modified" status

Hello I need a formula which can display which checkbox which was modified FIRST. my idea was to create multiple checkboxes for multiple staff, and we need a way to see which staff ticked their checkbox FIRST and display that as a field. So multiple checkboxes [E.G Adam, Su, Ivy] then a last modified field for each of these checkboxes. Then a custom formula which can see which one of these last modified fields is the OLDEST down to the second. One problem i forsee if they happened within the same second, does airtable deal with nanoseconds? Any help would be appreciated

11
Justin_Barrett
S
Steven_VillarinNew Participant
posted in Formulas
How to: Create a food diary…

Goal Track daily macro Protein/Carb/Fat against micro cal goals and see remaining calories if have not met for each day Associate each food entry with a corresponding caloric allotment, e.g., On X day = 1000 cal, on other days = 1200 cal The challenge I am running into is that I don’t know the best way to structure the table or formula. Any suggestions, please? Thanks for your help. :raised_hands:

651
kuovonne
S
Sarah_HolmesNew Participant
posted in Formulas
Month Formula - Name instead of Number

Hello, Just wondering if it’s possible for the Month formula to show the month instead of just the number? Example attached. Any help would be very much appreciated :slightly_smiling_face: Thank you, Sarah Listed Month (formula)|408x331

772
S
S
slyfoxInspiring
asked in Formulas
Partial Text Match Formula - Name Contains

I can’t figure out a simple formula. Name (field) = John Smith I need the formula to come up with a result if the search for “Smith” is true. I tried IF(FIND('Smith', Name) > 0) Does not work. Please share ideas.

372
S
K
kevin_katanNew Participant
posted in Formulas
Tel:// works as a click to phone in shared view but sms:// formula doesn't make a clickable link what am I missing

tel:// works as a click to phone in shared view but sms:// formula doesn’t make a clickable link what am I missing

113
B
N
Nebal_MaysaudNew Participant
posted in Formulas
Expanding Shorthand set of characters to Longhand

Hi there, This is my first time on this forum and I was wondering if anyone could help me with a slightly niche issue that I’m hoping is possible. At first I thought this might need the scripting block, but I’m wondering if this can be done with an extremely complex formula. I’m doing a database of musical pieces for a symphony orchestra. They have a lot of instruments so we use a shorthand. This shorthand always has the same number of characters where we simply list the number of instruments in score order. Here’s an example. 2222-4321-13-11-1 Translates to: 2 flutes, 2 oboes, 2 clarinets, 2 bassoons, 4 horns, 3 trumpets, 2 trombones, tuba, timpani, 3 percussion, piano, harp, and strings Other instruments we add on in the end in parenthesis i.e. (+contrabassoon, +eng horn). I developed it this way so that it might be easy for what I’d like to do, which is to translate the shorthand into longhand since the longhand form is what’s getting copy/pasted into our website. We can add extra i

10
N
B
ballgame168Inspiring
posted in Formulas
Entering a value and automatically updating a range

I have created a field for the total number of employees in a company (e.g. 227) and company revenue (e.g. $897,000). When the specific number for value is entered for each field, I want it to correlate to another field for a range of employees (e.g. Under 100, 100-249, 250-499, etc.) or revenue range (e.g. Under $1m, $1m-$5m, $5m-$10m, etc.). For example, if I entered 227 for the number of employees it would automatically classify that company in the 100-249 range and if I entered $897,000 it would automatically classify the company in the Under $1m category. Any suggestions for the best way to do this?

00
B
M
Miroslav_TunjicInspiring
posted in Formulas
Time Calculation

Hi all! How to set up a Formula to calculate correctly even if some of the fields are empty. Thx! :m:

20
M
T
TuurInspiring
posted in Formulas
Rollup & Max & Dates & Formulas & Formatting = Loop / Freeze

MAX(values) doesn’t work properly in the scenario where you rollup a date field which uses formatting. It’s no longer recognised as a date and can cause problems. It also looks like old field level formatting is stored when a datatype changes. I used an existing field that was formatted for currency and changed the formula to simply show a date field. My rollup in the parent record caused a major problem later on although the child field appeared as it should: I was locked out the parent table as it got stuck on the ‘Loading’ screen. To re-enable access to that table I had to change the formula in the child table. FYI. :slightly_smiling_face:

269
T

Badge winners

  • Certified AI App Builder
    Wrenhas earned the badge Certified AI App Builder
  • Certified AI App Builder
    Winedotthas earned the badge Certified AI App Builder
  • First Reply
    Jramirohas earned the badge First Reply
  • First Reply
    joechehas earned the badge First Reply
  • Welcome
    joechehas earned the badge Welcome
Show all badges
Terms & Conditions