Skip to main content

Formulas

Leverage this space to unlock the power of Airtable formulas.

  • 5,938 Topics
  • 18,225 Replies
5938 Topics
U
Ujval_ShahInspiring
posted in Formulas
Script - Covert string to number/integer

I am using a script feature to add data in the table. One of my field type is “Number”. Data coming from the API is in string. Hence, its not allowing me to add the data in AT, showing error - Error: Field “fldqIRRqhaalc20cqo3” cannot accept the provided value.

121
kuovonne
D
Dale11Participating Frequently
posted in Formulas
How to make a deduction automatic but only once a week

Im trying to figure out how to make the insurance deduction for my drivers automatic but only once a week when I run their payroll settlement.

2113
D
S
Street_opsInspiring
posted in Formulas
IF() Statement in Date Rollup Formula Giving me the 0's

Hey all! Here’s my situation: I’m working on a table with multiple linked records, and I have a rollup that displays the MAX(values) of a linked date field. Here’s my problem: I want said rollup field to display the date ONLY when another condition is met. When I use only MAX(values), it works as expected, but when I add IF(Condition,MAX(values),BLANK()) as below, it returns either a blank cell, or a zero. Here’s what the rollup formula looks like: And this is the result I get: I know I could get around this by creating a separate column for the IF() statement, and another column to show the Final Study On-Platform only if the IF() statement is true, but that’s messy. Hoping for a cleaner solution as my base is already getting quite large. Thanks! Paul

194
kuovonne
J
Jacqui_EganNew Participant
posted in Formulas
Date Range Formula

I’m trying to create a formula field to denote project “wave” based on a date range. So, if the target completion date is between 3/1/2020 and 5/30/2020, this would be “Wave 1”, if the target completion date is between 6/1/2020 and 8/31/2020 this would be “Wave 2”, etc. I’ve made a few different attempts, but it doesn’t like my formula. Any ideas?

395
M
Drew_Nemer
Drew_NemerKnown Participant
asked in Formulas
Last 3 Month Formula

I have a field that is called Re-Exam date. Each Re-exam date is the first of a month in a year. I want to count the total number records of the Current Re-Exam Date (the current month), Next Re-Exam Date (Next month), and the previous month Re-Exam Date. So let’s say a person had: 20 Re-Exams for the Re-Exam Date of 4/1/2022 14 Re-Exams for the Re-Exam Date of 5/1/2022 8 Re-Exams for the Re-Exam Date of 6/1/2022. I can total these in a formula to 42. And when the current month is 6/1/2022, the window shifts so I get the total of 5/1/2022, 6/1/2022, and 7/1/2022.

41
Kamille_Parks11
J
James_RhoadsParticipating Frequently
asked in Formulas
Use a formula to calculate percent complete

Hello! I have a table that has multiple single select fields with the options of “Complete”, “In Progress”, and “To Do.” I was wondering if there was a formula to count the number of fields with “Complete” as the selected option and turn that into a percent complete of all the tasks. Thank you!

362
J
J
Jo_TarczynskiNew Participant
asked in Formulas
How do I count phrases in a list?

I am trying to count the number of key phrases in a list (text field). I found help on Split Text to Columns on how to count words, but am wondering if I can adapt the formula in such a way that counts phrases instead? Here are some screenshots! In the first image I would like record 1 to have a word count of 2 rather than 4. The second image shows the current word count formula.

71
A
C
Cady_Smith1Participating Frequently
posted in Formulas
Extract Color from Select Field

Is it possible to extract the color name or hex code of an option in a select field? I have this select field and I want to create a filter view that we won’t need to continuously update as we are constantly adding new categories. Everything for Marketing will be color coded ‘hot pink’ no matter what. I want the filter to basically be ‘if the select field is this color, then show record in this view’. My thought was that I could use a formula field to extract the color, and base the filter off of that formula field. So if the select field is ‘hot pink’ the formula field would say ‘hot pink’ and the view would be ‘if formula field is hot pink then show record in this view’.

423
C
O
Olivier_NachinNew Participant
posted in Formulas
Visualize how long a card has been in a stack (Kanban view)

Hi, I am using the Kanban view to follow the application process of my students. I would like to see on every card how long this card has been in this stack to quickly see if a card has been in a stack for too long. And if possible, I would like to be able to quickly see on a card if it has been move rencently in this stack (with a different color for this card for exemple). Do you know if it is possible to do this ? Thanks !

62
O
B
Brent_del_RosarKnown Participant
posted in Formulas
Extract the link for last attachment added

Hi, I can’t seem to figure out what I’m doing wrong. I’m trying to pull the link for the last attachment added and the only way I thought to try was using Regex. I tested in regex101 here: regex101: build, test, and debug regex I’m using this in a formula field: REGEX_REPLACE({Image URLs},'.*\\((?=[^(]*$)','') and just get an error. I’m trying to extract from an attachment field: 190423-LMOps005Pool-Fitness0130_blue.jpg (https://dl.airtable.com/.attachments/cad9e1ef16011261083de0d3af0f372e/dbf5c759/190423-LMOps005Pool-Fitness0130_blue.jpg, 190423 LMOps005Pool Fitness0130.jpg (https://dl.airtable.com/.attachments/34893e2547f354b5377bd0ca0d39d118/e6877e1d/190423LMOps005PoolFitness0130.jpg

96
B
L
Leo_FlintNew Participant
asked in Formulas
Compare date value of two entries and check a box on the older entry

Hello lovely community! I have a table [Assets] with new entries being created based on video assets in a dropbox via a make(integromat) integration. thats working nicely. each file ends with a version tag “_v2205181930” which is _v YY MM DD hh mm - which i can extract, read and convert to a date format that airtable understands. each file auto-links itself to an entry in a [Main] table using a 3 digit ID number. this also works nicely. if i have two entries in [Assets] 101_SomeRandomWords_v2205181930.mov 101_SomeRandomWords_v2205182000.mov both linked to entry “101” in [Main] i want for any entry in [Main] that has more than one linked asset, to compare the date tag in each, find which is the newest and for any older entires tick a checkbox column called “Old” I cannot for the life of me work out how to achieve this having tried mutiple formulas, lookups, rollups and even poked aorund at scripting [not my area of expertise] Thank you

100
L
H
Hunter_ReynoldsKnown Participant
asked in Formulas
Calculating Pay based on Work Type

Good afternoon everyone. I’m trying to create a field that calculates the appropriate pay given the work type. In my case, there are 3 different work types, (1. Booked gig, Not booked gig, and Custom Rate). In case 1 where it’s a booked gig, the base pay is $125. If a team member surpasses $125 in sales, then they’ll receive a 20% commission on any sales after. (example, $500 revenue is generated, $500-125=$375*.2=$75+125=$200) In case 2, there is no base pay, but team-member makes 40% commission on all sales made. (example, $500 revenue generated, $500*.4=$200). In case 3, a custom rate is set and field is populated by custom rate given. I’ve been using the following formula, but my issue is that it’s still calculating the commission even if the commission benchmark is not reached for case 1. SWITCH({Work Type},“WAE - Club Booked”,(((Revenue-125).2)+125),“WAE - Club Not Booked”,(Revenue.4),{Custom Rate}) I tried creating the following formula to correct for the situation above, esse

124
H
M
Melissa1Inspiring
asked in Formulas
Description column if formula?

Hello, I am not a specialist in formulas and especially in IF formulas. I have a description column and I would like it to be filled with the content of my product description column fr when language fr (single selection) is selected, same for EN Thank you Mélissa

133
TheTimeSavingCo
C
Cedric_RouxNew Participant
posted in Formulas
Creating records hash in Airtable

Hello there I’d like to create the hash (md5, sha-1, whatever) or a concatenation of two text fields in an Airtable table. I have absolutely no idea how to do that - while in Google script it’s super easy… Does anyone have an idea please? Thanks! Best

1845
R
D
Dale11Participating Frequently
posted in Formulas
Removing certain people from a calculation

So im building a base for my trucking company. I am the owner as well as driver, I also have three owner/operators who are paid by percentage of the load gross. Im stumped on how to take myself out of those calculations - I need to track the money that I make but there are no deductions or percentages taken out of my money as im the boss :slightly_smiling_face: Also how to make a deduction automatic but it would come out of the weekly settlement for the driver, not each load. Thanks - Dale Clarke

96
M
M
Margie_StahlNew Participant
asked in Formulas
Formula: Adding a Quarter and Year to a Formula

Hello! I’m new to Formulas on. Airtable and would love some assistance. I have tried to figure this out through reading the channel and articles and watching videos. I haven’t found the right resource to help me so I’m hoping one of you can. I already have my NPS calculation on the bottom. I want to use the NPS formula and break it down by quarter and year. I already have a quarter and fiscal year conversion. I have tried multiple formulas and nothing seems to be working to add that into my NPS formula. Thank you!

335
TheTimeSavingCo
S
Smyth_CountyParticipating Frequently
asked in Formulas
Working With Fuel Surcharges

I’m not sure this is possible. I’m trying to multiply the amount of an item by a range with an attached percentage. For example, if the fuel price is $5.00 then the range is $5.00 - $5.25 and the percentage is 2%. Then multiply the Freight Amount by the corresponding percentage of 2%. So in this scenario, Freight Amount is $100, Fuel Price is $5 so I would add 2% to the freight price for a fuel surcharge. I have Freight Amount Column, Fuel Price Column, Start Price, End Price and Percentage Columns Any ideas or work arounds would be appreciated. Thanks!

11
V
A
Adi_MenayangKnown Participant
posted in Formulas
If((Find)) not functioning properly

I frequently use the formula IF((FIND)) formula to create indicator columns with 1s and 0s or emojis. In my latest project, however, it has not been working out as I want: The above is a screenshot of a Grid View of form results. The field “BACKEND_Expertise Category Prefix” is a lookup field that shows the subject area codes of subject areas that our respondents selected from a Linked Record question. For example in the fifth row, you see “SB, SB, SB, SS, SH”; that’s because the respondent selected the options for “Early Childhood education;” “Out-of school learning;” and “Parent Teacher Involvement;” which are all coded “SB” (which in our taxonomy system, means they fall under Education). I then created dummy variables/fields using the formula field type to spit out 1 or 0. IF( FIND(“SB”, {BACKEND_Expertise Category Prefix (from Subject Area Expertise)}), 1, 0 ) = so that all records that have “SB” in the lookup field will get a “1” in the column “Education”, and then I can sum up

61
M
Nathan_Ramey
Nathan_RameyKnown Participant
asked in Formulas
Showing Numbers in a formula field

Hello everybody! Inside my base we have 10-digit codes for each of our users for passwords and such. I was wondering if I could somehow use a formula field to only show a select few digits of that code. Similar to a phone number when they send you a confirmation text you can only see the last four. Thanks!

102
Nathan_Ramey
Nedra_Hines
Nedra_HinesInspiring
asked in Formulas
Help revise IF Statement

Hello community! I have the below if statement that needs revising. The issue is that the formula assumes zero when DeStaging Date is blank, when in fact, I want to see the result from the rest of the formula to popup on my Google Calendar. What should I add? IF({DeStaging Date}<{Monthly Rental Fee Renewal Date},“No renewal charge”,CONCATENATE(DATETIME_FORMAT({Monthly Rental Fee Renewal Date},‘MM-DD-YY’)," | “,{Client Name},” | ",“Rental Fee Renewal Date”))

132
Nedra_Hines
P
Perez_ConstructNew Participant
asked in Formulas
IF,SWITCH,Contains and Rollups? Not sure

Hiii So I am trying to track checks and employees. I have an “Employees” table and I have a separate “Checks” table. They are linked. I was able to do a rollup for each employee to get the total amount they received no problem. My issue is I want my rollup to only show me the checks they received in 2021. I see a lot of the forums talking about SWITCH and IF stuff. But what I want is different. I want the formula to look at my dates, see which ones contain the year 2021, and then spit out the check amount so I can then do a rollup on each individual year. This may be way too complicated and I would love a better answer. But ideally, I want to be able to look at how much each employee made by year in the employee table and not just in the checks table. But as each check is individually added, it would need to add up all the checks of that year from the checks table.

82
P
H
Hannah_PowellNew Participant
asked in Formulas
Filtering records by age

I have created a base for my companies therapists. I want our front desk to be able to filter the data to match clients up with therapists. I’m stumped on how to add the age ranges, as they vary greatly. Some therapists see ages 3-10, others 18+, or 25-40, etc. What I’m wanting is to be able to enter the clients age, 32 for example, and be able to filter out therapists that don’t work with that age. plz help

5113
TheTimeSavingCo
M
mbela_channelNew Participant
posted in Formulas
How to Count Unique Values Among Duplicates

Dear beloved friend, Could any one help telling the formula to count unique value or text among the duplicate in airtable? Thanks in advance and regard,

21
TheTimeSavingCo
M
Margie_StahlNew Participant
asked in Formulas
If/Then Statement with Year

Hello there! I am brand new to Airtable formulas and was hoping to get some help. I’m attempting to calculate NPS just for this calendar year of 2022. I was able to calculate the NPS for all of my results but when I try to modify it to add in just for the year 2022 as an If/Then statement it doesn’t work. Here is what I have been attempting to use. Thank you so much for your help! IF( Date}=2022 ) THEN( {NPS Score}=BLANK(),BLANK(), IF({NPS Score} < 7,-100, IF({NPS Score} > 8,100, 0)))

112
M
J
JPPSO_NCParticipating Frequently
posted in Formulas
How to format string as a MAC address

I am storing MAC addresses and we scan a bar code and in inputs the MAC address. The only thing with this is they don’t have the ‘-’. Is it possible to format it to have the - between every 2 characters? Also do I need to have 2 fields for this? The first being the scanned MAC address and the second being a formula? Or can I have a formula and input text then it formats it within the same field? Either way will work.

433
Kamille_Parks11

Badge winners

Show all badges
Terms & Conditions