Skip to main content

Formulas

Leverage this space to unlock the power of Airtable formulas.

  • 5,939 Topics
  • 18,227 Replies
5939 Topics
matt_stewart1
matt_stewart1Inspiring
asked in Formulas
Rollup formula for MIN date with possible blanks?

I have 2 date columns that are only completed on some projects. I need this rolled up to show the MIN of each of these dates on another table. Arrayunique shows the value, but its not a date, its text. Any other formula like if value then min will only show 0. Essentially any time I add Min to the formula it is showing 0 because of the blank cells.

835
R
A
AnxiousInspiring
asked in Formulas
Formula to display item from another table in the same base, but not using linked records

I am creating a base to keep track of a collection for insurance purposes. I have the list of items in one table and then I have a checklist of what I need for each item in another table all in the same base. I want to copy the items that are in the main table over to the checklist table’s main field dynamically. The main table is already a mile long column-wise, so I didn’t want to put the checklist in the same table. I thought about just using another view, but…it junks up my table, even though you can hide fields. Unfortunately, the type of field the primary field can be is severely limited, but it is able to be a formula field. So I was wondering if there was a formula that could use to dynamically copy/update the primary field with the items in my main table. For example, you can display items from another column with a formula field by simply using the formula: {FieldNameHere} Is it possible to “call” on another table? I only get one automation as well since I only have a free pl

30411
R
M
Maria_Juliana_YNew Participant
posted in Formulas
Comparison and text extraction between Long-Text Fields

Hello Airtable Community, I have two Long-Text Fields associated with the same record. I would like to make a comparison between both Long-Text Fields, which allows me to: Extract in a field the identical text Extract the differential text in a field I appreciate any leads on this, and thank you in advance for your help.

153
Hannah_Wiginton
I
Imee_LeeKnown Participant
posted in Formulas
Can someone help me what are the correct formula of these in Airtable?

Hello, I’m currently copying the formula from our excel file to airtable. But some of the formulas are having errors in Airtable. Can someone help me what are the correct formula of these in Airtable to make it work? Correct Formula for: 6mo Avg =iferror(GPM*(if(Months#>6,6,“”)),“”) 6mo Avg% {WIP: 6mon Ave$}/{Purchase Price} ROI Growth =iferror(Actual Growth on Purchase / Cash Required,) COC Return =iferror(Cashflow / Cash Required) Total ROI =iferror((Actual Growth on Purchase + Cashflow) / Cash required) 6MA =iferror(PCM$ * (if(Month>6,6,“”)),“”) Thank you

241
kuovonne
Tiaan_Terblanch
Tiaan_TerblanchInspiring
posted in Formulas
I need help with a formula to indicate a 21 day reminder before renewal

What I need: For a field to update automatically with a status change if there is 21 days remaining before the clients renewal date. For example, if the next renewal date is 21 days away I would want a “Status of Renewal” field to say ‘Close To Renewal’, but I would also want to know if the member is still ‘Active’ or if they are past the ‘Next Renewal’ date then it has to say ‘Needs Renewal’.

71
R
A
AnnicaTKnown Participant
asked in Formulas
Requesting help with conditional IF formula for makeshift status field

Hello! I have a formula for a “status field” that keeps tripping me up. I have tried multiple ways and can get parts of it to work, but not all. Undoubtedly I’m a comma or similar away from the solution, but by now I’ve tangled myself so far in my own head that I have to admit defeat and humbly ask for assistance. I have the following columns that should be considered in the formula: {End Date} = Formula field {BiU} = Checkbox field {Last Shared} = Date field {Next Share} = Formula field What I want to achieve is: If {BiU} is NOT checked = ‘-’ If {BiU} is checked AND TODAY() is after {End Date} = :white_check_mark: If {BiU} is checked AND TODAY() is BEFORE {Last Shared} and {End Date} = :ballot_box_with_check: If {BiU} is checked AND TODAY() is AFTER {Last Shared}, but BEFORE {End Date} = :link: Any and all help would be greatly appreciated.

4144
Justin_Barrett
Z
Zulefika_MofokeNew Participant
asked in Formulas
Formula/Automation for Date Entry that depends on Status Change

I need to make a automation that will reflect the date I update a Status field on my table to ‘Filed, Letter Received’. These are the two ways I tried, but i’m not sure what I’m doing wrong in both situations. Please help?

324
Z
T
Thomas_PriceNew Participant
posted in Formulas
Formatting date

I know this is trivial and been answered before, however looking through other topics I still haven’t been able to solve this myself and my business needs it resolved swiftly. Formula designed to generate a date 14 days before “Dates Agreed”, I want the outputs formatted in the European fashion (DD/MM/YYYY) and without the time tacked on. Current formula; IF({Dates agreed},DATEADD({Dates agreed}, -14, “days”)) Thanks

31
Ben_Young1
S
Still-LearningInspiring
asked in Formulas
How can i retrieve only particular length of a text from particular colum

I have sample db like this here i want to retrieve only Card colum for last 4 digit alone. I have used the below formula but it returns the whole data But i was success with other filterByFomula like searching InActive members from Status col like this Please help me to resolve

11
S
D
Darren_DuHadawaNew Participant
posted in Formulas
Using an IF statement to cap given number in a field

I’m trying to link multiple fields together and cap the number depending on another field’s selection. For example: Currently I have this formula working as intended in the a field: {X field} / WORKDAY_DIFF({Y Date}, {Z DATE}) I want to add a quantifier on top of that will determine the number it puts out. So, if field A=B, cap the number at 2 If field A=C, cap the number at 1.

31
Kamille_Parks11
N
Neville_ChamberNew Participant
posted in Formulas
Need help with REGEX_REPLACE to prepare markdown for HTML formatting

I’m trying to format markdown in a long text field to something I can use in Zapier. The input looks something like this: # This is a heading 1 This is a paragraph - bulleted item 1 - bulleted item 2 - bulleted item 3 And here's a paragraph - with an embedded minus, and some more normal text The output should look like this: # This is a heading 1 This is a paragraph - bulleted item 1 - bulleted item 2 - bulleted item 3 And here's a paragraph - with an embedded minus, and some more normal text In essence, I need to replace the single newline after each paragraph with two newlines, except for the bulleted lists. My first strategy is to use REGEX_REPLACE(source,“\n”,“\n\n”) which adds the 2 newlines everywhere. So far so good. But for the life of me I can’t find a way to remove the (now) extra newline between the bulleted items (lines starting with a “-”). It seems that the “^” (match at the beginning of a line) in Airtable’s REGEX doesn’t seem to work? Any suggestions highly apprec

233
N
Ricardo11
Ricardo11Inspiring
asked in Formulas
Airtable form submission: Duplicate trigger on every submit (Make/Integromat / Watch responses)

Hello community, I’m using an Airtable form and Make’s (formerly Integromat) Airtable module “Watch responses”. Every submission trigger the hook twice within 5-6s. Has anyone experienced this or an idea of how to avoid the duplication? Thanks!

113
air-chris
O
Owen_MossNew Participant
asked in Formulas
Formula to determine the season we're in

I want to make formula that tells you in what Season we are. I have the seasons divided as follows: Summer = July-Oct Spring = Mar-June Winter = Nov-Feb I have a column which shows the number of the month we’re in with help of the formula MONTH(TODAY()) Then in the column next to it I made a nested IF statement which should determine which the season we’re in based on the division I set above, but it’s only showing the season ‘Summer’ when the month column has a number between 7 and 9 (July-September). With the other month numbers it show no result (i.e. a blank cell). Here’s the IF statement I’m using: IF( {Current Month} >= 7, IF({Current Month} <= 10, 'Summer', IF({Current Month} >= 3, IF({Current Month} <= 6, 'Spring', IF({Current Month} >= 10, IF({Current Month} <= 12, IF({Current Month} >= 1, IF({Current Month} <= 2, 'Winter') ))))))) It would be great if you could help me out. Thanks in a

212
O
L
Lisa_FaganNew Participant
posted in Formulas
Formula editor just keeps spinning

I have tried to edit a formula in a shared base, my own existing bases and an completely new base and all I get is a spinning circle. I have a deliverable that is due tomorrow and I am dead in the water. The response from airtable is that they’ll get back to me tomorrow. HELP

60
L
J
John_DlouhyKnown Participant
asked in Formulas
Formatting a Formula

Greetings Airtable Community, Several weeks ago I received great assistance with a formula quandary involving the combination of two multi-select fields: {College(s)} & IF({Department(s)}, " and " & {Department(s)}) This formula assumed that the {College(s)} field would always be populated. Unfortunately, as we’ve progressed, I’ve discovered this will not always be the case—there will be rare instances were the {College(s)} field will be empty. In these cases, the formula returns: “and Athletics” instead of just “Athletics.” Is it possible to adjust the formula so that it will return just the name of the department (i.e. the " and " will not appear)? Thank you very much!

163
Ben_Young1
P
Peter_BovenmyerNew Participant
posted in Formulas
Two different pricing structures based on Multiple Select field

I want to write a formula that calculates percentages of a total sale price based on data within a multiple select field. Basically if an item is marked “commission” in the multiple select field the formula would calculate 30% of sale price. If marked “asset” in multiple select field the sale price would not change. Do I need to use an if statment to write this?

21
Kamille_Parks11
D
Daniel_RNew Participant
posted in Formulas
Repeating Friday Date Every 2 Weeks

Looking to convert this excel formula into an Airtable formula. =TODAY()+7-WEEKDAY(TODAY(),16) Details We run payroll every 2 weeks, so we’d like to send a reminder on the Friday that timecards are due. So, if today is 7/21, the date in the cell should read 7/29 - until it passes. Then the next date should read 8/12. The above formula does that in Excel, but I’m at a loss of how to convert it.

132
D
Scott_Gardner1
Scott_Gardner1Known Participant
posted in Formulas
Combine 2 DATES to show in one filed, if one is blank. Formula

Hello- I need help, I have 2 separate date fields and only one will be filled out for each record, I need a new field to show all dates MERGED. Thank you! SG

51
TheTimeSavingCo
S
Suzie_GilmoreNew Participant
asked in Formulas
Formula for IF statement with Linked Accounts

I want to find a way to automatically mark a record as “Used” if it is linked to another table. Is there a formula for an IF statement for “IF a field has information in it (if it’s NOT empty), add “Used”” Right now I have the following, but I’m receiving an invalid formula error message: IF({field}, “USED” Thanks in advance!

122
S
Anna_Marie_Wats
Anna_Marie_WatsKnown Participant
posted in Formulas
Checkbox Formatting

Hi! How do I format total cost with if the checkbox is checked it multiplies the cost by 50% and if the checkbox is unchecked, it keeps the full total amount? Example: cost= $100, if I check the checkbox it totals the cost to 100*.50= $50; if the box is unchecked it totals the cost to $100

66
Anna_Marie_Wats
S
Seb11New Participant
posted in Formulas
Progress bar based on empty column

Hello, I can’t create a progress bar based on the empty or not-empty columns of my table. ex: if I have 6 columns and only 3 are filled then the expected result will be: 50% :black_circle: :black_circle: :black_circle: :black_circle: :black_circle: :white_circle: :white_circle: :white_circle: :white_circle: :white_circle: On Notion I succeeded, but I can’t do it on Airtable. Can you help me ? Thanks !!

70
S
M
momentsgoneby80Inspiring
asked in Formulas
How do you FIND() or REGEX_MATCH() in a RTF Long text field?

Hi! I’ ve encountered a possible Airtable quirk and was wondering if anyone has a hint how to work around it. I have a Long Text Field with RTF enabled. In this field I have RTF formated text / Markdown text. In a formula field I’m trying to search this field for Blood Type: AB and if found output AB. Nothing strange so far. Only issue is that no such instance is found and the cell remains empty. I have tried IF( FIND('Blood Type: AB',{Profile}), 'AB' ) and IF( REGEX_MATCH({Profile}, 'Blood Type: AB'), 'AB' ) As I figure that it’s the markdown messing with it I have also tested IF( FIND('**Blood Type:** AB',{Profile}), 'AB' ) and IF( REGEX_MATCH({Profile}, '**Blood Type:** AB'), 'AB' ) and IF( FIND('__Blood Type:__ AB',{Profile}), 'AB' ) and IF( REGEX_MATCH({Profile}, '__Blood Type:__ AB'), 'AB' ) I even tested adding a &'' at the end of the field name, just in wild fishing/testing effort. No luck. Does anyone know how to do this? If I change the text to ‘n

212
M
M
momentsgoneby80Inspiring
posted in Formulas
Formula for alternative spellings and formating of names

Hi! I have a base with quite a bit of Korean names in it. As the names often have a few different ways of romanization I have a field for alternative romanized spellings; {Alternative Spelling(-s)}. What complicates it further is that each romaization can be formated in three ways. Format 1: Name with only spaces, e.g. Park Bo Young Format 2: Name with a hyphen in the second ‘space position’, e.g. Park Bo-Young Format 3: Name with space only between first and last name, e.g. Park Boyoung I need to be able to search by any of these format versions and thereby need them all listed in a field. Today I do this manually, but I figure there’s a way of doing this with a formula with possibly some regex in there. It’s above my current knowledge to 1) figure out the best path to do this and 2) get all the wrappings and or syntaxes in the right position, so I seek help figuring this one out. Each alternative spelling is always separated with ; I need a formula that takes each name separated by ;

10
M
L
Lara_Chan-BakerNew Participant
asked in Formulas
How to extract a portion text from one field and add it to other text in another field

Hi there! I’m losing my mind trying to figure out a formula for something I thought would be quite simple. Basically I have one field into which we input a URL (which comes from our CRM). I want to extract a particular portion of this and then add it to text in another field. To explain: The original field might have something like the below: https://author-p4650.adobeaemcloud.com/editor.html/content/dd/au/en/the-project/**about-us/our-favourite-restaurants-with-a-view**.html I’d like to extract just the section in bold and add it to the following: https://www.danmurphys.com.au/the-project/ to create the below in the formula field: https://www.danmurphys.com.au/the-project/about-us/our-favourite-restaurants-with-a-view I’m not super well versed in formula writing but have tried quite a few things. While the first portion of the original string (ending with “the-project/”) is always that exact number of characters (80 in this example), the issue I keep having is that the portion I want

442
L
P
Peta_HowlettNew Participant
posted in Formulas
Help with DATETIME_DIFF Formula

Hi team, I’m hoping for some help. I am trying to work out the number of weeks between 2 dates. It keeps returning the results Nan and I have no idea what I am doing wrong. Here is the formula I am using. I want to know how many weeks between “Cancel Date” and “Sale Date”. If anyone could give some input or advise it would be massively appreciated. Thanks DATETIME_DIFF( {Cancel Date}, {Sale Date (from Sales)}, ‘weeks’ )

197
A

Badge winners

  • Welcome
    youssefhas earned the badge Welcome
  • First Topic
    _Chris_has earned the badge First Topic
  • First Topic
    GegHhas earned the badge First Topic
  • Welcome
    blessing333has earned the badge Welcome
  • Welcome
    margaryhas earned the badge Welcome
Show all badges
Terms & Conditions