Skip to main content

Formulas

Leverage this space to unlock the power of Airtable formulas.

  • 5,940 Topics
  • 18,233 Replies
5940 Topics
K
Kristen_EdgeKnown Participant
asked in Formulas
Adjust DateTime_Format for numerous Dates in One Formula

I created a formula field to merge three date fields into one. No record has more than one date, so I wanted them all to be in one place. However, when I used concatenate, the dates are very long, so I’m trying to use DateTime_Format for all three, but I keep getting Errors. I’ve tried looking this up and have tried different combinations, but no luck. Here’s what it looked like originally before I tried formatting

71
Kamille_Parks11
K
Kristen_EdgeKnown Participant
asked in Formulas
Concatenate and Substitute

Hi all, I am trying to combine text from two fields using Concatenate (and that’s simple enough) but when I try to add a Substitute formula to shorten one field’s text, it doesn’t work. I am new to formulas, and am sure I’m doing something wrong here. Here are the two formulas that work on their own, but not together. Thank you for any insights! SUBSTITUTE(RIGHT({2021-22 School Program & Membership},LEN({2021-22 School Program & Membership})-22) CONCATENATE({Program Booked},’ / ',{School Name})

508
K
K
Kristen_EdgeKnown Participant
posted in Formulas
Formula to Trim and Replace Text via Formula

Hi all, I am terrible with formulas, and have spent way too long looking through previous posts and reading up on Replace and Substitute and Find formulas. I have a column with various names for programs. I would like to cut the first 22 characters of the title and replace any instance of New School with “New/Comprehensive” for Salesforce upload purposes. Any idea where I’m going wrong here? REPLACE({2021-22 School Program & Membership},1,22,""), FIND(“New School”,{2021-22 School Program & Membership}),17,“New/Comprehensive”) I’ve also tried variations of combining Replace with Substitute. Both work on their own but I can’t find a way to combine them in one field. This may be impossible, but the more likely scenario is user error on my part.

274
K
J
Joseph_BoernerNew Participant
posted in Formulas
IF SWITCH formula not working- what am I doing wrong!?

I’m guessing there’s a syntax error here… I can get the formula to save if I copy a sample formula that includes 3 or 4 arguments, but as soon as I add more, I get an error when trying to save. PLEASE HELP! IF( {Ticket Total} > 0, SWITCH( {ShowDate4Formula}, “2021-09-11”, “PIT”, “2021-09-18”, “MAR”, “2021-10-02”, “PHL”, “2021-10-30”, “MD”, "2021-11-06”, “PAS”, "2021-11-20”, “SD”, "2021-12-04”, “DEN”, "2021-12-18”, “SJ”, "2022-01-01”, “AUS” ) )

92
J
A
airtableManuelKnown Participant
posted in Formulas
I need only the first letter of every word Big

From this: animals > cat > face > eyes into Animals/Cat/Face/Eyes how can i do that i know the first step: SUBSTITUTE({Category 1}, ’ > ', ‘/’) // this replaces the > into / and then how can i use the upper case method only for the first letter of every word? thanks for the help please write an example code, thanks!

11820
Justin_Barrett
A
automadienInspiring
posted in Formulas
Day formula not working - global team

Hi all, thanks in advance for reading. We have the fairly standard day formula implemented in our Airtable: IF({Live Date}=BLANK(),"", DATETIME_FORMAT({Live Date},‘ddd’)) However, for just one week, if the time is before 1AM on Live Date, it appears as the day before. I’m aware a standard fix for this is overriding time zones however that won’t work for us - we’re a global team so the localised time displays are vital, especially as we work in publishing. So, I’m kind of stumped. Any ideas?

65
A
D
Divaksh_JainNew Participant
asked in Formulas
Treat cells with 0 and empty value differently

Hello, Cell value 0 is blank Cell value 0.00 is blank Cell value empty is also blank How to treat cell with 0 value and empty cells differently? All the following forumas are returning result for both the {Buy} cells that has zero value and the {Buy} cells that are empty. I want result only for the cells that has 0 or more value not the cells that has empty values. IF(AND(IF({Buy} >= -1, TRUE(), FALSE()),{Sell}), (Sell-Buy)*Lot*Qty) IF(AND(IF(NOT({Buy} = 0), TRUE(), FALSE()),{Sell}), (Sell-Buy)*Lot*Qty) IF(AND(IF({Buy} = BLANK(), FALSE(), TRUE()),{Sell}), (Sell-Buy)*Lot*Qty)

120
kuovonne
M
Manuel_SoberaniNew Participant
asked in Formulas
Monthly report options

Hello, I’m trying to generate a monthly report can someone give me an idea on how to do it please other than Filtering the date.

113
Justin_Barrett
L
Laura_DinesNew Participant
posted in Formulas
Help with Combining Text with Formula

I’m trying to create a Text field that combines two existing fields that are separated by a dash and a space. I’m using the formula tab but keep getting this error message. Can anyone tell me what I’m doing wrong? I’m new to Air Table so I’m assuming this is a rookie mistake :grinning_face_with_smiling_eyes:

53
L
P
PocketListings_New Participant
asked in Formulas
Extracting the last two characters of a string IF certain conditions are met

I’m probably overthinking this, but struggling to find the right formula to accomplish the following: I have a list of addresses in abbreviated format “123 Main St”, but some addresses have numbers on the end (“123 Main St #2A”). I’m trying to find a way to extract the “St” without affecting addresses with street names like “State” or “Sterling”. It doesn’t need to be perfect, so addresses with St that have a number after them can remain in that format. I’m thinking I need a formula that combines IF, RIGHT and SUBSTITUTE, but can’t quite get it. IF “ST” is contained in the last two characters, extract them. Thanks, Airtable!

361
P
T
Torus_GlobalKnown Participant
asked in Formulas
Help with finance/investment tracking formula

Hi everyone, struggling to pin down a formula… My goal is to track investments over time (i.e. date of deposit, how much was deposited, what the total amount was in my wallet at the time of that deposit, and finishing with profit/loss since I began). The formula I need is for profit/loss since starting. I figure this value is “Total Amount After Deposit” minus the SUM of all values in the “Converted” field (which are all deposits converted from EUR to USD on the investment platform, i.e. Converted1, Converted2, etc)… So how do I get the SUM of ALL Converted values into the formula? (See screenshot for reference) Any questions/clarification required, please let me know. Thanks! Christopher

31
R
A
andrew_braseNew Participant
asked in Formulas
Tax Rate Schedule Formula

Hello Airtable community! I’ve been cobbling together a Base for managing my freelance business which includes tracking bank transactions, expense categories, clients, projects, billable hours, etc. I’m attempting to recreate the 2021 Tax Rate Schedule for the Single filing status from the 1040-ES. I believe I’ve gotten most of the income tax bracket arguments to trigger properly but am stuck for any amount over $209,425.00. Adding even $0.01 to that quantity ends up with a seemingly empty result. Where am I goofing up my formula? IF(AND(INCOME >= 0, INCOME <= 9950), (INCOME - 0) * .1 + 0, IF(AND(INCOME > 9950, INCOME <= 40525), (INCOME - 9950) * .12 + 995, IF(AND(INCOME > 40525, INCOME <= 86375), (INCOME - 40525) * .22 + 4664, IF(AND(INCOME > 86375, INCOME <= 164925), (INCOME - 86375) * .24 + 14751, IF(AND(INCOME > 164925, INCOME <= 209425), (INCOME - 164925) * .32 + 33603, IF(AND(INCOME > 209425, INCOME <= 523,600), (INCOME - 209425) * .35 + 4

222
A
S
SMPParticipating Frequently
asked in Formulas
Unexpected duplicate character on the end of a formula result

I have 2 different kinds of {App Pseud} formats, e.g. kay_obsessive goblin fic (Kiestan) Then I have a formula to spit out a URL, where the concatenate is the important bit: IF(OR({Work Submission Pseud}!={Synced Participant},{Assigned Writer(s)}=BLANK()),BLANK(),CONCATENATE("https://autoao3app.firebaseapp.com/#/mfd2021_round2/user/",SUBSTITUTE(TRIM({App Pseud})," ","%20"))) And it spits out these URLs: https://autoao3app.firebaseapp.com/#/mfd2021_round2/user/kay_obsessive https://autoao3app.firebaseapp.com/#/mfd2021_round2/user/goblin%20fic%20(Kiestan)n The extra character on URLs where the app pseud has a parenthesis always matches the character just before the ). I don’t see what is causing it in the formula. Help!

32
S
S
Shilpa_WymerNew Participant
posted in Formulas
Monthly revenue forecassting

Hi, I’ve got the same issue as this https://community.airtable.com/t/calculating-monthly-revenue-from-start-and-end-dates. But I can’t locate the answer. So I have a calculated column which shows what’s left to pay on a contract and I have a calculated column showing number of months left. I’d like to build this out to show the monthly revenue based on a equal monthly amounts. How do I then get it to populate months from there? Hope that makes sense. Shilpa

143
S
Y
Yauhen_RachkousNew Participant
posted in Formulas
Activate/deactivate button to run a script depand on status column

Hi there, Is it possible to activate/disactivate buttons linked to script depend on status in another column? The case is: I have buttons: run, pause. Each button has own script to do. If status is “to do”, only 'run" button is active. If else ‘in progress’ show ‘pause’. I tried to use additional column for that with IF(),but when i’m put the column with the button to TRUE/FALSE condition formula field convert it to link that is not working. Could you help me to find a way to solve it?

71
kuovonne
E
Enrico_ZoiaParticipating Frequently
posted in Formulas
Create a variable URL with formula

Hi, I want to create a url that contain the contents of a field. Ex: NAME - Google Search NAME is the field, so I can to search on google the content of the field How can I do? I try this (“https…q=NAME”) but it doesn’t work Thanks

71
Kamille_Parks11
N
nisha_elliottInspiring
asked in Formulas
Prefilling Form URL with hyphenated data

Hi! I’m trying to create a prefilled form URL, but I believe the prefill isn’t working because I’m trying to prefill information with hyphens. Any ideas on how I can make this work? Example: Field Name: Type Name Example data: Apples - Michigan Form URL: “https://airtable.com/shrxxxxxxxx?"&“prefill_”&ENCODE_URL_COMPONENT(“Type Name”)&”="&ENCODE_URL_COMPONENT({Type Name}) How do I make sure “Apples - Michigan” doesn’t break the URL (and in turn, not prefill the form)?

172
N
A
Andreas_TystadNew Participant
asked in Formulas
Create date out of numbered fields

Hello! I am organizing and archiving all the films shown from our local cinema. We started in Google Sheets andhave now imported into Airtable. Now we have seperate fields for the Year the film was shown, the month and the day. Is there a formula that combines the numbered fields to a date field? I can’t seem to find it anywhere. Can anyone help? I asume this isn’t the hardest formula, but I don’t know how to do it. Thanks!

73
A
J
Joao_MaiaNew Participant
posted in Formulas
Math Automation

Hi guys, I need help, I have a “statement” column and every month I need to add an x ​​value to that statement, can you guys help me with that?

31
Justin_Barrett
J
Jonathan_FortenNew Participant
posted in Formulas
Newbie Help: Array Formula

I have a Google Sheet currently that uses an Array formula and I want to move to Airtable for a more stable and long term build. Let’s say I have a type of product (user chooses crayons for example) and how they use it (they choose stick figures) the combination of those two has a certain % rate. How do I get Airtable to lookup and return that rate to my client table based on those two inputs? Thanks in advance!!

84
J
S
SMPParticipating Frequently
asked in Formulas
Complex IF using the result of another formula field

So when I first built this IF statement, it brought back “Drabble” correctly when the Word Count was 100 words: IF({Word Count}=100,“Drabble”,IF({Word Count}=200,“Double Drabble”,IF({Word Count}>39999,“Novel”,IF(AND({Word Count}>17499,{Word Count}<40000),“Novella”,IF(AND({Word Count}>7499,{Word Count}<17500),“Novelette”,IF(AND({Word Count}>1000,{Word Count}<7500),“Short Story”,“Ficlet”)))))) Word Count was using the formula provided by airtable help (which is bunk), that is IF({Text}=BLANK(), 0, LEN({Text}) - LEN(SUBSTITUTE({Text}, ’ ', ‘’))+1) When I pasted in a long text that I already knew was 200 words and it brought back 185, I knew I was in trouble. Long story short, it turns out that word count formula in the official help counts the number of spaces and only works if you limit yourself to a single paragraph. So… Now I have the Space Within Paragraph Count field: IF({Text}=BLANK(), 0, LEN({Text}) - LEN(SUBSTITUTE({Text}, ’ ', ‘’))+1) And the Paragraph Count

62
S
F
Fioravante_SouzNew Participant
posted in Formulas
Last Modified date getting wrong date

Hey there, I’ve just added two new fields to my table which will be used to track modification time using Last Modified date. It load dates for all modified records as expected, however, when auditing the values I noticed that some differ from what’s on Activity tab. For example, one of the monitored record’s field was edited 7mo ago (2019-09-25) but the value captured by Last Modified Date is 2020-03-11. Any ideas why this is so different? Thanks

31
S
C
ChemifoxNew Participant
posted in Formulas
Maximum count of a non-numerical linked value

I have an Airtable with 3 tables: a list of readers, a list of books, and a list of genres. The books table has a lookup relationship with both genre and readers. Each book can have multiple genres. I’m trying to display the reader’s favorite genre based on the books they have read. In other words, of the books the user has read, which genre (or genres in the case of a tie) occur(s) the most number of times? Here’s how I tried approaching the problem: Make a hidden lookup or rollup field that aggregates all of the genres from the linked books then find the genre that occurs the most number of times. However, I’m not sure how to do that second step. Is this possible in Airtable? I would appreciate any suggestions! :slightly_smiling_face:

41
S
L
Lauren_BriskinKnown Participant
asked in Formulas
Pulling info into record from field of linked record in formula

I think this may not be possible based on Access fields from linked record in formula without creating Lookup fields - #2 by Kamille_Parks but I’m going to try anyway! I have a pretty complex base based on some longterm needs and incoming records and trying to squeeze things into Airtable that aren’t quite possible. Right now, I’m dealing with two tables that are linked together and linking existing records together. Table 1: Main table. Table 2: Form submission. Table 1: Over a years’ worth of information about people, including linked records of people who are in relationship with one another. Table 2: People who exist in Table 1 one are submitting updated information about themselves AND other people that exist in Table 1. When someone submits information via a form, we link their record to their record in table 1 (usually by the VLookup automation, but since this breaks a lot, often manually.) Table 2 has an important field called “Can you continue to pay for groceries?” that is fi

1422
L
B
Breight_GroupKnown Participant
asked in Formulas
If Created date is within the the last 12hrs

Hi Team, Looking for some assistance on a formula. I want to know if the created date and time is within the last 12hrs. Thanks in advance!

52
Justin_Barrett

Badge winners

Show all badges
Terms & Conditions