Skip to main content

Formulas

Leverage this space to unlock the power of Airtable formulas.

  • 5,941 Topics
  • 18,238 Replies
5941 Topics
P
plyskeInspiring
asked in Formulas
Automatically update date on a new Record

Helle everyone! I am trying to make a table where I'll have an overview of different kind of meetings. Let's take meeting A as an example. This meeting happens every 14 days. When the meeting has happened (the date is yesterday) I would like a new Record to be made automatically with the updated Date (14 days later). The reason that I would like a new Record to be made is that each Record will have a document attached with a resume from the meeting.I've tried different approaches but yet to hit the mark. Can someone help me make this happen? Thanks a lot! 

121
TheTimeSavingCo
G
Gregory_DaillyNew Participant
posted in Formulas
Get the number of Monday in 2022

Hi, I would like to know how to get the number of Monday per year. I’m searching in the formula date but i don’t find nothing about that… Could you help me. It’s for calculate the number of hour per day in a year. So i must know the number of “Monday” for example.

82
M
M
Madalina_BelciuNew Participant
asked in Formulas
Concatenate depending on a single select field.

Hi!I have a single select field with two campaign options: Paid Media and Email Nurture. I am trying to create a name field that would concatenate different values, depending on the selected option. I wrote the following line, which is not accepted.IF({Campaign Type} = "Paid Media", "CONCATENATE{Campaign Tactics}, " — ", {Asset}, " — ", {Webpage}", IF({Campaign Type} = "Email Nurture", "CONCATENATE{Campaign Tactics}, " — ", {Steps}, " — ", {Persona}"))Would appreciate any suggestions on how to write this correctly and make it work. Thank you!

150
M
N
newland101New Participant
asked in Formulas
Calculate "Week Commencing" - Not quite right...

Hi guys,We have a field that lists the publish date + time for a piece of web content. We want to be able to organise all of these dates into 'week commencing', ie. add a field that gives the date of the Monday of that week. So, an item published say on Thurs 22nd December 2022, falls into 'Week Commencing' 19th December 2022. Something published on Friday 23rd December 2022, also the same Week Commencing date. The formula we're using to do this is:DATEADD({CONTENT PUBLISH DATE},1-WEEKDAY({CONTENT PUBLISH DATE}),'days')And it sort of works. But it seems to want to force some content pieces from the end of the week into the next week and I'm unsure why. For example, I have a piece of content dated for the 11th December 2022, 3pm, yet it forces it into week commencing 12/12/2022. Any ideas why this might be?

282
N
K
KellyFideiNew Participant
posted in Formulas
How to modify this formula so the record lands on the right recurring date?

Hi all,The problem I'm seeing is when I use the formula below, the recurring date shows up properly in the current year, but one day to early.DATETIME_PARSE(DATETIME_FORMAT({Recurring date},'MM/DD')&"/"&YEAR(NOW()))This 3 minute video I made shows the problem:https://www.loom.com/share/913091fb457b424b9836b6369c75bb10 I realize the problem is likely caused by the Kamillionaire calendar block not considering that 12am (midnight) is really the current day vs. the day before. So my work around I'm hoping for is to modify the formula to show a time after 12am, like for example 6am. Kamillionaire site:https://kamillionaireapps.com/ Thanks in advance for any suggestions,Kelly  

30
K
T
TimafeyNew Participant
posted in Formulas
Trying to come up with a task priority system - has anyone accomplished this?

Hi all, I’m building a client portal, where tasks would be executed according to their priority.The client will assign priorities to their tasks to designate the execution queue.The task priority should be unique and shouldn’t overlap (doubling).Example:Priority values are: “1st in queue”, “2nd in queue”, “3rd in queue”, and “4th in queue”Three tasks in the pipeline:Task_X has priority “1st in queue”Task_Y has priority “2nd in queue”Task_Z has priority “3rd in queue”A client created Task_AAA and prioritized it to “2nd in queue”The new tasks order would look following:Task_X has priority “1st in queue”Task_AAA has priority “2nd in queue”Task_Y has priority “3rd in queue”Task_Z has priority “4th in queue”Any thoughts on how I can achieve this?

301
Karlstens
Jared_Wright
Jared_WrightKnown Participant
posted in Formulas
Single and Multi Select ID accessed from formulas

I want to have a unique ID for single and multi-select fields so that I can use it with formulas and automation, without scripts.

00
Jared_Wright
Z
zeremKnown Participant
posted in Formulas
How can you use something similar to Vlookup?

Hey friendsI have 2 tables that are divided into 2:1. A table of all the leads.2. A table of people who paid.I want to create a column that will check in table 1 whether a certain email of a record appears in table 2 and if so - to add a V in this column.How can this be done?I tried the option of linking but it only connects the leads for me and doesn't do a check on all of them.Thank you. Attach photos.

31
J
S
SquishTrainingNew Participant
asked in Formulas
If formula depending on start date

Hello,I'm new to logic formulas and need some guidance. I have an AT to track company orientations. I have three columns- {Start Date}, {Orientation Complete Date}, and {Orientation Status}. I would like to create a formula for {Orientation Status} to show "Complete" when {Orientation Complete Date} is filled in, "Almost Due" when {Start Date} is within 14 days of todays date and {Orientation Complete Date} is empty, and "Overdue" when {Start Date} is after 15 days from todays date and {Orientation Complete Date} is empty.Any help or a link in the right direction would be appreciated!

60
S
E
EPerezNew Participant
asked in Formulas
IF THEN Formula to remove error (complex formula)

Hi!Right now I get an #ERROR in Column A when the date is blank. However, I'd like it to return "N/A" instead.The formula below still results in an error.  What do you suggest?IF({Formatted Date},CONCATENATE({Formatted Date}," - ",{Event/Release}), "N/A")

230
E
I
Ian_DaviesNew Participant
posted in Formulas
Add sequential loop numbers that trigger another row in separate column to increase in number by +1

I would like to do what was accomplished on this thread except to add the additional steps of updating another row in another column once the loop reaches a specified number. Please see the picture for reference on what I would like to accomplish:

76
B
M
Michael_LeidensParticipating Frequently
asked in Formulas
If function (If column A = True, then .., otherwise Column B

Hi,How should I write the IF function to calculate commission based on A or B ? See below:If Take Rate is filled: Commission = GMV x Take Rate %Otherwise: Commission = GMV - Achat DistributeurThank you    

130
D
G
GregoolParticipating Frequently
asked in Formulas
order in a formula ...

Hi,In one column I want to get shown a trend more obvious with colours.So I want the difference to the week before and in this case a green or a red broken heart.But somehow I am not allowed to put the heart at the front and the difference at the end ...(se result: NaN) 🤔 🤔Any idea, what I cannot see right now?Best regards,Martin

160
G
A
Adam_AbbottNew Participant
posted in Formulas
Linked bases using Integromat or backend service

Have you integrated your Airtable bases with a backend data service/database/or used Integromat to draw information for other bases? Can you help with the below? We’re a GAAS platform that use Airtable to execute various company wide and customer requirements. One of these specifically is how we execute growth projects for customers. We have a central project template with predesigned workflows that we rollout for each customer project. One problem here is that version control gets hard to manage given that Airtable doesn’t allow you to create parent/child templates, ie so you can deploy changes to a template to all active projects that utilise that specific template. As a means to deal with part of this problem we’d like to build out a backend data service that would enable us to pull in generic information into a base and keep that part at least centralised. An example use case: A user on the project team creates a new experiment within the relevant tab on a project base. They will

53
I
M
MrSharksNew Participant
asked in Formulas
Change price with single select ? =) Help =)

Hello,Thank you in advance for your help. I explain you my project 😃I am in charge of a product sourcing agency.I need to make a catalog of my products, but I need to be able to change the price of the product depending on the country of delivery.Example:Product 1 : USA : Price 10 DollarsProduct 1 : FR : Price 15 DollarsProduct 1 : CA : Price 20 Dollars.If you can help me with this it would be great 😃I don't know how to do it, with a select single maybe?

80
M
S
Sean_FogelmanNew Participant
asked in Formulas
Excel - INDEX / MATCH equivalent

What is the excel index match equivalent in a table?

837
E
N
NoCodeLearner32Participating Frequently
asked in Formulas
How to separate out text into a field based on a specific word?

Hi there! I'm new to Airtable and trying to figure out how everything works. Let's say I have a cell that as the following information I inserted below: Beef Onion Burgers with PicklesIngredients:2 hamburger patties1 onion, diced2 tablespoons ketchup4 slices pickles2 hamburger bunsHow can I separate all of the text to the left of the word  "ingredients" into one cell? I basically am trying to extract the name of the recipe into its own cell.

120
TheTimeSavingCo
R
Raphael1New Participant
posted in Formulas
Stamp Duty Land Tax Formula/Calculator

Hi all, does anyone have a formula to calculate Stamp Duty Land Tax (UK) in Airtable? I attach the Excel formula that I previously used. =SUMPRODUCT(--(A1>{125000;250000;925000;1500000}), (A1-{125000;250000;925000;1500000}), {0.02;0.03;0.05;0.02})+(A1*0.03)Thanks in advance!

621
Soly
M
Marc_TorraParticipating Frequently
posted in Formulas
Show all the users in the linked account except the one in the record

I have a table called 'accounts' and another one called 'users' linked to accounts in a one account to many users relationship. I want to create a field in the table users that shows an array with all the other users in that account except themselves. I wonder if airtable would allow me to do that?I have tried with a rollup but I don't know what to enter in the aggregation formula to exclude the user in the record, so I get a list with all the other users in their account.

21
TheTimeSavingCo
R
raechylNew Participant
posted in Formulas
Loan Payoff Date Estimator

Would anyone be able to help me adapt the following formula to Airtable?N = –[ln(1 – [(PV * i) / PMT_] ) / ln(1 + _i)]N = Number of months remainingPV = Present ValuePMT = Payment AmountI'm struggling with an otherwise simple operation because I'm not sure how to account for the natural logarithm in Airtable. I'm ok with something that gets close enough, because the idea is I just want to arrive at an estimated payoff date. Doesn't have to be 100% accurate, just a decent estimate.Thanks for any insight.

50
R
D
David_VoynowParticipating Frequently
posted in Formulas
Using a formula field to create a unique identifier

I currently use the following formula to produce a unique identifier in my my primary field: "PHDEV-" & {Fx_Year} & "-" & REPT(0,3-LEN(CONCATENATE({Fx_Autonum}))) & Fx_Autonum This works as intended, but I'm at a loss understanding how I might expand this formula to do the following:  1) use a different autonumbering value for records based on a field value (e.g., if location is "PA", add suffix "-PA" AND autonumber, and if location is "MA", add suffix "-MA" AND a different series autonumber. Because I'm currently relying on the field formatted to autonumber to add the number, it will issues numbers like 001-PA, 002-PA, 003-MA, 004-PA, 005-MA whereas i want it to number those records as 001-PA, 002-PA, 001-MA, 002-MA, 003-PA 2) reset the numbering count when a new calendar year is present. Is this possible? or will i need to create a new tab for each location/year? Thanks in advance!

640
D
S
sofigueNew Participant
posted in Formulas
Arrayunique : please explain slowly ... :-)

Dear Airtable community,I have read all the posts on the Arrayunique function, and I still have not succeeded in presenting Member Names with no duplicates.Table 1 : Tasks with a multi-select field to describe who is in charge of that task. Tasks are linked to Projects. My Table 1 looks like this :TaskID | Multi-select Field Members      |  Link to Project  |  Lookupfield from Project : Members |Task1      Member1, Member2, Member3                 Project 1                   Member1, Member2, Member3, Member2Task2     Member2                                                 Project1                    Member1, Member2, Memb

90
S
P
Phillip_BodineNew Participant
posted in Formulas
Automatically Linking Columns to a MAster Table to Track Monthly Data (Record Label Streams)

Hey there,I need some help with my airtable. I know it's possible but I'm relatively new and eager to get into database work. I'm now working on a database for my record label and want to create reports for my artists regarding their streams that they can get via a monthly e-mail that I will later automate. I'll break down the workflow to better explain it. And here is the Airtable for you to look over: https://airtable.com/shrQtfIpRU8JlMScQ1) At the end of every month. I got to the Music Distributor's dashboard (MDD), which only I have access to, and go look at the stream count for the selected period (every month). The table that is presented is each individual track NOTE: I cannot filter the table in my (MDD) meaning the order in which the tracks are presented is only based on stream count. So the order is ever-shifting.2) With a plugin, I scrape the HTML of the MDD's table and convert it into csv and import it into airtable as a new table, "Month YYYY." Meaning each month has

51
TheTimeSavingCo
T
TomSpencerNew Participant
posted in Formulas
Comma separated field return value

I have a cell containing values that are semi column separated and have a value after the :How can I split this info into multiple cells?IDLocations1location A:1,Location B:3, Location C:92location A:4,Location B:10, Location C:44I know all Locations, so I can pre-create all needed columns if this would be necessary (beter if it can be done automated of course). How can I automatically copy the info to the right columns?So needed outputIDLocation ALocation BLocation C1139241044Any idea how to achieve this? 

105
J
J
Joel_Andrews1Inspiring
asked in Formulas
Formula to generate icons when box is checked

I'm pretty sure I've seen this covered before, so forgive the reposting. I want to create a field that generates a flag icon/emoji if a checkbox field is checked.I'll use this to add the icon to my field title (using Concatenate) so it draws attention. I've done the same thing using the SWITCH formula, but I can't figure out how to do it with a checkbox field.TIA! 

240
J

Badge winners

Show all badges
Terms & Conditions