Skip to main content

Formulas

Leverage this space to unlock the power of Airtable formulas.

  • 5,940 Topics
  • 18,233 Replies
5940 Topics
P
Paul_MatsushimaParticipating Frequently
posted in Formulas
Formula - include URL from long text field (with rich text enabled)

Hello,Is it possible to enable the URL from a long text field with rich text enabled (in other words, this field type) to be clicked on in another formula field? In other words, the long text field I have has text within the field linking out to URLs. When that long text field is included in another formula field (for instance, as a concatenation linking the long text field with other fields), the URLs disappear. Is there any way to enable the URLs to be included and clickable in that separate formula field?Thanks so much!

315
Andrey_Kovalev
K
Kendy511Known Participant
asked in Formulas
Calculate checkbox (rich text) in Long text field

I have a sub-task field (long text with rich text format- check box format) I wanna Insert a field called “Processing” to calculate the % of sub task I have done (checked), which formula should i use for that ?

502
Justin_Barrett
P
Penny_PenatiNew Participant
posted in Formulas
Dynamic Count based on Date in linked field

Hi there. I have a table that has all of our customers and their start date. When they register to an event, it goes into a different table and these are linked to the customers table. I can count how many events they have registered to without a problem, however i'm finding an issue as:I want to calculate how many events they have registered to after their start date (note they can register to events before they start), and I can't seem to find a workaround.  

172
A
M
Michael_LeidensParticipating Frequently
asked in Formulas
Concatenate with limited characters

Hi,Who's gonna help to :- concatenate Row 1+2+3 with a limitation of 3 character per row and a total character of 16 ?I need to create unique reference (in e-commerce) called Variant SKU looking likeModele, Size, Color but as we are limited in number of characters (16) it would look like#MOD-SI-COLIn this view : Variant SKU will be a formula including#SAM (from marque (brand in french)-modele-vendortaking only the 3 first characters thanks guys 🍭

222
M
M
makasabianNew Participant
asked in Formulas
Combining Two Formulas

Hi! I am trying to solve the following:I have a lookup field that lists multiple dates multiples times (the same day in each respective record). Like so: 10/10/2022, 10/10/2022, 10/10/2022, 10/10/2022. This is the "Move In Date ~ Expansion" Field. I only want one date listed and clean like so "MM-DD-YYYY". So I created a Formula Field ("Move In Date ~ Expansion (Calculation)" Field and did an ARRAYUNIQUE formula, however, it shows up with additional 0's and is not clean. How can I adjust this formula so that it lists the date of the lookup field only once and clean like so"MM-DD-YYYY"? Or does anyone have any other solve here? Thank you!

170
M
C
cvlawsonNew Participant
asked in Formulas
Years of service formula

I am new to Airtable, but I cannot find a simple formula to calculate years of service for employees from the start date. I can get it to calculate years, but would like to see it in decimal format. For 8 1/2 years I would like to see 8.50. I am currently using the below...Adding the "months", did not do anything different. DATETIME_DIFF(TODAY(),{Start date},'years', "months") Any help is appreciated... 

230
C
1
100grammesNew Participant
asked in Formulas
View showing lowest price per category/sub-category

Hi,I need to supply to my team of buyers the "best deal" for any given category and sub-category of products in our database. At first, it was easy to group by category / sub-category into a view sorted by price ASC. But over time, the database grew big and we now have 7000+ products splitted into 25 categories and 50 sub-categories ...that makes it long and tedious to scroll through this view to locate manually the cheapest product for each category/sub-category combo (about 150 unique combinations in total).My question: is there a way to get a view that'd output the best product per category / sub-category combo in a flat table format. In other words, this view would contain ONLY the 150 best deals.Many thanks!

30
1
A
Alina_SigitovaNew Participant
posted in Formulas
Field w/ Dynamic Field Conditions possible?

Hello, I am looking for a solution to having dynamic conditions for fields. Example: I have a table with attendees and another table with events. I want a count of how many events an attendee has gone to (which is created via a linked field in the “events” table, to the “Attendees” table). However, I only want a count of how many events they’ve been to AFTER a certain date. That date is dynamic and unique to each participant and lives in the “Attendee” table. How can I filter the “count” field so that I can only see events that they have attended, but only after a certain unique date? The current filters in fields are static, meaning any value applied in the field applies to ALL records equally. Thanks so much for your help!

51
T
A
alternazInspiring
asked in Formulas
Multi IF/OR and fields ?

Hello community!I am looking to display the amount of an hour of additional service to be applied to a price depending on the type of service (A, B, C or D).This number of hours can be 0, 1 or 2.I have already created a column that automatically displays the number of hours to be invoiced (0, 1 or 2).I have already created a column that automatically displays the type of service (A, B, C or D).Here are the possible scenarios :- If my hours column displays 0h and the product type is A, B, C or D, then display 0 €- If my hours column displays 1h or 2h, and the product type is A, then display 10 €- If my hours column displays 1h or 2h, and the product type is B, then display 20 €- If my hours column displays 1h or 2h, and the product type is C, then display 30 €- If my hours column displays 1h or 2h, and the product type is D, then display 40 €Can you help me please ?

100
A
Darryl_Oliver
Darryl_OliverInspiring
asked in Formulas
Rollup Value in a Formula Doesn't Work

My attempts to use a value from a Rollup field in the record Name field result in a circular reference error. Is this expected behavior or a bug?

243
Darryl_Oliver
S
Sara11Inspiring
asked in Formulas
Concatenate Problem

Hi everyone! I hope you had an amazing Thanksgiving!I have some problems with my formula, I'm trying to concatenate two columns (see attachment ). As you can see, the Calculation column missed the "commas" of the originals columns, and moreover, if there is anything in a column, it adds a comma.I would like that my calculation is :1 Report Name2- Report NameCalculationD, EC, FD, E, C, FF, CF, CD, ED, Emy formula is in the attachment.What am I doing wrong?Thank you very much!

1711
S
G
GVM_AdminNew Participant
posted in Formulas
Determine if today is one of a list of dates

I have a list of holiday dates, and I need to determine if today is one of those dates. So far I’ve figured out how to check today against a single date, but not against a list of dates. However, there’s also a problem with checking the date. If I use this formula, I get the correct response. IF( IS_SAME( TODAY(),‘11/22/2022’ ), ‘HOLIDAY’,“NULL” ) BUT, I need to know if TODAY() is equal to the holiday date for different timezones. If I perform a DATETIME_FORMAT with SET_TIMEZONE, then it sets the date to yesterday. And if I use NOW() as the basis for the comparison, it won’t just check against the date, it seems to also want the time. So, question is two-fold: 1) How can I compare today’s date with a specific date in different timezones in order to determine if today is a holiday in that timezone? 2) How can I compare today’s date to a list of dates? Thanks for your help! -Shawn

81
Justin_Barrett
Tiaan_Terblanch
Tiaan_TerblanchInspiring
asked in Formulas
Please help with a formula

HiI would appreciate any assistance with my formula.The end goal is that my Cost field should automatically calculate a cost based on a combination of options selected from the Frequency, Activity, and Complex fields.The dropdown options for Frequency are:Daily1 X Week2 X Week3 X Week1 X Month"Daily" is equal to 22 days."1 X Week" is equal to 4 days."2 X Week" is equal to 8 days."3 X Week" is equal to 12 days."1 X Month" is equal to 1 day.The dropdown options for Activity are:12345"1" is equal to 30 min."2" is equal to 1 hour."3" is equal to 2 hours."4" is equal to 3 hours."5" is equal to 4 hours.The dropdown options for Complexity are:1 (Standard)2 (Medium)3 (Difficult)4 (Very Difficult)"1 (Standard)" is equal to €15."2 (Medium)" is equal to €16."3 (Difficult)" is equal to €17."4 (Very Difficult)" is equal to €18.Now in my Cost field, I want whatever I've selected in Frequency, Activity, and Complex to calculate as an amount.For example, if I select "Daily" in the Frequency field, "2"

120
Tiaan_Terblanch
A
alternazInspiring
asked in Formulas
4 types of services and 2 types of packages

Hello, I am a pro writer for companies and I am looking to generate quotes automatically.Depending on type of report (4 types) selected by the customer AND the time of writing (full day or half day) the price will vary.I want to convert this example to formulas :If half day and very synthetic = price 1If day and very synthetic = price 2If half day and abbreviated summary = price 3If day and abbreviated summary = price 4If half day and standard synthesis = price 5If day and standard synthesis = price 6If half day and exhaustive = price 7If day and exhaustive = price 8I have already created in my table, 2 columns:- one indicating the type of report (very synthetic, abbreviated summary, standard synthesis, exhaustive)- the second indicating if it's half or full dayI am therefore trying to create a formula that will check in my columns "full day or half day?" AND "Type of report ?" in order to show a price.Do you have the possibility to help me please?Have a good day

230
A
Scott_Gardner1
Scott_Gardner1Known Participant
posted in Formulas
Formula question IF statement removing characters

Hello- Does anyone know how to remove the : in front of asset type when the Item Descr and the Multi game is blank? I keep getting this : EMAIL - ANNOUNCEMENT: COMICS PRO - FEB: 2023 Email is the Asset Type IF( {ASSET TYPE}, CONCATENATE({ITEM DESCRIPTION LOOK UP}, IF( {MULTI-GAME TASK TYPE}, “” & {MULTI-GAME TASK TYPE}), IF( {ASSET TYPE}, ": " & {ASSET TYPE}), IF( {ASSET TASK}, " - " & {ASSET TASK}), IF({ADDITIONAL ASSET DESCRIPTION}, ": " & {ADDITIONAL ASSET DESCRIPTION}) ))

11
A
S
Steven_WhitingNew Participant
posted in Formulas
Extracting Date From URL String

Hi AllI'm currently scraping data from a few event listing websites into Airtable to create a database of events for the LGBTQ community.On a few of the websites, I can't extract the full event date from the listings, this only appears in the URL.An example of the URL is:https://www.admiral-duncan.co.uk/soho/02-01-2023/karaoke-showdown-with-candy-healsIs there any easy way to do this?The other issue is that each site link has a different URL length, but the way they show the date is the same each time:https://www.the2brewers.com/london/08-12-2022/pantoAny suggestions?Thanks

432
Matthew_Lanni
T
twmericNew Participant
posted in Formulas
How to change a dependent column to be independent by using formula

Dear All,I have come across a dilemma and see if anyone of you could give me a hand. Is it possible to transform a lookup value, which is dependent, to an independent value in the same table?Like in the above diagram, Main (from Set) is a lookup value, which it changes everyday. Since I am using this table to record the transaction, I would like to see if there is any way that I don't need to cut those records and paste it to Main Dish column manually.Or is there any script that can help making this happen? For example, on a daily basis, it would carry out a schedule job by copying one column to another one?Appreciate your kind advice and comment.🙏

243
A
S
Sylvain_DionneKnown Participant
posted in Formulas
Link any fileds

Hello, there is a way to link a field to another table,other than the, master key field?See attached fileThanksSylvain

102
S
A
alternazInspiring
asked in Formulas
"Long" formula issue

Hello,I'm totally new in the formula game, I created mine by putting pieces of tutorials together and using answers obtained here (by the way, thanks to the community!)That's the deal :When my cell x show 1h, I need to show 0When my cell x show 2h, I need to show 0When my cell x show 3h, I need to show 0When my cell x show 4h, I need to show 1When my cell x show 5h, I need to show 2When my cell x show 6h, I need to show 0I wrote :IF( OR( {Durée estimée} = "1h", {Durée estimée} = "2h", {Durée estimée} = "3h" {Durée estimée} = "6h" ), "0" ) IF( OR( {Durée estimée} = "4h", ), "1" ) IF( OR( {Durée estimée} = "5h", ), "2"I do not yet master the closing of the command and I think it comes from thereDo you have an idea please?

130
A
F
Fahad_ShejiKnown Participant
posted in Formulas
How to put a condition in a email field to lower case all the data

Hey everyone, I’ve been doing a couple of automation with Airtable and I use a formula to pull in data, this is the formula I use for it. "input.email"={Email} I use that to pull in any record on Airtable that has the email I am inputting, what I realized is, it doesn’t work when the casing is different i.e. I noticed john@gmail.com is not equal to John@gmail.com, so the formula will say nothing found although the same email exists with just an upper case. So I have 2 solutions in mind, lowercase all the emails in the Airtable email field cause the input data is always lowercase, or tell the formula to take in upper cased similar emails as well. Could anyone here help me with it?

292
Shuyen
A
alternazInspiring
asked in Formulas
Split text with linear range to multiple columns

Hello community!I'm trying to extract some words of a column in 2 new columns. The number of words is always the same before the parentheses, example :2 big cars (With little windows)3 big cars (With black roof)4 big cars (With a lot of gas)I'm trying to get on column 1 :2 big cars3 big cars4 big carsAnd on column 2, only the information in parentheses :With little windowsWith black roofWith a lot of gasCan you please help me ? 🙂Cheers

62
A
T
TuroNew Participant
posted in Formulas
Lookup from multiple columns

Hello,I have a following situation:Table buyers has 8 rating columns (AAA,AA,A,NR,B,C,No Rating) which are determining different interest rates for each rating value. Each buyer has different interest rates in these rating columns.Table invoices has lookup field which looks up from a supplier table the rating of the supplier and adds that to the supplier_rating field.Now I want to get the correct interest rate from the buyer table based on the rating value of the supplier into invoices table.How should I do this?

41
kuovonne
P
PartybornKnown Participant
posted in Formulas
Airtable error with invalid AND condition. Formular does not abort after first mismatch.

Hi!I have this small test function to reproduce my issue:```IF(AND(5=6,LOWER(50)), 'ok', 'not ok')```Normally I would expect this to output "not ok", but it gives an error. The AND operation does not stop after the first mismatch. Usualy in programming languages you would stop then and no further arguments will be evaluated.This looks like a bug, right?Does anyone know how to work around this?

123
P
M
MarissaP11New Participant
posted in Formulas
Assigning Numeric Value to Text

Hi! Im trying to assign a currency value from a single select field text and am having trouble. I've tried using the IF function and I can get it to look how I want, but the field is not a number field and so will not give me a sum. For example my single select has 2 options "Regular" or "Reserved" . If I select "Regular" I want my formula field to automatically assign a monetary value of $100. And then I want to see the sum of all of those. How can I do this?Thank you!

111
kuovonne
Valentino_Escal
Valentino_EscalInspiring
asked in Formulas
Dividing numbers in a lookup field separated by a comma (,)

Hi Airtable Community!In essence, I want to be able to divide multiple numbers from lookup field (Quantity / Count). The formula that I have returns the value of 1.000 if there is more than one number in the lookup field (Quantity).I have attached two screenshots below 👇Thanks! 👌

173
Valentino_Escal

Badge winners

Show all badges
Terms & Conditions